Как найти медленные проблемные запросы 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. Оптимизация запросов SQL по процессору

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

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

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

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

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

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

SQL-инструмент для создания личных кабинетов на сайте

Суть подхода и история создания Falcon Space

Платформа Falcon Space

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

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

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

по ходу эксплуатации программы

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

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

Веб-приложения на MS SQL. Партнерская программа для разработчиков и веб-студий

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