0

I expect to see the disk queue move on the data, tempdb, log and backup volumes.

I do not expect to see the OS drive (C:) which has zero SQL files present, show disk queue impact.

Assume it is to the OS paging file, or ?

Suggestions on how to chase?

This is SQL 2014 on Win2012-R2/

  • Are all db's, logs and resources installed on other drives? Where is sql installed? – Sir Swears-a-lot Jan 25 '15 at 8:32
  • All dbs on other drives. Sql itself on e: all resources i know of on other drives. – Jonesome Reinstate Monica Jan 25 '15 at 16:56
1

Run Performance Monitor, then click Resource Monitor on the opening page. You can then use the disks tab to see all the action. Look under Disk Activity. It will show you the culprits.

2

Use Microsoft Performance Analyzer or Media Experiance Analyzer with Windows Performance Recorder. Any of these is capable of answering your question in great detail, quickly and foul-proof, using system ETW capabilities.

This article is a good walk through: Analyzing Storage Performance using the Windows Performance Analysis ToolKit.

At the simplest, it will be something like this:

c:\>Xperf -on FileIO+Latency+DISK_IO+DISK_IO_INIT+SPLIT_IO
 # repro your scenario
c:\>Xperf -d repro.etl
c:\>Xperf repro.etl

Use the Hard Faults view to track paging spikes, use Disk Utilization to correlate paging and disk IO, use Disk Utilization per Process to identify disk users.

You can take it one step further with Windows Performance Analyzer, WPA can open the same .etl file Xperf Viewer can, but it has much nicer graphs and intelligence built-in.

  • Yes, this is great! Used resource monitor. One interesting thing: SQL Server IO to mdb and ldb is visible in the graphs, but NOT in the "disk activity" breakdown. – Jonesome Reinstate Monica Jan 26 '15 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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