SQL-Ex blog

Эти скрипты не привязаны к какой-то одной версии SQL Server, но некоторые из них могут не работать на старых версиях SQL Server, которые уже не поддерживались, когда была опубликована эта статья. Другие могут не работать в облачной среде, например, Azure DB или Azure Managed Instance.

Что выполняется прямо сейчас?

Когда производительность SQL Server падает, одним из первых шагов является выяснение операций, которые он выполняет прямо сейчас. Одним из лучших способов сделать это - выполнить хранимую процедуру sp_whoisactive. Она отличается от sys.sysprocesses, поскольку показываются только процессы, выполняющие запросы прямо сейчас, а не используемые SPID.

Загрузите sp_whoisactive непосредственно из источника на GitHub

EXEC sp_whoisactive;

В первом столбце выводится продолжительность запроса, поэтому вы можете не обращать внимания на запросы, которые выполняются лишь доли секунды.

Блокировки и блокирование

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

SELECT * FROM sys.sysprocesses 

WHERE blocked > 0 

  OR SPID IN (SELECT Blocked FROM sys.sysprocesses);

Столбец "blocked" показывает 0, когда SPID не блокируется, и показывает блокирующий SPID при наличии блокировки. Эти запросы выводят все процессы, которые блокируются или которые блокируют.



В этом примере SPID 60 блокируется SPID 86. SPID 86 не блокируется и является проблемным процессом.

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

DBCC OPENTRAN();

Вот пара примеров ее выполнения: первый показывает открытую транзакцию, а второй говорит об отсутствии открытых транзакций.

Производительность запросов

Последние дорогие запросы

Иногда вы получаете предупреждение о низкой производительности, но оно пропадает, прежде чем вы сможете запустить sp_whoisactive. К счастью, вы можете обратиться к кэшу планов, чтобы выяснить, какие запросы с высокой стоимостью выполнялись за последнее время.

;WITH qs AS (

  SELECT TOP 10 

    total_worker_time/execution_count AvgCPU

  , total_elapsed_time/execution_count AvgDuration

  , (total_logical_reads + total_physical_reads)/execution_count AvgReads

  , execution_count

  , sql_handle

  , plan_handle

  , statement_start_offset

  , statement_end_offset

  FROM sys.dm_exec_query_stats

  WHERE execution_count > 5

    AND min_logical_reads > 100

    AND min_worker_time > 100

  ORDER BY (total_logical_reads + total_physical_reads)/execution_count DESC)

SELECT

  AvgCPU

, AvgDuration

, AvgReads

, execution_count

 ,SUBSTRING(st.TEXT, (qs.statement_start_offset/2) + 1, 

            ((CASE qs.statement_end_offset  

                WHEN -1 THEN DATALENGTH(st.TEXT)

                ELSE qs.statement_end_offset  

              END - qs.statement_start_offset)/2) + 1) StatementText

 ,query_plan ExecutionPlan

FROM 

  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 

  AvgDuration DESC;

Статистика ожиданий

SQL Server отслеживает ожидания каждого процесса, выполняемого сервером. Ожидание каждого типа начинается с 0мс, когда запускается служба, и подсчитывается с этого момента. Чтобы понять, что происходит прямо сейчас, вы должны сравнивать числа от одного момента времени до другого. Следующий скрипт сделает именно это, сравнив DMV со своим снимком, сделанным 15 секундами ранее.

SELECT 

  wait_type 

, waiting_tasks_count

, signal_wait_time_ms

, wait_time_ms

, SysDateTime() AS StartTime

INTO 

  #WaitStatsBefore 

FROM 

  sys.dm_os_wait_stats 

