SQL Performance Report Queries

--Query 1: logical reads

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.TEXT)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1),

qs.execution_count,

qs.total_logical_reads, qs.last_logical_reads,

qs.total_logical_writes, qs.last_logical_writes,

qs.total_worker_time,

qs.last_worker_time,

qs.total_elapsed_time/1000000 total_elapsed_time_in_S,

qs.last_elapsed_time/1000000 last_elapsed_time_in_S,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_logical_reads DESC -- logical reads

--Query 2: logical writes

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.TEXT)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1),

qs.execution_count,

qs.total_logical_reads, qs.last_logical_reads,

qs.total_logical_writes, qs.last_logical_writes,

qs.total_worker_time,

qs.last_worker_time,

qs.total_elapsed_time/1000000 total_elapsed_time_in_S,

qs.last_elapsed_time/1000000 last_elapsed_time_in_S,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_logical_writes DESC -- logical writes

--Query 3: CPU time

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.TEXT)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1),

qs.execution_count,

qs.total_logical_reads, qs.last_logical_reads,

qs.total_logical_writes, qs.last_logical_writes,

qs.total_worker_time,

qs.last_worker_time,

qs.total_elapsed_time/1000000 total_elapsed_time_in_S,

qs.last_elapsed_time/1000000 last_elapsed_time_in_S,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_worker_time DESC -- CPU time

--Query 4: Missing Index

SELECT  

dm_mid.database_id AS DatabaseID, 

dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact, 

dm_migs.last_user_seek AS Last_User_Seek, 

OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName], 

'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_' 

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')  

+ CASE 

WHEN dm_mid.equality_columns IS NOT NULL 

AND dm_mid.inequality_columns IS NOT NULL THEN '_' 

ELSE '' 

END 

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','') 

+ ']' 

+ ' ON ' + dm_mid.statement 

+ ' (' + ISNULL (dm_mid.equality_columns,'') 

+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns  

IS NOT NULL THEN ',' ELSE 

'' END 

+ ISNULL (dm_mid.inequality_columns, '') 

+ ')' 

+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement 

FROM sys.dm_db_missing_index_groups dm_mig 

INNER JOIN sys.dm_db_missing_index_group_stats dm_migs 

ON dm_migs.group_handle = dm_mig.index_group_handle 

INNER JOIN sys.dm_db_missing_index_details dm_mid 

ON dm_mig.index_handle = dm_mid.index_handle 

WHERE dm_mid.database_ID = DB_ID() 

ORDER BY Avg_Estimated_Impact DESC 

GO

--Query 5: Query Time Execution

SELECT   

    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds, 

    qs.total_elapsed_time / 1000000.0 AS total_seconds, 

    qs.execution_count, 

    SUBSTRING (qt.text,qs.statement_start_offset/2,  

         (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) AS individual_query, 

    o.name AS object_name, 

    DB_NAME(qt.dbid) AS database_name 

FROM sys.dm_exec_query_stats qs 

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt 

    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id 

WHERE qt.dbid = DB_ID() 

ORDER BY average_seconds DESC;

----------Query 6: AvgIo

SELECT 

creation_time

, last_execution_time

, total_logical_reads AS [LogicalReads] , total_logical_writes AS [LogicalWrites] , execution_count

, total_logical_reads+total_logical_writes AS [AggIO] , (total_logical_reads+total_logical_writes)/(execution_count+0.0) AS [AvgIO] , st.TEXT

, DB_NAME(st.dbid) AS database_name

, st.objectid AS OBJECT_ID

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(sql_handle) st

WHERE total_logical_reads+total_logical_writes > 0

AND sql_handle IS NOT NULL

ORDER BY [AggIO] DESC