Monday 16 December 2019

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

No comments:

Post a Comment

Upload valid file in C#

    protected bool CheckFileExtandLength(HttpPostedFile HtmlDocFile)     {         try         {             Dictionary<string, byte[]>...