DiggerSite

Microsoft SQL Server медленно работает? Ищем узкие места, выявляем причины

Дмитрий Горчилин:

Зачастую в работе возникает простой вопрос- почему сервер медленно работает? В чем причина? Кто именно грузит сервер? Какова природа этой избыточной нагрузки? Где узкое место, бутылочное горлышко? Еще вчера работал хорошо и быстро- почему вдруг производительность резко упала?

 

Эти несложные скрипты позволяют отчасти локализовать проблему. Дальше разбираться уже проще, причины становятся понятны и осязаемы.

 

На самом деле процесс поиска причин снижения производительности- это очень увлекательное, творческое занятие. Мне оно напоминает технический детектив, формальные рецепты помогают плохо. Каждый сервер в чем-то индивидуален, каждый имеет свои особенности.

 

Но начинать поиск проблемы нужно с таких вот вещей:

 

— Dmitry Gorchilin 20170412 Дмитрий Горчилин www.digger.dp.ua
— Microsoft SQL Server slowly?
— Сервер работает медленно? Почему?

— 1. Простейший механизм выявления узких мест

DECLARE @wait TABLE(
wait_type nvarchar(60)NULL,
wait_time_ms BIGINT NULL,
signal_wait_time_ms BIGINT NULL
)

DECLARE @startdate DATETIME,@ExitFlag int
SELECT @startdate=GETDATE(),@ExitFlag=0

delete from @wait

insert into @wait(wait_type,wait_time_ms,signal_wait_time_ms)
select wait_type,wait_time_ms,signal_wait_time_ms FROM sys.dm_os_wait_stats
WHERE wait_type LIKE ‘PAGELATCH_%’ OR wait_type LIKE ‘LCK_M_%’
or wait_type IN (‘IO_COMPLETION’,’ASYNC_IO_COMPLETION’,’WRITELOG’,’CXPACKET’,’SOS_SCHEDULER_YIELD’)

WAITFOR DELAY ’00:01′

UPDATE w SET wait_time_ms=ws.wait_time_ms-w.wait_time_ms,signal_wait_time_ms=ws.signal_wait_time_ms-w.signal_wait_time_ms
FROM @wait w INNER JOIN sys.dm_os_wait_stats ws ON ws.wait_type = w.wait_type

UPDATE w SET wait_time_ms=wait_time_ms-signal_wait_time_ms FROM @wait w

select ‘CPU overload’ from @wait w1, @wait w2 WHERE w1.wait_type=’CXPACKET’ and w2.wait_type=’SOS_SCHEDULER_YIELD’ AND w1.wait_time_ms>60e3 AND w1.wait_time_ms*0.30<w2.wait_time_ms

UNION ALL
select ‘IO overload’ WHERE 60e3<(SELECT SUM(wait_time_ms) FROM @wait w WHERE wait_type LIKE ‘PAGELATCH_%’ OR (wait_type IN (‘IO_COMPLETION’,’ASYNC_IO_COMPLETION’,’WRITELOG’)))

UNION ALL
select ‘active lock’ WHERE 60e3<(SELECT SUM(wait_time_ms) FROM @wait w WHERE wait_type LIKE ‘LCK_M_%’)

UNION ALL
select ‘signal overload’ WHERE 60e3<(SELECT SUM(signal_wait_time_ms) FROM @wait w )

UNION ALL
SELECT ‘Processes blocked’ FROM sys.dm_os_performance_counters WHERE (counter_name LIKE ‘%Processes blocked%’)AND (cntr_value>0)

UNION ALL
SELECT ‘Page life low’ FROM sys.dm_os_performance_counters WHERE (counter_name LIKE ‘%Page life expectancy%’)AND (cntr_value<=300*4)

UNION ALL
select ‘Lock Blocks’ FROM sys.dm_os_performance_counters p1,sys.dm_os_performance_counters p2
where p1.counter_name = ‘Lock Blocks Allocated’ and p2.counter_name = ‘Lock Blocks’
AND p1.cntr_value>0 and 20< 100.*p2.cntr_value/p1.cntr_value

UNION ALL
select ‘Lock Owner Blocks’ FROM sys.dm_os_performance_counters p1,sys.dm_os_performance_counters p2
where p1.counter_name = ‘Lock Owner Blocks Allocated’ and p2.counter_name = ‘Lock Owner Blocks’
AND p1.cntr_value>0 and 20<100.*p2.cntr_value/p1.cntr_value

UNION ALL
select ‘CPU overload’ from sys.dm_os_performance_counters pc1 INNER JOIN sys.dm_os_performance_counters pc2
on pc1.counter_name = ‘CPU usage %’ AND pc2.counter_name = ‘CPU usage % base’ AND pc1.instance_name=pc2.instance_name AND pc1.[object_name]=pc2.[object_name]
WHERE pc1.cntr_value > pc2.cntr_value*0.60—more 60%
— 2.Процессы наиболее активно потребляющие ресурсы IO
— Для Job подставляются их названия

