Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
What wait state are you actually seeing, Network I/O? i.e., are you using a SAN? –  Eric Higgins Apr 13 '12 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. –  ConcernedOfTunbridgeWells Apr 14 '12 at 15:50
    
Its a NETAPP array connected to the sql server with Qlogic fiber HBA's. –  Edgey Apr 17 '12 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. –  MattGWagner Sep 27 '13 at 14:32
add comment

2 Answers 2

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.

share|improve this answer
    
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 '12 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 '12 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. –  Remus Rusanu Apr 17 '12 at 20:34
    
And also check the Process for IO Data Bytes/sec and see if some other process is trashing the disk. –  Remus Rusanu Apr 17 '12 at 20:34
add comment

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
OPTION  ( RECOMPILE );

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.

SELECT TOP 10
        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
( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH', 'SQLTRACE_BUFFER_FLUSH',
  'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK',
  'SLEEP_BPOOL_FLUSH', 'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT',
  'FT_IFTSHC_MUTEX', 'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
  'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
  'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
  'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
  'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
  'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'WAITFOR',
  'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN', 'RESOURCE_QUEUE' )
ORDER BY wait_time_ms DESC

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

BTW you can find many posts on how to use sp_whoisactive for troubleshooting here.

share|improve this answer
    
I just used the final script in this list - its kick ass. –  DBAWaffle Aug 15 '13 at 10:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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