patternsqlMinor
performance issue caused by IO?
Viewed 0 times
issuecausedperformance
Problem
Just curious to get an issue sanity checked . . .
I'm trying to ascertain whether a performance issue experienced by users is the result of the app, server it sits on or SQL Server.
Looking at perfmon I can see very high Avg. Disk Write Queue. Looking at SQL ASYNC_IO_COMPLETION and OLEDB are featuring as the more prominent waittypes. These suggest to me that SQL isn't the defining factor in the poor performance.
The presence of CXPACKET waittypes was high, this I believe is the result of parallelism being enabled and high fragmentation of the underlying indexes couple with out of date statistics.
What do others think? What other steps can I take to prove (or disprove) my theory that the disk is slow.
Thanks !
I'm trying to ascertain whether a performance issue experienced by users is the result of the app, server it sits on or SQL Server.
Looking at perfmon I can see very high Avg. Disk Write Queue. Looking at SQL ASYNC_IO_COMPLETION and OLEDB are featuring as the more prominent waittypes. These suggest to me that SQL isn't the defining factor in the poor performance.
The presence of CXPACKET waittypes was high, this I believe is the result of parallelism being enabled and high fragmentation of the underlying indexes couple with out of date statistics.
What do others think? What other steps can I take to prove (or disprove) my theory that the disk is slow.
Thanks !
Solution
There's a few different questions in here:
Q: "Looking at perfmon I can see very high Avg. Disk Write Queue."
That Perfmon counter isn't relevant for SQL Server anymore. SQL Server batches IO operations together, and it's normal to see big jumps here. Instead, check out the counters for Avg Disk Sec/Read and sec/Write. This tells you how fast the storage is responding to your requests. The downside is that it's only at the drive (volume, mount point) level. To get IO stats for specific files, query sys.dm_io_virtual_file_stats. David Pless has a great query here:
Link
Q: "Looking at SQL ASYNC_IO_COMPLETION and OLEDB are featuring as the more prominent waittypes. These suggest to me that SQL isn't the defining factor in the poor performance."
ASYNC_IO_COMPLETION is typically data file writes that happen in the background, asynchronously. When you insert/update/delete stuff, SQL Server has to get the data into the log file immediately - that's WRITELOG waits. It changes the data pages in memory, and then those get cached until later. ASYNC_IO_COMPLETION waits aren't holding up end users. It can indicate slow drive performance for data file writes, but that's a system bottleneck, not an end-user-facing bottleneck.
In my experience, OLEDB is usually caused by performance monitoring tools like Spotlight, SQL Sentry, Idera SQL DM, etc that are running traces and grabbing performance data over the wire.
Q: What do others think? What other steps can I take to prove (or disprove) my theory that the disk is slow.
I've got a video on how to do that here:
http://www.brentozar.com/archive/2011/08/how-prove-its-san-problem-webcast-video/
Short story - IO is probably not your biggest problem based on what you described here.
Q: "Looking at perfmon I can see very high Avg. Disk Write Queue."
That Perfmon counter isn't relevant for SQL Server anymore. SQL Server batches IO operations together, and it's normal to see big jumps here. Instead, check out the counters for Avg Disk Sec/Read and sec/Write. This tells you how fast the storage is responding to your requests. The downside is that it's only at the drive (volume, mount point) level. To get IO stats for specific files, query sys.dm_io_virtual_file_stats. David Pless has a great query here:
Link
Q: "Looking at SQL ASYNC_IO_COMPLETION and OLEDB are featuring as the more prominent waittypes. These suggest to me that SQL isn't the defining factor in the poor performance."
ASYNC_IO_COMPLETION is typically data file writes that happen in the background, asynchronously. When you insert/update/delete stuff, SQL Server has to get the data into the log file immediately - that's WRITELOG waits. It changes the data pages in memory, and then those get cached until later. ASYNC_IO_COMPLETION waits aren't holding up end users. It can indicate slow drive performance for data file writes, but that's a system bottleneck, not an end-user-facing bottleneck.
In my experience, OLEDB is usually caused by performance monitoring tools like Spotlight, SQL Sentry, Idera SQL DM, etc that are running traces and grabbing performance data over the wire.
Q: What do others think? What other steps can I take to prove (or disprove) my theory that the disk is slow.
I've got a video on how to do that here:
http://www.brentozar.com/archive/2011/08/how-prove-its-san-problem-webcast-video/
Short story - IO is probably not your biggest problem based on what you described here.
Context
StackExchange Database Administrators Q#48182, answer score: 2
Revisions (0)
No revisions yet.