Выросли очереди на диски на сервере базы данных mssql. Задача найти запросы топ по чтению с диска.
Нагрузка на диск на чтение
По графику видно что нагрузка выросла. Диски nvme и поменять на более производительные нет возможности
Запрос по топу чтения
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);
Теперь по запросам
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 ;
Теперь надо посмотреть как часто они выполняются на сервере sql . настроено логирование запросов с помощью sp_whoisactive
Так что ищем в таблице
SELECT *
FROM [DBA].[dbo].[WhoIsActive]
WHERE
--CONVERT(CHAR(8),start_time,8) BETWEEN '09:20:00' AND '18:40:00' AND
datepart(YEAR,start_time) BETWEEN 2025 AND 2025 and
datepart(month,start_time) BETWEEN 01 AND 01 and
datepart(day,start_time) BETWEEN 21 AND 21
--and [dd hh:mm:ss.mss] > '00 01:00:00.0000'
and
CAST([sql_text] AS nvarchar(max)) LIKE ('%BDSM.dbo.Record%')
and
-- CAST([sql_text] AS nvarchar(max)) LIKE ('%InventTable%') AND
--sql_text LIKE ('%InventTable%') AND
database_name IN ('base')
ORDER BY start_time
