Как найти медленные проблемные запросы 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;
Насколько полезной была статья?

Что еще посмотреть по SQL Server

SQL Server. Оптимизация запросов SQL. MS SQL Медленно работают запросы SELECT

SQL Server сильно грузит процессор. Проблемы с CPU у процесса SQLServer. Оптимизация запросов SQL по CPU. Подробное руководство

SQL Server. Работа с индексами через запросы SQL

Где ставить индексы SQL Server

SQL Server. Оптимизация SQL запросов, план запроса в SQL Server. Советы по оптимизация запросов SQL

Решение проблем с большим кешем запросов SQL Server

Инструмент для анализа производительности запросов SQL Server Query Store

SQL Server Как найти запросы с проблемами (warning) в плане выполнения запроса (SQL Plan)

Falcon Space

Это снижение стоимости владения

за счет меньшего количества людей для поддержки и узкого стека разработки. Про снижение стоимости владения продуктом

Это быстрое внесение изменений

по ходу эксплуатации программы. Как создается функционал на платформе

Это простой удобный интерфейс

адаптация под мобильные устройства. Про юзабилити платформы

Нужна бесплатная консультация?
Получить оценку проекта
Создайте концепцию проекта на основе нашего шаблона и получите оценку проекта в виде КП.
Демо-сайт решений
Базисные решения, которые можно гибко адаптировать под себя: менять внешний вид, бизнес-логику и даже структуру базы данных.