A few years ago, I had the good fortune to work on an engagement with Kevin McPherrin of Microsoft. Kevin is one of those Microsoft employees that you never hear about, but is an absolute expert on DBA good-practice and SQL advice.
One really useful tip he gave me was to regularly run reports on my production servers, to email details of heavily-used and badly performing stored procedures to your developers… the DMV sys.dm_exec_query_stats can be queried for this summary information.
Now, I send out reports every night, using SSRS, such as this :-
See how often ‘proc A’ is run – and also that the oldest entry in procedure cache is only 6 hours old…
Using this information, developers can focus their attention on optimising the most heavily-used stored procedures in a system.
The query used to produce this report can easily be altered to show what procedures use the most CPU…
Interestingly now that although stored procedure A is run over 25 million timesin 5 and a half hours – there are other stored procedures that use a lot more CPU.
For example, Proc H uses more CPU, even though it has only been run 32 times. And note the CPU usage from this proc only relates to the last hour and a half..
I find these reports are excellent for focusing developer attention on production problems.
The SQL used to produce a report of procedures using the highest average logical IO per execution :-
SELECT TOP 20 RANK() OVER(ORDER BY (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count desc) AS 'Rank',
qs.execution_count AS 'Executions',
qs.max_elapsed_time/1000 AS 'Max Duration ms',
qs.total_elapsed_time/1000 AS 'Total Duration ms',
(qs.total_elapsed_time/(qs.execution_count*1000)) AS [Avg Duration ms],
qs.total_worker_time/1000 AS 'Total CPU ms', (qs.total_worker_time/(qs.execution_count*1000)) AS 'Avg CPU ms',
qs.max_worker_time/1000 AS 'Max CPU ms',
qs.total_logical_reads + qs.total_physical_reads AS 'Total Reads', qs.total_logical_writes AS 'TotalWrites',
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],
qs.max_logical_reads AS 'Max logical reads',
qs.max_logical_writes AS 'Max logical writes',
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2)
OBJECT_NAME(qt.objectid,qt.dbid) AS 'object',
qs.creation_time AS 'plan creation time'
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE object_name(qt.objectid,qt.dbid) IS NOT NULL
Change the text in red to produce a report based on a different order.
I usually include 4 subreports in one email :-
- top 20 most executed stored procedures.
- top 20 highest total CPU stored procedures.
- top 20 highest average logical IOs stored procedures.
- top 20 highest maximum duration stored procedures.
Be aware of the limitations of the sys.dm_exec_query_stats view – for example, it only reports on stored procedures with plans still in cache – but I find this a cheap and easy way of exposing production performance data to a wider audience.