If you are anything like me then frequently, you will come across database related performance issues that are caused by slow running and expensive queries. Slow running queries or expensive queries are queries that have longer execution times and consume a relatively large amount of hardware resources, such as CPU, memory, and disk I/O. A classic example of a poor performing query is one which processes records row- by row, using cursor to perform a task that can be accomplished by a set-based query. Multiply this by a few hundred or thousand query requests per second coming from different applications hitting the same database continuously and you have a real problem. So how would you identify those slow running and expensive queries?
Of course you can use DMV’s (Dynamic Management Views) or the old faithful Activity Monitor in management studio, but to get more insight into what is going on I prefer SQL Profiler. When looking for expensive queries in SQL Profiler you will more than likely be interested in the duration and cpu times of a given query. Generally queries resulting in high cpu and durations times result in extended lock times and blocking of other queries. In addition poorly written queries produce bad query plans and can result in high numbers of read\write operations which are slow and increase execution times.
For a basic profiler trace for slow and expensive queries I look at the following data columns:
- CPU-Amount of CPU processing time in milliseconds taken by an event;
- Duration-Total amount of time in microseconds taken by an event;
- StartTime-Time when an event starts;
- EndTime-Time when an event ends;
- Reads-Number of data pages that SQL Server has to read for an event;
- Writes-Number of data pages that SQL Server has to write on disk for an event.
How to do it… Follow the steps provided here:
- Start SQL Server Profiler.
- To start SQL Server Profiler, navigate through Start | All Programs | Microsoft SQL Server Program Group | Performance Tools | SQL Server Profiler.
- Select New Trace… from the File menu.
- In the Connect to Server dialog box, provide connection details of SQL Server hosting the AdventureWorks2012 database and click on Connect.
- In the General tab of Trace Properties, specify ExpensiveQueries as trace name and select Blank template for the Use the template: drop-down menu.
- Check the checkbox Save to file: and specify a trace file name and location in the Save As dialog box.
- In the Events Selection tab, check the checkbox for event class SQL:BatchCompleted under TSQL event category.
- Click on the Column Filters… button. In the Edit Filter dialog box, select DatabaseName from the list of available data columns on the left. Expand the Like option and enter string value of the database you are interested in monitoring; then click on the OK button.
- Click on Organize Columns… button in Events Selection tab of Trace Properties dialog box. Select TextData data column and then keep clicking the Up button repeatedly to move the column up the order in the list until the column appears as the second item at the top of the list underneath EventClass data column.
- Do this same exercise also for data columns, such as CPU, Duration, StartTime, Endtime, Reads, and Writes so that they appear underneath the TextData column.
- Press OK in the Organize Columns dialog box.
- When Ready select Select Run
After executing the trace for awhile, stop the trace.
Notice the CPU, Duration, StartTime, EndTime, Reads, and Write columns.
- If you see that some of the SQL:BatchCompleted events caused high number of CPU usage counts, duration counts, and reads/writes counts. These queries are resource consuming and thus expensive queries.
Additionally it is often helpful to filter the trace results to only those queries that have CPU or Duration times above a certain threshold. For example if you want to find out the queries that are taking total execution time of 10 seconds or more, then you can define a filter on Duration column and only those queries running for >= 10000 milliseconds.