Collecting Query Statistics for SQL Server 2005


By:   |   Updated: 2009-09-23   |   Comments (4)   |   Related: More > Dynamic Management Views and Functions


Problem

With SQL Server 2005 DMVs, we can easily find query performance statistics. Every DBA has a favorite script to find query performance stats. I also have one. But, are all the queries captured by the DMVs?  In this tip I will walk through capturing some of this data and show what is capture and what is not captured by the DMVs.

Solution

Here is a simple example which clearly shows that not all queries are captured in the DMVs.

This excellent article was my starting point. I was running the sample queries in that article and was curious to see if they showed up in my query stats script, but they did not.

Here is my favorite script to find query stats.  This will show the number of times a query was executed, reads, writes and the execution plan. 

The script gets data from these DMVs.

-- Query Stats 
SELECT  
       sdest.dbid  
       ,sdest.[text] AS Batch_Object, 
       SUBSTRING(sdest.[text], (sdeqs.statement_start_offset/2) + 1, 
       ((CASE sdeqs.statement_end_offset 
               WHEN -1 THEN DATALENGTH(sdest.[text]) ELSE sdeqs.statement_end_offset END 
                       - sdeqs.statement_start_offset)/2) + 1) AS SQL_Statement 
       , sdeqp.query_plan  
       , sdeqs.execution_count 
       , sdeqs.total_physical_reads 
       ,(sdeqs.total_physical_reads / sdeqs.execution_count) AS average_physical_reads 
       , sdeqs.total_logical_writes 
       , (sdeqs.total_logical_writes / sdeqs.execution_count) AS average_logical_writes 
       , sdeqs.total_logical_reads 
       , (sdeqs.total_logical_reads / sdeqs.execution_count) AS average_logical_lReads 
       , sdeqs.total_clr_time 
       , (sdeqs.total_clr_time / sdeqs.execution_count) AS average_CLRTime 
       , sdeqs.total_elapsed_time 
       , (sdeqs.total_elapsed_time / sdeqs.execution_count) AS average_elapsed_time 
       , sdeqs.last_execution_time 
       , sdeqs.creation_time  
FROM sys.dm_exec_query_stats AS sdeqs 
       CROSS apply sys.dm_exec_sql_text(sdeqs.sql_handle) AS sdest 
       CROSS apply sys.dm_exec_query_plan(sdeqs.plan_handle) AS sdeqp 
WHERE  sdeqs.last_execution_time > DATEADD(HH,-2,GETDATE())  
               AND sdest.dbid = (SELECT DB_ID('AdventureWorks')) 
ORDER BY execution_count DESC 

For a clean server, that has just been started or if we run DBCC FREEPROCCACHE, if the above query is run we can see that no data is captured as shown below.

Let's see what is captured by the DMVs and what is not captured.


Execute Script-1 a few times in query analyzer and then execute the Query Stats script above and see if script-1 shows up in the results.

-- Script-1 
USE AdventureWorks 
GO 
EXEC sp_executesql  
@stmt = N'select * from Sales.SalesOrderDetail where ProductID = @ProductID', 
@params = N'@ProductID int', @ProductID = 870 
GO

As we can see below it does not show up.

So, we now know sp_executesql queries are not captured in the DMVs


Execute Script-2 few times in query analyzer and then execute the Query Stats script above and see if script-2 shows up in the results.

-- Script-2 
USE AdventureWorks 
GO 
SELECT * FROM Sales.SalesOrderDetail 
WHERE ProductID = 870 

As we can see below it does not show up.

So, we now know standalone queries are not captured in the DMVs. This is good to know since most developers are very comfortable writing inline T-SQL in their application code. So, those inline T-SQL statements are not captured in the DMVs


Now, let's create a stored procedure that has a select statement using script-3 below. After the SP has been created, execute the stored proc dbo.DMVQueryTest a few times in query analyzer and then execute the Query Stats script to see if the stored procedure and select statement shows up in the results.

-- Script-3 
USE AdventureWorks 
GO 
IF OBJECT_ID('dbo.DMVQueryTest') IS NOT NULL 
       DROP PROC dbo.DMVQueryTest 
GO  
CREATE PROC dbo.DMVQueryTest  
@productId INT 
AS 
BEGIN 
       SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @productId 
       RETURN 0 
END 
GO 
  
EXEC dbo.DMVQueryTest 870

As we can see below we can now see this in the query stats.

So, SQL statements that are within a stored procedure are captured in the DMVs. This proves that NOT all T-SQL executed against a database is captured in the DMVs. This is another reason why stored procedures are the best method for data related operations, so we can track their performance and usage using DMVs.


It turns out that the issue is with trying to include the execution plan.  If we run the following script we can see the data, but we do not get the execution plans for all of the SQL batches that are run.

SELECT TOP 5 creation_time, last_execution_time, total_clr_time, 
    total_clr_time/execution_count AS [Avg CLR Time], last_clr_time, 
    execution_count,  
    SUBSTRING(st.TEXT, (qs.statement_start_offset/2) + 1, 
    ((CASE statement_end_offset  
        WHEN -1 THEN DATALENGTH(st.TEXT) 
        ELSE qs.statement_end_offset END  
            - qs.statement_start_offset)/2) + 1) AS statement_text 
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st 
ORDER BY total_clr_time/execution_count DESC; 
GO

 

Note: Upon further investigation of this query based on input from yuewah, the problem is with the line of code that is commented out below that was limiting the data to queries with a database id for AdventureWorks.  If this line is commented out the script will return the results for the queries. The reason for this is that the dbid column is NULL for ad hoc and prepared SQL statements, so trying to limit ths to only queries from the AdventureWorks database removed these queries from the resultset. 

