Высокая загрузка дисковой подсистемы на сервере СУБД MS SQL Server

Краткое содержание:

Что требуется сделать

1. Подключиться к указанному серверу
2. Убедиться с помощью Монитора Ресурсов (Resource Monitor), что основную нагрузку создаёт именно сервер СУБД. Может оказаться, что проблемы медленной дисковой подсистемы никак не связаны с тяжелыми запросами в СУБД. Поэтому требуется быть уверенным, что следует рассматривать именно запросы именно к этому серверу СУБД.
3. Запустить MS Sql Server Management Studio
4. Выяснить, какие именно информационные базы создают нагрузку на диск.
5. Выяснить, какие именно запросы создают нагрузку на дисковую подсистему. 
6. Найти запрос в коде конфигурации, получить стек вызова на встроенном языке
7. Найти соответствующие сеансы в консоли администрирования найденной базы по spid (в случае, если несколько сеансов приводят к проблеме засчет частого выполнения проблемных запросов)
   • Записать номер сеанса и время начала сеанса
   • Завершить найденные сеансы
   • Проанализировать журнал регистрации с целью выяснить сценарий работы пользователя
8. Проверить наличие длительных транзакций
9. Проанализировать и исправить найденный запрос
   • Понять причину неоптимальной работы запроса
   • Исправить
10. Убедиться, что нагрузка на диск упала

Запросы, создающие нагрузку на диск

SELECT

SUM(qs.total_physical_reads) as physical_reads,

SUM(qs.total_logical_reads) as logical_reads

into T1 FROM (

select top 100000 * from

sys.dm_exec_query_stats qs

where qs.last_execution_time > (CURRENT_TIMESTAMP - '01:00:00.000')

order by qs.total_physical_reads desc

) as qs;

select top 100

(qs.total_physical_reads) as physical_reads,

(qs.total_logical_reads) as logical_reads,

qp.query_plan,

st.text,

dtb.name,

qs.*,

st.dbid

INTO T2

FROM

sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

left outer join sys.databases as dtb on st.dbid = dtb.database_id

where qs.last_execution_time > (CURRENT_TIMESTAMP - '01:00:00.000')

order by qs.total_physical_reads desc;

select

(T2.physical_reads*100/T1.physical_reads) as percent_physical_reads,

(T2.logical_reads*100/T1.logical_reads) as percent_logical_reads,

T2.*

from

T2 as T2

INNER JOIN T1 as T1

ON 1=1

order by T2.total_physical_reads desc

;

drop table T2

;

drop table T1

;

Базы, создающие нагрузку на диск

Копировать в буфер обмена

WITH DB_Disk_Reads_Stats

AS

(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_physical_reads) AS [physical_reads]

 FROM sys.dm_exec_query_stats AS qs

 CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 

              FROM sys.dm_exec_plan_attributes(qs.plan_handle)

              WHERE attribute = N'dbid') AS F_DB

 GROUP BY DatabaseID)

