SQL Server - Performance Report by CPU time

Here I have posted a complete T-SQL for performance report by CPU time. What this sql does:

1. Create temp table to store result set.
2. Create cursor to create HTML table for proper formatting and display result into formatted manner.
3. Send email through Send mail feature by SQL Server via Sql Server Agent Job.

We should have profile to send mail.


CREATE TABLE #Temp 
( 
  [Ranking]  [varchar](50),
  [Elapsed Time (ms)]  [varchar](50),
  [CPU Time (ms)] [varchar](50),
  [Executions]  [varchar](50),
  [SQL Text] [varchar](max)
)


Declare @DBNAME as varchar(100)
SELECT @DBNAME = DB_NAME()
INSERT INTO #Temp([Ranking], [CPU Time (ms)], [Elapsed Time (ms)], [Executions],  [SQL Text])
SELECT DISTINCT TOP (10)
 row_number() over(order by total_worker_time desc) as [Ranking]
,total_worker_time/1000 [CPU Time (ms)]
         ,total_elapsed_time/1000 [Elapsed Time (ms)]
         ,execution_count [Executions]
         ,SUBSTRING(text,
                   CASE WHEN statement_start_offset = 0
                          OR statement_start_offset IS NULL 
                           THEN 1 
                           ELSE statement_start_offset/2 + 1 END,
                   CASE WHEN statement_end_offset = 0
                          OR statement_end_offset = -1 
                          OR statement_end_offset IS NULL 
                           THEN LEN(text) 
                           ELSE statement_end_offset/2 END -
                     CASE WHEN statement_start_offset = 0
                            OR statement_start_offset IS NULL
                             THEN 1 
                             ELSE statement_start_offset/2 END + 1
                  ) AS [SQL Text] 
    FROM sys.dm_exec_query_stats qs 
    --JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
    OUTER APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
    WHERE attribute = 'dbid' 
    AND 
     CASE when @DBNAME = '<not supplied>' THEN '<not supplied>'
                               ELSE COALESCE(DB_NAME(st.dbid),
                                          DB_NAME(CAST(pa.value AS INT)) + '*',
                                          'Resource') END
                                    IN (RTRIM(@DBNAME),RTRIM(@DBNAME) + '*') 
     AND
     last_execution_time > GETDATE() - 1
     ORDER BY [CPU Time (ms)] DESC


declare @DynamicCursor as varchar(8000)
declare @Ranking as varchar(50)
declare @ElapsedTime as varchar(50)
declare @CPUTime as varchar(50)
declare @Executions as varchar(50)
declare @SQLText as varchar(max)
  
set @DynamicCursor='DECLARE CUR_CUST CURSOR READ_ONLY FOR
select [Ranking], [CPU Time (ms)], [Elapsed Time (ms)], [Executions],  [SQL Text] from #Temp'


DECLARE @TR AS VARCHAR(MAX)
SET @TR='<table border=1>'
SET @TR=@TR+'<TR>
<TD>[Ranking]</TD>
<TD>[CPU Time (ms)]</TD>
<TD>[Elapsed Time (ms)]</TD>
<TD>[Executions]</TD>
<TD width=60%>[SQL Text]</TD>
</TR>'


exec (@DynamicCursor)
OPEN CUR_CUST 


FETCH NEXT FROM CUR_CUST INTO @Ranking,@CPUTime,@ElapsedTime,@Executions,@SQLText


WHILE (@@FETCH_STATUS=0)
BEGIN

SET @TR=@TR+'<TR>
<TD>'+@Ranking+'</TD>
<TD>'+@CPUTime+'</TD>
<TD>'+@ElapsedTime+'</TD>
<TD>'+@Executions+'</TD>
<TD>'+@SQLText+'</TD>
</TR>'

FETCH NEXT FROM CUR_CUST INTO @Ranking,@CPUTime,@ElapsedTime,@Executions,@SQLText
END
CLOSE CUR_CUST  
DEALLOCATE CUR_CUST


SET @TR=@TR+'</table>'
drop table #Temp
 
EXEC msdb.dbo.sp_send_dbmail @recipients='test@test.com',      
     @profile_name = 'testprofile',      
--@blind_copy_recipients= '',      
     --@copy_recipients='',      
     @subject = 'Report By CPU Time',   
     @body = @TR,      
     @body_format = 'HTML';  




Any comments or suggestion most welcome.


Thanks.