;WITH ss AS(
SELECT distinct spid,DATEDIFF(SS,s.login_time,GETDATE()) AS ss FROM sys.sysprocesses s
WHERE ISNULL(DATEDIFF(SS,s.login_time,GETDATE()),0)>0),
session_job AS (select distinct spid AS session_id,isnull(rtrim(nt_username),») AS nt_user_name,
CASE WHEN b.name IS NULL THEN rtrim(s.program_name) ELSE
‘TSQL JobStep ‘+’ [‘+rtrim(b.name)+’]’ end AS PROGRAM_NAME,
rtrim(isnull(s.cmd,»)) AS cmd
from sys.sysprocesses s
LEFT OUTER JOIN msdb.dbo.sysjobs b ON (SUBSTRING(MASTER.dbo.FN_VARBINTOHEXSTR(CONVERT(VARBINARY(16), b.JOB_ID)),1,10)) = SUBSTRING(PROGRAM_NAME,30,10))
SELECT ‘<‘+convert(varchar(20),s.spid)+’> ‘+
convert(varchar(20),ss.ss/60)+’ Min; ‘+
convert(varchar(20),convert(numeric(20,2),1.*physical_io/ss.ss))+’ IOPS ‘+
sj.nt_user_name +’ ‘+sj.program_name+’ ‘+sj.cmd
FROM sys.sysprocesses s
INNER JOIN ss on ss.spid=s.spid
INNER JOIN session_job sj on sj.session_id=s.spid
WHERE
1.*physical_io/ss.ss>0
ORDER BY 1.*physical_io/ss.ss DESC

— 3.Процессы наиболее активно использующие блокировки

select isnull(login_name,») as login_name,isnull(program_name,») as program_name,isnull(db_name(resource_database_id),») as db_name,isnull(request_mode,») as request_mode,count(*) as count_l,
case when isnull(request_mode,»)=’S’ then ‘Совмещаемая блокировка’
when isnull(request_mode,»)=’U’ then ‘Блокировка обновления’
when isnull(request_mode,»)=’X’ then ‘Монопольная блокировка’
when isnull(request_mode,»)=’IS’ then ‘C намерением совмещаемого доступа’
when isnull(request_mode,»)=’IX’ then ‘C намерением монопольного доступа’
when isnull(request_mode,»)=’IU’ then ‘C намерением обновления’
when isnull(request_mode,»)=’SIX’ then ‘Cовмещаемая с намерением монопольного доступа’
when isnull(request_mode,»)=’Sch-S’ then ‘Блокировка изменения схемы’
when isnull(request_mode,»)=’Sch-M’ then ‘Блокировка стабильности схемы’
when isnull(request_mode,»)=’BU’ then ‘Блокировка массового обновления’
when isnull(request_mode,»)=’RangeS-S’ then ‘Совмещаемая блокировка диапазона — совмещаемая блокировка ресурса; упорядочиваемый просмотр диапазона’
when isnull(request_mode,»)=’RangeS-U’ then ‘Совмещаемая блокировка диапазона — блокировка обновления ресурса; упорядочиваемый просмотр обновлений’
when isnull(request_mode,»)=’RangeI-N’ then ‘Блокировка диапазона для вставки, блокировка ресурса не определена; используется для проверки диапазонов перед вставкой новых ключей в индекс’
when isnull(request_mode,»)=’RangeX-X’ then ‘Монопольная блокировка диапазона, монопольная блокировка ресурса; используется при обновлении ключа в диапазоне’
end as Lock_Desc,
session_id
from sys.dm_tran_locks left join sys.dm_exec_sessions on session_id=request_session_id
group by isnull(login_name,»),isnull(program_name,»),isnull(db_name(resource_database_id),»),isnull(request_mode,»),session_id
order by isnull(request_mode,») DESC,count(*) DESC,isnull(db_name(resource_database_id),»)

 

Dmitry Gorchilin:

Often in the work a simple question arises, why the server is slow? What is the reason? Who exactly loads the server? What is the nature of this excess load? Where is the bottleneck, bottleneck? Yesterday I worked well and quickly-why did the productivity suddenly drop?

These simple scripts can partially localize the problem. Further it is already easier to understand, the reasons become clear and tangible.

In fact, the process of finding the reasons for the decline in productivity is a very fascinating, creative activity. It reminds me of a technical detective, formal recipes help me badly. Each server in something is individual, each has its own characteristics.

But you need to start looking for a problem with such things:

why_ms_sql_slow в текстовом документе, символы не преобразованы WP

 

Дополнить можно этим скриптом Кто грузит MS SQL Server в текущий момент?

2 comments for “Microsoft SQL Server медленно работает? Ищем узкие места, выявляем причины

  1. Дмитрий
    13.04.2017 at 15:59

    Скрипт не работает. Пришлось исправить символы комментариев, кавычек и >>

    • 13.04.2017 at 18:15

      Да, WP иногда подменяет символы. Для такого случая в конце скрипт выложен в виде текстового документа, вот он точно проверен и работает.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *