Troubleshooting Sitecore Performance with Debug mode and the Profiler

 Recently, I had this issue where a Sitecore site appeared to function just fine up until migrating to a staging environment.

Once there, though, an issue was discovered with the CPU and memory usage being pegged.  CPU ramped up to about 90-95% usage, and memory usage steadily climbed without going back down.  The Sitecore admin console came up fine, but the front-end pages wouldn't load.

Sitecore logs weren't telling me anything.

On first blush, it looked like a memory leak.  To find the issue, I turned to the trusty Sitecore debug mode, which you can get to by going into the Sitecore Desktop and clicking Debug; or by simply appending ?sc_debug=1 to the URL.

 
Checking the trace at the bottom of the page showed me which layout took the longest to load.  At that point, I looked at the layout and began selectively disabling sublayouts to see which one was causing the problem.

After pinpointing the offending sublayout, which was a sublayout for Featured Video, I wrapped code in profiler tags so that they would show up in the page trace:

Sitecore.Diagnostics.Profiler.StartOperation("Retrieve Video Datasource By ID");
// code goes here
Sitecore.Diagnostics.Profiler.EndOperation("Retrieve Video Datasource By ID");

After doing that, the trace values on the page clearly showed the offending code and how long it was taking:





As it turns out, the problem was that we were using Sitecore Query instead of Fast Query.  Double checking the call in the developer center indicated a 99.91% decrease in the time it took to make the query.

The original reason we didn't use Fast Query?  SelectSingleItem doesn't work (in Sitecore 6.6 anyway) - you have to use SelectItems and grab the first item from the list.

Moral of the story?  Use Sitecore Fast Query (over Sitecore Query).