SQL Server Performance: Not All Fast Queries Are Innocent

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

FROM ::fn_trace_gettable('C:\YourTraceFile.trc',default)

• Once the trace data is imported, use following query to find quires with high CPU and READ/WRITE values

SELECT COUNT(*) AS TotalExecutions,

      EventClass,

      TextData,

      SUM(Duration) AS TotalDuration,

      SUM(CPU) AS TotalCPU,

      SUM(Reads) AS TotalReads,

      SUM(Writes) AS TotalWrites

FROM Trace_Table

GROUP BY EventClass,TextData

ORDER BY TotalReads DESC

Why order by TotalReads and not CPU, read this