-- Query Stats 
SELECT  
       sdest.dbid  
       ,sdest.[text] AS Batch_Object, 
       SUBSTRING(sdest.[text], (sdeqs.statement_start_offset/2) + 1, 
       ((CASE sdeqs.statement_end_offset 
               WHEN -1 THEN DATALENGTH(sdest.[text]) ELSE sdeqs.statement_end_offset END 
                       - sdeqs.statement_start_offset)/2) + 1) AS SQL_Statement 
       , sdeqp.query_plan  
       , sdeqs.execution_count 
       , sdeqs.total_physical_reads 
       ,(sdeqs.total_physical_reads / sdeqs.execution_count) AS average_physical_reads 
       , sdeqs.total_logical_writes 
       , (sdeqs.total_logical_writes / sdeqs.execution_count) AS average_logical_writes 
       , sdeqs.total_logical_reads 
       , (sdeqs.total_logical_reads / sdeqs.execution_count) AS average_logical_lReads 
       , sdeqs.total_clr_time 
       , (sdeqs.total_clr_time / sdeqs.execution_count) AS average_CLRTime 
       , sdeqs.total_elapsed_time 
       , (sdeqs.total_elapsed_time / sdeqs.execution_count) AS average_elapsed_time 
       , sdeqs.last_execution_time 
       , sdeqs.creation_time  
FROM sys.dm_exec_query_stats AS sdeqs 
       CROSS apply sys.dm_exec_sql_text(sdeqs.sql_handle) AS sdest 
       CROSS apply sys.dm_exec_query_plan(sdeqs.plan_handle) AS sdeqp 
WHERE  sdeqs.last_execution_time > DATEADD(HH,-2,GETDATE())  
               --AND sdest.dbid = (SELECT DB_ID('AdventureWorks')) 
ORDER BY execution_count DESC 
Next Steps
  • Keep this in mind when you are using the DMVs for query usage and performance stats.  If you are using inline T-SQL and sp_executesql you may not be capturing all of the data that you need.
  • Also, think about using stored procedures for all data related operations instead of using inline T-SQL or sp_executesql in your application code.
  • Also, take the time to read Books Online to understand when certain values are available and when they are not available.





get scripts

next tip button



About the author
MSSQLTips author Ranga Narasimhan Ranga Narasimhan has been in IT for over 10 years working on Siebel, Visual Basic, Crystal Reports and SQL Server.

View all my tips


Article Last Updated: 2009-09-23

Comments For This Article




Saturday, October 17, 2009 - 3:32:31 PM - admin Back To Top (4225)

Greg,

Thank you for the additional script!

Thank you,
The MSSQLTips Team


Tuesday, October 6, 2009 - 8:23:49 AM - Greg Larsen Back To Top (4142)

I've modified the original code to constrain the statistics being displayed based on the plans that are associated with the AdventureWorks database in someway by using information in the sys.dm_exec_sql_text or sys.dm_exec_plan_attributes DMVs.

Greg

 

SELECT COALESCE(DB_NAME(sdest.dbid),DB_NAME(CAST(pa.value AS INT)) + '*')  AS DBNAME       ,sdest.[text] AS Batch_Object,        SUBSTRING(sdest.[text], (sdeqs.statement_start_offset/2) + 1,        ((CASE sdeqs.statement_end_offset                WHEN -1 THEN DATALENGTH(sdest.[text]) ELSE sdeqs.statement_end_offset END                        - sdeqs.statement_start_offset)/2) + 1) AS SQL_Statement        , sdeqp.query_plan         , sdeqs.execution_count        , sdeqs.total_physical_reads        ,(sdeqs.total_physical_reads / sdeqs.execution_count) AS average_physical_reads        , sdeqs.total_logical_writes        , (sdeqs.total_logical_writes / sdeqs.execution_count) AS average_logical_writes        , sdeqs.total_logical_reads        , (sdeqs.total_logical_reads / sdeqs.execution_count) AS average_logical_lReads        , sdeqs.total_clr_time        , (sdeqs.total_clr_time / sdeqs.execution_count) AS average_CLRTime        , sdeqs.total_elapsed_time        , (sdeqs.total_elapsed_time / sdeqs.execution_count) AS average_elapsed_time        , sdeqs.last_execution_time        , sdeqs.creation_time  FROM sys.dm_exec_query_stats AS sdeqs        CROSS apply sys.dm_exec_sql_text(sdeqs.sql_handle) AS sdest        CROSS apply sys.dm_exec_query_plan(sdeqs.plan_handle) AS sdeqp        OUTER APPLY sys.dm_exec_plan_attributes(sdeqs.plan_handle) pa WHERE  sdeqs.last_execution_time > DATEADD(HH,-2,GETDATE())   AND attribute = 'dbid'    AND (DB_NAME(sdest.dbid) = (SELECT DB_ID('AdventureWorks'))        OR DB_NAME(CAST(pa.value AS INT)) = 'AdventureWorks')

 


Monday, October 5, 2009 - 5:18:18 AM - Jeff Moden Back To Top (4126)

Cool tip, Ranga.  Thanks for taking the time.


Wednesday, September 23, 2009 - 2:01:40 AM - yuewah Back To Top (4080)

Actually,

if you comment the following statement, you can see the SQL in DMV
AND sdest.dbid = (SELECT DB_ID('AdventureWorks'))



download














get free sql tips
agree to terms