I've been arguing with a DBA and a couple hardware guys about performance issues on our SQL server. Normally everything is fine, however over the past few weeks we have been having huge lag spikes in sql server. Its clear that SQL Server is waiting on disk I/O. But I keep getting told that it is beacuse SQL Server is asking for abnormally high I/O. Which isn't the case. I can see from what is running that there is nothing out of the normal, and all the DBA cares to look at is what is causing the blocking and so on, which is useless. For instance the major thing we see backing up is operation on the ASPState database, which we are using to manage the ASP Session State on the web servers. These operations are normally never seen on Sp_who2 active results because they occur so quickly. The database is in simple recovery mode and logging is miminal. However during these lag spikes we can see alot select and update operations on the database being blocked or waiting. I'm sure what is going on is that someone or some job is running something that is causing heavey disk usage on the raid arrays used for that databases log and data files. The problem is proving it, since no one wants to admit they are doing something that is killing our website.

My question is what performance counters or whatever can I log that will help show that SQL server is waiting on I/O, but not because its asking for more than normaly, instead beacuse the disk is to busy to respond to the requests from sql server as quickly as it normally would?

  • 3
    What wait state are you actually seeing, Network I/O? i.e., are you using a SAN? Apr 13, 2012 at 18:17
  • Check to see if you have any queries that are dominating resource usage on the DB server. If there are, try tuning those. If you don't have any badly behaving queries, then high PAGEIOLATCH waits will typically indicate your system is I/O bound. Also, as @EricHiggins says, SANs are often slow and cause performance issues with databases. Apr 14, 2012 at 15:50
  • Its a NETAPP array connected to the sql server with Qlogic fiber HBA's.
    – Edgey
    Apr 17, 2012 at 18:14
  • I know this is a relatively old question, and this won't directly fix your issue... but we switched to aspnet_state.exe for session state and saw a great load off of our SQL Server. It's not well documented but pretty easy to setup. Sep 27, 2013 at 14:32
  • So what did you/the DBA end up doing and what was the problem?
    – Mukus
    May 21, 2015 at 0:04

3 Answers 3


Have a look at the following perfmon counters:

SQL Server driving a high number of IO requests would be corroborated with a high number scans, increase in page lookups and page reads and high page IO latch waits. Is worth trying a look at sys.dm_exec_query_stats for entries with high physical reads counts. They could quickly pinpoint the culprit.

In general approaching the problem as a performance troubleshooting problem, following a methodlogy like Waits and Queues is the right approach. You DBA seems to be doing the right thing so you should listen to him.

  • I don't have a problem with the DBA he is one of the best DBA's I've worked with. And he's given me a list of high blocking stored procedures. But as I mentioned one of the procs that is causing a lot of blocking is "TempUpdateStateItemLong" which is a proc used by hte SQL Session state store. Its a MS proc, and it only updates a single table by the sessionID which is the indexed primary key on the table. Also at most this table has 2000-3000 records, so updates really should take no time at all.
    – Edgey
    Apr 17, 2012 at 18:18
  • This is a good place to start. We are still running SQL Server 2000, we are in the process of upgrading but that won't happen for a few more months, so I don't have the PAge IO Latch waits counter to look at. Thanks again.
    – Edgey
    Apr 17, 2012 at 18:24
  • Note that blocking per-se does not imply high IO. It could be lock contention, and that would affect the table no matter the size, specially if optimizer chooses a table scan based plan. Apr 17, 2012 at 20:34
  • And also check the Process for IO Data Bytes/sec and see if some other process is trashing the disk. Apr 17, 2012 at 20:34

To start use Glenn Berry's Diagnostic queries and Adam Machanic's SP_Whoisactive to find out whats really happening.

First see which database files have the most IO bottleneck by running this query(Query by Glenn Berry)

SELECT  DB_NAME(fs.database_id) AS [Database Name] ,
        mf.physical_name ,
        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) AS fs
        INNER JOIN sys.master_files AS mf WITH ( NOLOCK ) ON fs.database_id = mf.database_id
                                                             AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC

Then run this query to see the top ten events your server is waiting on(query by Jonathan Kehayias). You will also find similar query from Glenn Berry diagnostic queries.

        wait_type ,
        max_wait_time_ms wait_time_ms ,
        signal_wait_time_ms ,
        wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
        100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS percent_total_waits ,
        100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( ) AS percent_total_signal_waits ,
        100.0 * ( wait_time_ms - signal_wait_time_ms )
        / SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM    sys.dm_os_wait_stats
WHERE   wait_time_ms > 0 -- remove zero wait_time
        AND wait_type NOT IN -- filter out additional irrelevant waits
ORDER BY wait_time_ms DESC

Once you have this information at hand it would be much easier to troubleshoot the problem.

  • 1
    I just used the final script in this list - its kick ass. Aug 15, 2013 at 10:08

"The problem is proving it", rightly said. Take a look at SQL Server: Minimize Disk I/O

It is talking about following DMV



  1. How to examine IO subsystem latencies from within SQL Server
  2. Glenn Berry's SQL Server Performance - sys.dm_io_pending_io_requests

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.