phone +7 (495) 66 014 66

× Общий раздел

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

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

Краткое содержание:
Наблюдаем высокую загрузку дисковой подсистемы по счетчикам Avg. Disk Sec/Read и Avg. Disk Sec/Wirte на сервере СУБД c MS SQL Server
Высокую нагрузку диагностируем как текущее стабильное (не пиковое) показание счетчиков Avg. Disk Sec/Read > 2000 (мс) или Avg. Disk Sec/Write > 2000 (мс)

Что делать?

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

Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе.

4 года 10 мес. назад #4925 от Дмитрий
Запросы, создающие нагрузку на диск
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;

Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе.

Время создания страницы: 0.063 секунд