SELECT ROW_NUMBER() OVER(ORDER BY [physical_reads] DESC) AS [row_num],

       DatabaseName, [physical_reads], 

       CAST([physical_reads] * 1.0 / SUM([physical_reads]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Physical_Reads_Percent]

FROM DB_Disk_Reads_Stats

WHERE DatabaseID > 4 -- system databases

AND DatabaseID <> 32767 -- ResourceDB

ORDER BY row_num OPTION (RECOMPILE);

Как найти самый тяжелый (или несколько тяжелых) запрос в коде конфигурации.

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

<?xml version="1.0" encoding="UTF-8"?>

<config xmlns=" v8.1c.ru/v8/tech-log »»">

<log location="С:\Sql_Reads" history="2">

<event>

<eq property="Name" value="DBMSSQL"/>

<like property="Sql" value="%Reference5774%"/>

<like property="Sql" value="%SELECT TOP%"/>

</event>

<property name="all"/>

</log>

<plansql/>

</config>

Смысл в том, чтобы указать такие фильтры

<like property="Sql" value="%Reference5774%"/>,

которые будут включать имена таблиц в найденном вами на предыдущем шаге запросе. Если всё аккуратно сделаете, то в полученном технологическом журнале запрос у вас будет только тот, который нужен. Журнал получится небольшим. Собственно стек из кода на встроенном языке будет сразу в конце события с запросом.

Длительные транзакции

DECLARE @curr_date as DATETIME

SET @curr_date = GETDATE()

select --SESSION_TRAN.*,

SESSION_TRAN.session_id AS connectID, -- "Соединение с СУБД" в консоли кластера 1С

--TRAN_INFO.*,

TRAN_INFO.transaction_begin_time,

DateDiff(MINUTE, TRAN_INFO.transaction_begin_time, @curr_date) AS Duration, -- Длительность в минутах

TRAN_INFO.transaction_type, -- 1 = транзакция чтения-записи; 2 = транзакция только для чтения; 3 = системная транзакция; 4 = распределенная транзакция.

TRAN_INFO.transaction_state,

-- 0 = Транзакция еще не была полностью инициализирована;

-- 1 = Транзакция была инициализирована, но еще не началась;

-- 2 = Транзакция активна;

-- 3 = Транзакция закончилась. Используется для транзакций «только для чтения»;

-- 4 = Фиксирующий процесс был инициализирован на распределенной транзакции. Предназначено только для распределенных транзакций. Распределенная транзакция все еще активна, но дальнейшая обработка не может иметь место;

-- 5 = Транзакция находится в готовом состоянии и ожидает разрешения;

-- 6 = Транзакция зафиксирована;

-- 7 = Производится откат транзакции;

-- 8 = откат транзакции был выполнен.

--CONN_INFO.*,

CONN_INFO.connect_time,

CONN_INFO.num_reads,

CONN_INFO.num_writes,

CONN_INFO.last_read,

CONN_INFO.last_write,

CONN_INFO.client_net_address,

CONN_INFO.most_recent_sql_handle,

--SQL_TEXT.*,

SQL_TEXT.dbid,

db_name(SQL_TEXT.dbid) AS IB_NAME,

SQL_TEXT.text,

--QUERIES_INFO.*,

QUERIES_INFO.start_time,

QUERIES_INFO.status,

QUERIES_INFO.command,

QUERIES_INFO.wait_type,

QUERIES_INFO.wait_time,

PLAN_INFO.query_plan

FROM sys.dm_tran_session_transactions AS SESSION_TRAN

JOIN sys.dm_tran_active_transactions AS TRAN_INFO

ON SESSION_TRAN.transaction_id = TRAN_INFO.transaction_id

LEFT JOIN sys.dm_exec_connections AS CONN_INFO

ON SESSION_TRAN.session_id = CONN_INFO.session_id

CROSS APPLY sys.dm_exec_sql_text(CONN_INFO.most_recent_sql_handle) AS SQL_TEXT

LEFT JOIN sys.dm_exec_requests AS QUERIES_INFO

ON SESSION_TRAN.session_id = QUERIES_INFO.session_id

LEFT JOIN (

SELECT VL_SESSION_TRAN.session_id AS session_id,

VL_PLAN_INFO.query_plan AS query_plan

FROM sys.dm_tran_session_transactions AS VL_SESSION_TRAN

INNER JOIN sys.dm_exec_requests AS VL_QUERIES_INFO

ON VL_SESSION_TRAN.session_id = VL_QUERIES_INFO.session_id

CROSS APPLY sys.dm_exec_text_query_plan(VL_QUERIES_INFO.plan_handle, VL_QUERIES_INFO.statement_start_offset, VL_QUERIES_INFO.statement_end_offset) AS VL_PLAN_INFO) AS PLAN_INFO

ON SESSION_TRAN.session_id = PLAN_INFO.session_id

ORDER BY transaction_begin_time ASC

Наиболее часто выполняемые запросы

SELECT TOP 100 

[Execution count] = execution_count

,[Inpidual Query] = SUBSTRING (qt.text,qs.statement_start_offset/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)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Execution count] DESC;

Запросы с высокими издержками на ввод-вывод

SELECT TOP 100 

[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count

,[Total IO] = (total_logical_reads + total_logical_writes)

,[Execution count] = qs.execution_count

,[Inpidual Query] = SUBSTRING (qt.text,qs.statement_start_offset/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) 

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Average IO] DESC;

Использование кэшей сервера СУБД

SELECT TOP(100) [type], SUM(single_pages_kb) AS [SPA Mem, Kb]

FROM sys.dm_os_memory_clerks

GROUP BY [type]

ORDER BY SUM(single_pages_kb) DESC;

Использование кэшей по базам данных сервера СУБД

SELECT DB_NAME(database_id) AS DB,COUNT(row_count)*8.00/1024.00 AS MB, COUNT(row_count)*8.00/1024.00/1024.00 AS GB

FROM sys.dm_os_buffer_descriptors

GROUP BY database_id

ORDER BY MB DESC

Свободно в tempdb

SELECT SUM(unallocated_extent_page_count) AS [free pages],

(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]

FROM sys.dm_db_file_space_usage;

Список длительных транзакций

SELECT transaction_id, *

FROM sys.dm_tran_active_snapshot_database_transactions

ORDER BY elapsed_time_seconds DESC;