SQL Server DMV Views might be very helpful. Let’s imagine that you have a couple of nested stored procedures and you find out that it’s slow. It would be nice if you could discover which single query/ies in your procedures causes that it is slow. Few DMV views can tell you that.

Ian W. Stirk wrote whole book about practical use DMV views which is definitely worth to read. I’ve received comment from Ian to one of my post recently which contained helpful piece of advice I would like to share here.

Here is link to his book:
SQL Server DMVs in Action: Better Queries with Dynamic Management Views

He post me query which is using DMV view to basically order part of your stored procedure’s source code from slowest to fastest. After some time of using it I found out that it had really great value for me because it discovered things you couldn’t realize (e.g. that scalar functions are terribly slow).

Core of the query are two views – sys.dm_exec_query_stat and sys.dm_exec_sql_text.

Sys.dm_exec_query_stat query contains performance statistics for each query executed on your SQL Server instance. No rocket science. But it contains even offset which can lead you to exact portion of query which is slow – this means that you can find out statistics for e.g. one SELECT query in your procedure. Sys.dm_exec_sql_text contains source code of executed SQL objects. If you combine those two views together you will find out statistics for each individual part of your stored procedure.

Here is the query:

SELECT
CAST(total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [Total Duration (s)]
, CAST(total_worker_time * 100.0 / total_elapsed_time AS DECIMAL(28, 2)) AS [% CPU]
, CAST((total_elapsed_time - total_worker_time)* 100.0 /
total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
, execution_count
, CAST(total_elapsed_time / 1000000.0 / execution_count AS DECIMAL(28, 2)) AS [Average Duration (s)]
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
 ((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) + 1) AS [Individual Query]
, SUBSTRING(qt.text,1,100) AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE total_elapsed_time > 0
ORDER BY total_elapsed_time DESC

There’s one but – I found out these DMV views contain all statistics data from start of your SQL Server Instance. I didn’t find out how to display these statistics only for last query. I always restart SQL Server instance if I don’t want to get mix of slow queries which are not related to last batch I executed. It’s dumb but I didn’t make up better solution. If you know better solution, please share.

Tags: Average, CAST, COUNT, DMV, duration, Ian W. Stirk, Microsoft SQL Server, performance statistics, scalar function, SELECT, SQL, SQL Server, SUBSTRING, view, WHERE