Performance analysis on SQL Server—how to find resource hogs

There's many different ways of doing performance analysis in SQL Server, and multiple tools you can use. Or, you can also wait for users to complain, and tell you that that certain parts of the application are slow.

Of course, the built-in dynamic management view dm_exec_query_stats is a great tool to add to your list. It can be very useful just as it is. But in doing some performance research recently, I was able to get some very targeted comparative information on performance. This information was extremely useful in deciding which stored procedures, precisely, should be analyzed and improved. Often the stored procedures ones that are at the top of the list have a glaringly obvious flaw that can be fixed, for a big performance boost.

The below script creates a global temporary table, called ##PerformanceReporting. The table is then populated with information from dm_exec_query_stats. The ObjectName field is parsed out from the query text.

A few caveats needs to be given here. The code below is best suited for systems where the database is accessed through stored procedures. Also, stored procedures and functions are listed separately, but most often functions will be used within stored procedures, so that may be misleading.

 

if object_id('tempdb..##PerformanceReporting') is not null drop table tempdb..##PerformanceReporting
go

Create table ##PerformanceReporting (
    ObjectName varchar(100)
    ,QueryText varchar(100)
    ,TotalLogicalReads bigint
    ,ExecutionCount int
    ,TotalCPUTimeInSeconds int
    ,LastExecutionTime date
    ,Rank_LogicalReads int
    ,Rank_CPU int
    ,SumTotalLogicalReads bigint
    ,SumTotalCPU bigint
)    

-- Insert high logical reads
Insert into ##PerformanceReporting (
    QueryText
    ,TotalLogicalReads 
    ,ExecutionCount 
    ,TotalCPUTimeInSeconds 
    ,LastExecutionTime 
    ,Rank_LogicalReads 
    ,SumTotalLogicalReads
    ,SumTotalCPU
)
select 
    QueryText = convert(varchar(100), SQLText.text) 
    ,TotalLogicalReads = sum(total_logical_reads )
    ,ExecutionCount = sum(execution_count )
    ,TotalCPUTimeInSeconds = sum(total_worker_time/1000000 )    
    ,LastExecutionTime = max(last_execution_time )
    ,RowNumber = ROW_NUMBER() OVER (ORDER BY sum(total_logical_reads)  desc)
    ,SumTotalLogicalReads = (select sum(total_logical_reads) from sys.dm_exec_query_stats )
    ,SumTotalCPU = (select sum( total_worker_time)/1000000 from sys.dm_exec_query_stats )    
from 
    (select top 30
        last_execution_time
        ,execution_count
        ,plan_handle
        ,total_worker_time
        ,total_logical_reads
    from sys.dm_exec_query_stats 
    order by total_logical_reads desc
    ) QueryStats
cross apply sys.dm_exec_sql_text(plan_handle) SQLText
Group by convert(varchar(100), SQLText.text) 

-- Insert high CPU 
Insert into ##PerformanceReporting (
    QueryText
    ,TotalLogicalReads 
    ,ExecutionCount 
    ,TotalCPUTimeInSeconds 
    ,LastExecutionTime 
    ,Rank_CPU 
    ,SumTotalLogicalReads    
    ,SumTotalCPU
)
Select 
     QueryText = convert(varchar(100), [text]) 
    ,TotalLogicalReads = sum(total_logical_reads)
    ,ExecutionCount = sum(execution_count )
    ,TotalCPUTimeInSeconds = sum( total_worker_time)/1000000 
    ,LastExecutionTime = max(last_execution_time )
    ,RowNumber = ROW_NUMBER() OVER (ORDER BY sum(total_worker_time) desc)
    ,SumTotalLogicalReads = (select sum(total_logical_reads) from sys.dm_exec_query_stats )
    ,SumTotalCPU = (select sum( total_worker_time)/1000000 from sys.dm_exec_query_stats )    
from 
    (select top 30
        last_execution_time
        ,total_logical_reads
        ,execution_count
        ,plan_handle 
        ,total_worker_time
    from sys.dm_exec_query_stats 
    order by total_worker_time desc) QueryStats
cross apply sys.dm_exec_sql_text(plan_handle) 
Group by convert(varchar(100), [text]) 

-- Update ObjectName to clean up the QueryText
;With CleanUpQueryText as (
 Select
        ObjectNameClean = 
   replace (
    QueryText 
    ,'CREATE Function [dbo].['
    ,'Func: ' )
  ,QueryText
 From ##PerformanceReporting
 Where QueryText like '%function%'
 Union all 
 Select
  ObjectNameClean = 
   replace (
    QueryText 
    ,'CREATE Procedure [dbo].['
    ,'Proc: ' )
  ,QueryText
 From ##PerformanceReporting
 Where QueryText like '%Procedure%'
)
Update ##PerformanceReporting
Set ObjectName = Substring(ObjectNameClean, 1, Charindex ( ']', ObjectNameClean)- 1)
From ##PerformanceReporting 
    join CleanUpQueryText
        on ##PerformanceReporting.QueryText = CleanUpQueryText.QueryText

-- Update ObjectName - only if null 
--(which can happen if it's not a procedure or a function)
Update ##PerformanceReporting
Set ObjectName = QueryText 
Where ObjectName is null

Once the ##PerformanceReporting table has been created, you can query it in a number of useful ways. The first 2 queries below show the details of the objects that have the highest logical reads, and highest CPU usage. The third query shows those objects that have both high logical reads and high CPU. Targeting these should give you the most bang for the buck, in terms of analysis and improvement.

 

-- High Logical Reads
Select 
    ObjectName 
    ,TotalLogicalReads 
    ,ExecutionCount 
    ,TotalCPUTimeInSeconds 
    ,LastExecutionTime 
    ,Rank_LogicalReads
    ,PercentOfTotal = 
        convert(decimal(4, 3), 
            (TotalLogicalReads * 1.0)/SumTotalLogicalReads
            )
From ##PerformanceReporting
Where 
    Rank_LogicalReads  is not null
Order by Rank_LogicalReads 

-- High CPU
Select 
    ObjectName 
    ,TotalLogicalReads 
    ,ExecutionCount 
    ,TotalCPUTimeInSeconds 
    ,LastExecutionTime 
    ,Rank_CPU
    ,SumTotalCPU
    ,PercentOfTotal = 
        convert(decimal(4, 3), 
            ( TotalCPUTimeInSeconds* 1.0)/SumTotalCPU 
            )    
From ##PerformanceReporting
Where 
    Rank_CPU  is not null
Order by Rank_CPU


-- High for BOTH CPU and logical reads
Select 
    ObjectName = 
        isnull(HighLogicalReads.ObjectName, HighCPU.ObjectName)
    ,HighLogicalReads.Rank_LogicalReads
    ,HighCPU.Rank_CPU    
    ,Percent_TotalLogicalReads = 
        convert(decimal(4, 3), 
            (HighLogicalReads.TotalLogicalReads * 1.0)/HighLogicalReads.SumTotalLogicalReads
            )   
    ,Percent_TotalCPU = 
        convert(decimal(4, 3), 
            ( HighCPU.TotalCPUTimeInSeconds* 1.0)/HighCPU.SumTotalCPU 
            )    
From (Select * from ##PerformanceReporting where Rank_LogicalReads is not null) HighLogicalReads
    Full Outer Join (Select * from ##PerformanceReporting where Rank_CPU is not null) HighCPU
        on HighCPU.ObjectName = HighLogicalReads.ObjectName
Order by     
    isnull(HighLogicalReads.Rank_LogicalReads, 0) 
    +
    isnull(HighCPU.Rank_CPU, 0)