How can I help identify the worst performing queries in my SQL Server database application?

This is an easy task using the SQL Server Profiler.

Create a new trace in Profiler using the following configuration:

Select These Event Classes

• SQL:BatchCompleted

Select These Data Columns

• Groups
  
  • Duration
• Columns
  
  • EventClass
  • TextData
  • CPU
  • Application Name
  • LoginName
  • NTUserName
  • SPID

Select These Filters (as needed)

• Application Name
• DatabaseName
• Duration

As the above trace runs, you will notice that the duration of each query execution is sorted, from shortest to longest running duration. The longest running queries are at the bottom. You will very quickly be able to identify those queries that are taking a long time to run. Note: duration is measured in milliseconds. So a query that run in 1000 miliseconds takes one second to run.

Once you have identified the worst performing queries in your application, then you can start to analyze each one, trying to find ways to boost their performance. Don’t waste your time on queries that are seldom run. Instead, focus your time on those long running queries that run the most often in your application.

]]>