DMV Queries - Get Top N Cache Read Queries on Server

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    ChillyDBA
Date:        27 Jun 2012

Description

This function returns a results set containing a list of the top N (supplied as a parameter) worst logical cache disk read consuming query plans on the server. The stats are valid since the last recompile of the plan.
 
Both the full test of the query batch and the text of the specific SQL statement are included in the results, along with DB and Object information where the statement is not an ad-hoc or prepared SQL statement.
 

Code

Function:

DROP FUNCTION dbo.udf_GetTopNQueries_LogicalReads
Go

CREATE FUNCTION dbo.udf_GetTopNQueries_LogicalReads (@n INT)
RETURNS @TopNQueries_LogicalReads TABLE
  
(
      
PlanHandle              VARBINARY(64),
      
TotalLogicalReads       INT,
      
ExecutionCount          INT,
      
DatabaseName            SYSNAME NULL,
      
ObjectName              SYSNAME NULL,
      
ObjectID                INT NULL,
      
Number                  INT NULL,
      
Encrypted               INT NULL,
      
ParentQuery             NVARCHAR(MAX) NULL,
      
IndividualQuery         NVARCHAR(MAX) NULL
   )
AS
/***************************************************************
Purpose:   To retrieve the top N greatest query plan consumers of
           logical reads on the SERVER (since the last recompile of the plan)
          
           The following DMVs are used:
               sys.dm_exec_query_stats - to actually identify the disk usage levels
               sys.dm_exec_sql_text - to retrieve the original parent query plus
                                      the specific statement that the stat applies to      
          
          
          NOTE:  The DatabaseName and Object details are included in the results
                   but are not really useful when the query was ad-hoc or a prepared statement
          
Author:        ChillyDBA
History:   27 Jun 2012 - Initial Issue

****************************************************************/

BEGIN

   INSERT
@TopNQueries_LogicalReads
      
(
          
PlanHandle,
          
TotalLogicalReads,
          
ExecutionCount,
          
DatabaseName,
          
ObjectName,
          
ObjectID,
          
Number,
          
Encrypted,
          
ParentQuery,
          
IndividualQuery
      
)
  
SELECT  
      
top_n.plan_handle                               AS PlanHandle,  
      
top_n.total_logical_reads                       AS TotalLogicalReads,
      
top_n.execution_count                           AS ExecutionCount,
      
ISNULL(DB_NAME(q.dbid), NULL)                    AS DatabaseName,
      
CAST(OBJECT_NAME(q.objectid) AS VARCHAR(20))  AS ObjectName,
      
q.objectid                                      AS ObjectID,
      
q.number                                        AS Number,  --for a numbered stored procedure
      
q.encrypted                                     AS Encrypted,
      
CONVERT(NVARCHAR(MAX), q.text)                   AS ParentQuery,
        
      
SUBSTRING (q.text,(top_n.statement_start_offset/2) + 1,(
           (  
              
CASE
                  
WHEN top_n.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), q.text)) * 2
                  
ELSE top_n.statement_end_offset
              
END - top_n.statement_start_offset)/2
          
) + 1
      
)                                               AS IndividualQuery
  
FROM  
      
(
          
SELECT TOP (@n)
              
qs.plan_handle,  
              
qs.sql_handle,  
              
qs.execution_count,
              
qs.total_logical_reads,
              
qs.statement_end_offset,
              
qs.statement_start_offset
          
FROM sys.dm_exec_query_stats qs
          
ORDER BY qs.total_worker_time DESC
      
) AS top_n
  
CROSS APPLY sys.dm_exec_sql_text(top_n.sql_handle) AS q
  
ORDER BY top_n.total_logical_reads DESC
  
   RETURN
  
END

 
 

Test Code:

SELECT * FROM dbo.udf_GetTopNQueries_LogicalReads (15)
SELECT * FROM dbo.udf_GetTopNQueries_LogicalReads (20)


ċ
udf_GetTopNQueries_LogicalReads.sql
(3k)
Andy Hughes,
Jun 27, 2012, 6:28 AM
Comments