SQL Server : statistiques d'E/S par base et par lecteur
Mesurer les lectures et écritures par base et par lecteur, la latence des fichiers et les requêtes les plus coûteuses en lecture.
Un ensemble de requêtes basées sur sys.dm_io_virtual_file_stats pour analyser les entrées-sorties de SQL Server : totaux depuis le démarrage, échantillon sur 5 minutes, répartition par lettre de lecteur, latence moyenne par lecteur, et top 25 des requêtes les plus coûteuses en lecture.
Totaux par base depuis le démarrage
--Shows the total I/O for each database
SELECT name AS 'Database Name'
,SUM(num_of_reads) AS 'Number of Read'
,SUM(num_of_writes) AS 'Number of Writes'
FROM sys.dm_io_virtual_file_stats(NULL, NULL) I
INNER JOIN sys.databases D
ON I.database_id = d.database_id
GROUP BY name ORDER BY 'Number of Read' DESC;
Échantillon sur 5 minutes par base
Ce bloc attend 5 minutes (WAITFOR) entre deux lectures : la session reste occupée pendant ce temps.
--Shows the amount of I/O performed by each database in the last 5 minutes
DECLARE @Sample TABLE (
DBName varchar(128)
,NumberOfReads bigint
,NumberOfWrites bigint)
INSERT INTO @Sample
SELECT name AS 'DBName'
,SUM(num_of_reads) AS 'NumberOfRead'
,SUM(num_of_writes) AS 'NumberOfWrites'
FROM sys.dm_io_virtual_file_stats(NULL, NULL) I
INNER JOIN sys.databases D
ON I.database_id = d.database_id
GROUP BY name
WAITFOR DELAY '00:05:00.000';
SELECT FirstSample.DBName
,(SecondSample.NumberOfReads - FirstSample.NumberOfReads) AS 'Number of Reads'
,(SecondSample.NumberOfWrites - FirstSample.NumberOfWrites) AS 'Number of Writes'
FROM
(SELECT * FROM @Sample) FirstSample
INNER JOIN
(SELECT name AS 'DBName'
,SUM(num_of_reads) AS 'NumberOfReads'
,SUM(num_of_writes) AS 'NumberOfWrites'
FROM sys.dm_io_virtual_file_stats(NULL, NULL) I
INNER JOIN sys.databases D
ON I.database_id = d.database_id
GROUP BY name) AS SecondSample
ON FirstSample.DBName = SecondSample.DBName
ORDER BY 'Number of Reads' DESC;
Répartition par lettre de lecteur
--Displaying I/O statistics by physical drive letter
SELECT left(f.physical_name, 1) AS DriveLetter,
DATEADD(MS,sample_ms * -1, GETDATE()) AS [Start Date],
SUM(v.num_of_writes) AS total_num_of_writes,
SUM(v.num_of_bytes_written) AS total_num_of_bytes_written,
SUM(v.num_of_reads) AS total_num_of_reads,
SUM(v.num_of_bytes_read) AS total_num_of_bytes_read,
SUM(v.size_on_disk_bytes) AS total_size_on_disk_bytes
FROM sys.master_files f
INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) v
ON f.database_id=v.database_id and f.file_id=v.file_id
GROUP BY left(f.physical_name, 1),DATEADD(MS,sample_ms * -1, GETDATE());
Latence moyenne par lecteur
--Calculated Disk Latency for your different database drives
SELECT LEFT(physical_name, 1) AS drive,
CAST(SUM(io_stall_read_ms) /
(1.0 + SUM(num_of_reads)) AS NUMERIC(10,1))
AS 'avg_read_disk_latency_ms',
CAST(SUM(io_stall_write_ms) /
(1.0 + SUM(num_of_writes) ) AS NUMERIC(10,1))
AS 'avg_write_disk_latency_ms',
CAST((SUM(io_stall)) /
(1.0 + SUM(num_of_reads + num_of_writes)) AS NUMERIC(10,1))
AS 'avg_disk_latency_ms'
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
AND mf.file_id = divfs.file_id
GROUP BY LEFT(physical_name, 1)
ORDER BY avg_disk_latency_ms DESC;
Top 25 des requêtes les plus coûteuses en lecture
SELECT TOP 25 cp.usecounts AS [execution_count]
,qs.total_worker_time AS CPU
,qs.total_elapsed_time AS ELAPSED_TIME
,qs.total_logical_reads AS LOGICAL_READS
,qs.total_logical_writes AS LOGICAL_WRITES
,qs.total_physical_reads AS PHYSICAL_READS
,SUBSTRING(text,
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 + 1 END,
CASE WHEN statement_end_offset = 0
OR statement_end_offset = -1
OR statement_end_offset IS NULL
THEN LEN(text)
ELSE statement_end_offset/2 END -
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 END + 1
) AS [Statement]
FROM sys.dm_exec_query_stats qs
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
ORDER BY qs.total_logical_reads DESC;
Remarques
- Les compteurs de
sys.dm_io_virtual_file_statssont cumulés depuis le démarrage de l'instance ; les sections « échantillon » donnent l'activité sur une période précise. - La répartition par lettre de lecteur regroupe les fichiers selon le premier caractère de leur chemin : les points de montage et les chemins UNC ne sont pas séparés.
- Le top 25 vient du cache de plans : les instructions absentes du cache n'apparaissent pas.