Thursday, June 29, 2006

SQLserver 2005 query efficiency sql scripts

Until recently if you wanted to determine what queries or stored procedures were the least efficient you would have to capture all SQL either via SQL Profiler as described in these early posts on automating SQL Profiler and tuning using SQL Profiler or have a scheduled job which dumped the sql running from sysprocesses.

Now with the new dynamic performance views available in SQLserver 2005 you can run queries which allow you to determine these poor performers without much extra work.

As I noted in my last entry, the SQLserver 2005 team have a series of blogs and this inspired me to read and port my existing Oracle scripts which determine query efficiency to SQLserver 2005.
One of my favourite Oracle scripts uses the number of logical reads per execution as a good sign of poorly performing sql. Logical reads per execution is also a reasonable estimation of CPU per execution.

Normally on a poorly performing system I tend to follow these steps, in this case rewritten to use SQLserver 2005 new dynamic views.
  1. Quickly check taskmanager or perfmon to verify that the CPU or IO hog is in fact SQLserver and not IIS or SQL fulltext indexing services (or something else).
  2. Check for contention, is there one process blocking all others.
  3. Run the script to find sql with the highest elapsed time per execution.
  4. Run the script to find sql with the highest physical reads (PIO) per execution.
  5. Run the script to find sql with the highest logical reads (LIO) per execution.
So here are the efficiency scripts:

rem SQL Efficiency by Elapsed Time. Paul Moen 2006

select qs.creation_time
, qs.execution_count "Exec"
, qs.total_elapsed_time "Elapsed"
, total_physical_reads "PIO"
, total_logical_reads "LIO"
, round(qs.total_elapsed_time/qs.execution_count,1) "Time/Exec"
,  round(qs.total_physical_reads/qs.execution_count,1) "PIO/Exec"
,  round(qs.total_logical_reads/qs.execution_count,1) "LIO/Exec"
,  st.text
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
where qs.execution_count > 0
order by "Time/Exec" desc

rem SQL Efficiency by Physical Reads per execution. Paul Moen 2006

select qs.creation_time
, qs.execution_count "Exec"
, qs.total_elapsed_time "Elapsed"
, total_physical_reads "PIO"
, total_logical_reads "LIO"
, round(qs.total_elapsed_time/qs.execution_count,1) "Time/Exec"
,  round(qs.total_physical_reads/qs.execution_count,1) "PIO/Exec"
,  round(qs.total_logical_reads/qs.execution_count,1) "LIO/Exec"
,  st.text
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
where qs.execution_count > 0
order by "PIO/Exec" desc


rem SQL Efficiency by Logical Reads per execution
which is good estimate for CPU/execution. Paul Moen 2006

select qs.creation_time
, qs.execution_count "Exec"
, qs.total_elapsed_time "Elapsed"
, total_physical_reads "PIO"
, total_logical_reads "LIO"
, round(qs.total_elapsed_time/qs.execution_count,1) "Time/Exec"
,  round(qs.total_physical_reads/qs.execution_count,1) "PIO/Exec"
,  round(qs.total_logical_reads/qs.execution_count,1) "LIO/Exec"
,  st.text
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
where qs.execution_count > 0
order by "LIO/Exec" desc

Posted by at