To, find out costly quires, a majority of DBAs like to visit SQL Profiler. Queries with higher CPU and READ/WRITE are marked as costly quires. I have observed that most of the time quires with less CPU and READ are ACTUAL BIG PROBLEM. Cumulative effect of multiple executions of these commonly considered well performing quires normally put more pressure on system as compare to occasionally executing costly quires. Simple if a query giving result in 10 milliseconds but being executed 10000 time, with in a short time, then definitely it’s a REAL costly query.
To get optimum performance, identification of such COSTLY quires is necessary. This can be achieved by creating history of trace data for peak and off peak hours of your database. Follow given steps for this task.
- Open profiler, and select following columns
- EventClass, TextData, Duration, CPU, Reads, Writes
- Create a filter on your required database
- Start your trace, and later save this trace data to some trace output file.
- Load trace data from trace file to trace table by using following query
SELECT * INTO Trace_Table
- Once the trace data is imported, use following query to find quires with high CPU and READ/WRITE values
SELECT COUNT(*) AS TotalExecutions,
SUM(Duration) AS TotalDuration,
SUM(CPU) AS TotalCPU,
SUM(Reads) AS TotalReads,
SUM(Writes) AS TotalWrites
GROUP BY EventClass,TextData
ORDER BY TotalReads DESC
Why order by TotalReads and not CPU, read this