SQL Server 2005 I/O Capacity Issues

Most large scale SQL Server 2005 deployments sooner or later run into I/O bottlenecks. There are several reasons for this. First, engineers often just think about CPU and RAM when sizing "big" database servers, and second, many DBA’s are not able to completely tune the workload to minimize excessive I/O. Finally, there are often budgetary issues that prevent the acquisition of enough I/O capacity to support a large workload.

Whatever your situation, it helps if you know how recognize and measure signs of I/O pressure on SQL Server 2005. One thing you can do to help reduce I/O pressure in general is to make sure you are not under memory pressure, which will cause added I/O pressure. For large SQL Server 2005 deployments, you should make sure you are running 64-bit (so you can better use whatever RAM you have), and you should try to get as much RAM as you can afford or will fit into the machine.

At any rate, here are a few useful DMV queries for measuring signs of I/O pressure:


-- Check for IO Bottlenecks(lower is better)
    SELECT pending_disk_io_count 
    FROM sys.dm_os_schedulers
-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)
    SELECT signal_wait_time_ms=SUM(signal_wait_time_ms)
              ,'%signal (cpu) waits' = CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
              ,resource_wait_time_ms=SUM(wait_time_ms - signal_wait_time_ms)
              ,'%resource waits'= CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
    FROM sys.dm_os_wait_stats;

-- Avg Stalls (Lower is better)
    SELECT database_id, file_id , io_stall_read_ms, num_of_reads
        ,cast(io_stall_read_ms/(1.0+num_of_reads) AS numeric(10,1)) AS 'avg_read_stall_ms'
        ,io_stall_write_ms, num_of_writes
        ,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) AS 'avg_write_stall_ms'
        ,io_stall_read_ms + io_stall_write_ms AS io_stalls
        ,num_of_reads + num_of_writes AS total_io
        ,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) AS numeric(10,1)) AS 'avg_io_stall_ms'
    FROM sys.dm_io_virtual_file_stats(null,null)
    ORDER BY avg_io_stall_ms DESC

-- I/O Statistics for a single database
    SELECT file_id, num_of_reads, num_of_writes, (num_of_reads + num_of_writes) AS 'Writes + Reads', 
    num_of_bytes_read, num_of_bytes_written,
    CAST(100. * num_of_reads/(num_of_reads + num_of_writes) AS DECIMAL(10,4)) AS '# Reads Pct',
    CAST(100. * num_of_writes/(num_of_reads + num_of_writes) AS DECIMAL(10,4)) AS '# Write Pct',
    CAST(100. * num_of_bytes_read/(num_of_bytes_read + num_of_bytes_written) AS DECIMAL(10,4)) AS 'Read Bytes Pct',
    CAST(100. * num_of_bytes_written/(num_of_bytes_read + num_of_bytes_written) AS DECIMAL(10,4)) AS 'Written Bytes Pct'
    FROM sys.dm_io_virtual_file_stats(DB_ID(N'yourdatabasename'), NULL);

-- Get Top 20 executed SP's ordered by physical reads
    SELECT TOP 20 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',  
    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
    qs.total_worker_time AS 'TotalWorkerTime',
    qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
    qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, qs.creation_time,
    DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
    qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second'
    , qt.dbid --, qs.*
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    WHERE qt.dbid = 5 -- Filter by database
    ORDER BY qs.total_physical_reads DESC
Technorati Tags: ,

This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s