Как найти медленные проблемные запросы SQL Server. Оптимизация быстродействия SQL Server

Данная процедура позволяет найти различные проблемные запросы в базе данных.

Для работы запросы нужны права на Server viewstate. По умолчанию в базе Falcon Space она уже есть и вызывается через exec as_perf

CREATE PROCEDURE [dbo].as_perf
AS
BEGIN

select 'use master; GRANT VIEW SERVER STATE TO login' 'Дать права на спец запросы'

select 'Запросы с большим CPU' SECTION

SELECT TOP (5)
	qs.total_worker_time AS Total_CPU,
	db_name(st.dbID) db,
	st.text, 
	total_CPU_inSeconds = --Converted from microseconds
	qs.total_worker_time/1000000,
	average_CPU_inSeconds = --Converted from microseconds
	(qs.total_worker_time/1000000) / qs.execution_count,
	qs.total_elapsed_time,
	total_elapsed_time_inSeconds = --Converted from microseconds
	qs.total_elapsed_time/1000000,	
	qs.execution_count
	--qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);




select 'Проблемные запросы по памяти' SECTION

set transaction isolation level read uncommitted
select 
	top 10
	convert(money, (qs.total_elapsed_time))/(execution_count*1000)  AVG_DURATION_MS,
	db_name(st.dbid)as db,
	case 
		when sql_handle IS NULL then ' '
		else(substring(st.text,(qs.statement_start_offset+2)/2,(
			case
				when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
				else qs.statement_end_offset    
			end - qs.statement_start_offset)/2  ))
	end as text,
	creation_time,
	last_execution_time,
	execution_count,
	total_worker_time/1000 as CPU,
	convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
	qs.total_elapsed_time/1000 as TotDuration,
	total_logical_reads as [Reads],
	total_logical_writes as [Writes],
	total_logical_reads+total_logical_writes as [AggIO],
	convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0))as [AvgIO],
	object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as object_name
from sys.dm_exec_query_stats  qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where total_logical_reads > 0
order by AVG_DURATION_MS desc

select 'Запросы с большим кол-вом строк' SECTION
set transaction isolation level read uncommitted
select 
	top 5 
	max_rows ROWS_COUNT,
	db_name (dbID) db,
	case 
		when sql_handle IS NULL then ' '
		else(substring(st.text,(qs.statement_start_offset+2)/2,(
			case
				when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
				else qs.statement_end_offset    
			end - qs.statement_start_offset)/2+1  ))
	end as query_text,
	execution_count,
	convert( nvarchar, last_execution_time, 120) last_time, 
	total_worker_time/1000 as CPU,
	convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
	qs.total_elapsed_time/1000 as TotDuration,
	convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur]	
from sys.dm_exec_query_stats  qs
cross apply sys.dm_exec_sql_text(sql_handle) st
--where total_logical_reads > 0
order by max_rows desc


select 'Долгие запросы по времени' SECTION

SELECT TOP 10
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS AVG_SEC,
    DB_NAME(qt.dbid) AS db,
	case 
		when sql_handle IS NULL then ' '
		else(substring(qt.text,(qs.statement_start_offset+2)/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+1  ))
	end as query_text,
	qs.total_elapsed_time / 1000000.0 AS total_seconds,
    qs.execution_count,   	
    o.name AS object_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   qs.total_elapsed_time / qs.execution_count / 1000000.0 > 0.2
  ORDER BY AVG_SEC DESC;

select 'Процессы SQL Server по CPU (DBCC INPUTBUFFER(@spid))' SECTION

SELECT top 5 cpu, db_name(dbid) db,  spid, kpid,  memusage FROM sysprocesses
order by cpu desc 

select 'Размеры таблиц' SECTION

SELECT top 5 QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM sys.objects AS sOBJ INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0   AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY sOBJ.schema_id, sOBJ.name
ORDER BY [RowCount] desc
    
END;