WHERE 

  wait_type NOT IN ('SLEEP_TASK','BROKER_EVENTHANDLER','XE_DISPATCHER_WAIT','BROKER_RECEIVE_WAITFOR', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT','REQUEST_FOR_DEADLOCK_SEARCH','SQLTRACE_INCREMENTAL_FLUSH_SLEEP','SQLTRACE_BUFFER_FLUSH','LAZYWRITER_SLEEP','XE_TIMER_EVENT','XE_DISPATCHER_WAIT','FT_IFTS_SCHEDULER_IDLE_WAIT','LOGMGR_QUEUE','CHECKPOINT_QUEUE', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'BROKER_EVENTHANDLER', 'SLEEP_TASK', 'WAITFOR', 'DBMIRROR_DBM_MUTEX', 'DBMIRROR_EVENTS_QUEUE', 'DBMIRRORING_CMD', 'DISPATCHER_QUEUE_SEMAPHORE','BROKER_RECEIVE_WAITFOR', 'CLR_AUTO_EVENT', 'DIRTY_PAGE_POLL', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'ONDEMAND_TASK_QUEUE', 'FT_IFTSHC_MUTEX', 'CLR_MANUAL_EVENT', 'SP_SERVER_DIAGNOSTICS_SLEEP', 'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP','CLR_SEMAPHORE','DBMIRROR_WORKER_QUEUE','SP_SERVER_DIAGNOSTICS_SLEEP','HADR_CLUSAPI_CALL','HADR_LOGCAPTURE_WAIT','HADR_NOTIFICATION_DEQUEUE','HADR_TIMER_TASK','HADR_WORK_QUEUE','REDO_THREAD_PENDING_WORK','UCS_SESSION_REGISTRATION','BROKER_TRANSMITTER','SLEEP_SYSTEMTASK','QDS_SHUTDOWN_QUEUE');--Это серия не относящейся к делу статистики ожидания.



WAITFOR DELAY '00:00:15'; --15 секунд

 

SELECT 

  a.wait_type 

, a.signal_wait_time_ms - b.signal_wait_time_ms AS CPUDiff 

, (a.wait_time_ms - b.wait_time_ms) - (a.signal_wait_time_ms - b.signal_wait_time_ms) AS ResourceDiff

, a.waiting_tasks_count - b.waiting_tasks_count AS waiting_tasks_diff

, CAST(CAST(a.wait_time_ms - b.wait_time_ms AS FLOAT) / (a.waiting_tasks_count - b.waiting_tasks_count) AS DECIMAL(10,1)) AS AverageDurationMS

, a.max_wait_time_ms max_wait_all_timeMS

, DATEDIFF(ms,StartTime, SysDateTime()) AS DurationSeconds

FROM 

  sys.dm_os_wait_stats a 

    INNER JOIN 

  #WaitStatsBefore b ON a.wait_type = b.wait_type 

WHERE 

  a.signal_wait_time_ms <> b.signal_wait_time_ms

    OR 

  a.wait_time_ms <> b.wait_time_ms

ORDER BY 3 DESC;

Статистика по задержкам подсистемы ввода/вывода

SQL Server отслеживает задержки при взаимодействии с данными и файлами журналов. Подобно статистике ожиданий, эти счетчики стартуют с 0 при запуске службы. Следующий скрипт создает временную таблицу для хранения снимка чисел, выжидания 15 секунд и просмотра изменений. Я обычно комбинирую это с запросом статистики ожиданий, поэтому мне необходима только одна задержка.

SELECT 

  b.name

, a.database_id

, a.[FILE_ID]

, a.num_of_reads

, a.num_of_bytes_read

, a.io_stall_read_ms

, a.num_of_writes

, a.num_of_bytes_written

, a.io_stall_write_ms

, a.io_stall

, GetDate() AS StartTime

INTO

  #IOStatsBefore

FROM 

  sys.dm_io_virtual_file_stats(NULL, NULL) a 

    INNER JOIN 

  sys.databases b ON a.database_id = b.database_id;

 

 

WAITFOR DELAY '00:00:15'

 

SELECT

  a.name DatabaseName

, a.[FILE_ID]

, (b.io_stall_read_ms - a.io_stall_read_ms)/ CAST(1000 as DECIMAL(10,1)) io_stall_read_Diff 

, (b.io_stall_write_ms - a.io_stall_write_ms)/ CAST(1000 as DECIMAL(10,1)) io_stall_write_Diff 

, (b.io_stall - a.io_stall)/ CAST(1000 as DECIMAL(10,1)) io_stall_Diff 

, DATEDIFF(s,StartTime, GETDATE()) AS DurationSeconds

FROM 

  #IOStatsBefore a

    INNER JOIN 

  sys.dm_io_virtual_file_stats(NULL, NULL) b ON a.database_id = b.database_id AND a.[file_id] = b.[file_id]

ORDER BY

  a.name

, a.[FILE_ID];

Статистика ЦП

SQL Server естественным образом отслеживает историю использования ЦП экземпляра раз в минуту за последние 250 минут. Вы можете получить эти показатели с помощью следующего запроса. Если производительность упала, сравните последние 30 минут с предшествующими 220, чтобы увидеть не выросло ли использование ЦП в период, когда пользователи пожаловались на проблемы производительности.

;WITH XMLRecords AS (

SELECT 

         DATEADD (ms, r.[timestamp] - sys.ms_ticks,SYSDATETIME()) AS record_time

       , CAST(r.record AS XML) record

       FROM 

         sys.dm_os_ring_buffers r  

           CROSS JOIN 

         sys.dm_os_sys_info sys  

       WHERE   

         ring_buffer_type='RING_BUFFER_SCHEDULER_MONITOR' 

           AND 

         record LIKE '%%')

 SELECT 

   100-record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemUtilization

 , record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization

 , record_time

 FROM XMLRecords;

Метрики памяти

Используйте следующий запрос для записи ожидаемого времени жизни страницы в SQL Server. Если вы используете именованный экземпляр, ваш столбец object_name нужно будет изменить на имя экземпляра вместо "SQL Server". Помните, чем больше ожидаемое время жизни страницы, тем лучше, но без базового уровня трудно сказать, что для вас хорошо, а что плохо.

SELECT  

  LEFT(counter_name, 25) CounterName

, CASE counter_name 

    WHEN 'Stolen pages' THEN cntr_value/128 --8Кб страницы/128 = Мб 

    WHEN 'Stolen Server Memory (KB)' THEN cntr_value/1024 --Кб/1024 = Мб 

    ELSE cntr_value

  END CounterValue_converted_to_MB

FROM 

  sys.dm_os_performance_counters

WHERE 

  OBJECT_NAME = N'SQLServer:Buffer Manager' 

    AND 

  counter_name = 'Page life expectancy';

Емкость диска

Иногда заполненный диск может вызвать проблемы производительности. Используйте этот запрос для быстрого анализа всех логических томов, содержащих, по крайней мере, один файл данных или журнала SQL Server.

SELECT DISTINCT 

  vs.volume_mount_point Drive

, vs.logical_volume_name

, vs.total_bytes/1024/1024/1024 CapacityGB

, vs.available_bytes/1024/1024/1024 FreeGB

, CAST(vs.available_bytes * 100. / vs.total_bytes AS DECIMAL(4,1)) FreePct 

FROM 

  sys.master_files mf

    CROSS APPLY 

  sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs;

Ссылки по теме

1. Блокировки, блокирование и тупики в SQL Server


2. Команды DBCC в SQL Server: DBCC OPENTRAN


3. Интеллектуальный анализ кэша планов SQL Server - атрибуты плана


4. Советы по настройке производительности SQL-запросов


5. 5 причин, которые следует рассмотреть при падении производительности запроса


6. Данные монитора производительности SQL Server: введение и использование