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 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. It is not statspack or Automatic Workload Repository (AWR) or V$ACTIVE_SESSION_HISTORY (more importantly) but it is a good start to determining what sql and what sessions are consuming the most resources in SQLserver 2005 instance.

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. Sometimes you have to add an extra condition i.e WHERE executions > 1, or order by executions as well to get the most called, most inefficient sql.

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:

Note: All these scripts will return the actual text as the last column. You could replace that with the plan_handle or sql_handle to allow you to return the XML plan as described in that Top N sql article.

Have Fun

Paul

rem SQL Efficiency by Elapsed Time. Paul Moen (Pythian) 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 (Pythian) 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
rem which is good estimate for CPU/execution. Paul Moen (Pythian) 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