Performance Monitor- Perfmon Counters & Details
High Disk I/O from sql server or is High disk I/O slowing sql server?
Have a look at the following perfmon counters:
Page lookups/sec
Page reads/sec
Readahead pages/sec
SQL Server, Access Methods Object
Full Scans/sec
Range Scans/sec
Skipped Ghosted Records/sec
SQL Server, Wait Statistics Object
Page IO latch waits
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 atsys.dm_exec_query_stats for entries with high physical reads counts. They could quickly pinpoint the culprit.
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