Levy I/O:n selvittäminen

Huom: Tämä artikkeli käsittelee edistyneempiä optimointeja SQL Server tietokannassa, joita ei normaaliolosuhteissa ole tarpeen tehdä.

Huom: Tämä artikkeli voi sisältää vanhentuneita tietoja.

Perustuu artikkeliin: https://www.databasejournal.com/features/mssql/finding-the-source-of-your-sql-server-io.html

Listing 1: 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;

Listing 2: 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;

The code in Listing 2 determines the amount of I/O performed by database for the last 5 minutes. In order to capture the amount of I/O performed for last 5 minutes interval the code in listing 2 needed to capture 2 I/O samples from the sys.dm_io_virtual_file_stats DMF. The first sample collected was placed into a table variable and then statistics returned from the second sample were used with the statistics in the table variable to calculate how much I/O each database used in the five minute interval.

Identifying IO by Physical Disk Drive

If you have your SQL Server databases spread out across many SQL Server drives you might like to know which drive is the busiest from an I/O perspective. This is easy to do by joining sys.dm_io_virtual_file_stats with sys.master_files so you can get the physical name of each database file. The code in listing 3 displays the number read and write IOs by drive letter.

Listing 3: 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());

Disk Latency

Disk performance is important. The better your disks perform the better your database can perform I/O for all the different queries that are executed. Disk Latency is a way to measure how long it takes for an I/O to be completed. You can measure your disk latency by using the different io_stall columns available in sys.dm_io_virtual_file_stats DMF. The code in listing 4 demonstrates how to calculate the average read and write disk latency for different drive letters.

Listing 4: 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;

Identifying Those Queries that Are Using all Your I/O

I've written about how to find resource intensive queries before, but I thought it would be good to providing you with some code that can be used to determine which queries are performing lots of I/O. The code in Listing 5 uses the information in sys.dm_exec_query_stats to identify the top 25 most expensive queries by Read I/O.

Listing 5: Display the Top 25 Most expensive read I/O queries

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;