SQL Server Profiler

In my opinion, SQL Profiler is one of the least known and yet most powerful programmer tools out there. For those of you unfamiliar with Profiler, it basically lets you watch under the hood of your application to see all of the calls to your SQL database. It not only tells you each query or stored procedure call, it includes the read count, and duration, along with many other bits of helpful info.

I have given a handful of interviews for .net developers over the past few years and I always ask if they use SQL Profiler. There have been a couple of candidates that had heard of it but never really used it, and the only ones that had used it were dbas and not coders.

Today’s development tools make it super easy to have your database do a whole lot of unnecessary work. SQL Profiler the first app I open up when I am about to review or test one of my developers’ work. Sure enough, no matter how much I cram profiler down their throats it never fails. I can tell right away whether they had it open when they were testing their own code.

Here is what I find most:

• LINQ queries that check for .Count > 0 or .Any() before binding (yes that’s one call to get the count and another to retrieve the results)
• Binding controls multiple times, or even multiple Page.Databind calls
• Frequent calls to “Settings” tables or lookup tables where the data rarely changes and should be cached
• Calling the same function from within a loop instead of calling it once and setting a local variable
• Poorly written LINQ queries that are executing much slower than they should
• LINQ queries that are referenced or bound multiple times without being saved to a generic list
• Tables that are missing indexes (high read counts/durations)

SQL Profiler is also extremely useful for debugging production applications since it can spy into a web.winforms app without affecting its execution. And when you are ready to optimize for performance, just filter for query durations over 500-1000 milliseconds.

Tips

1. Uncheck all events except RPC:Completed and SQL:BatchCompleted
2. Filter by Login name or Application name (set in the connection string) to filter out other activity.
3. Filter for TextData Not Like exec sp_reset_connection.
4. Save the Trace Template with the filters for that project so you do not have to set them again.