4

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 find the queue length to be largely meaningless. What do you see for disk writes/sec and disk reads/sec? Is this one database or several? Are data files on the same underlying disk (regardless of drive letter) as the log files? How about tempdb, OS, etc.? Or is everything funneling through a single controller and a single drive? – Aaron Bertrand Aug 15 '13 at 12:25
  • Also take a look at sys.dm_io_virtual_file_stats (technet.microsoft.com/en-us/library/ms190326.aspx), it should shed some light on possible I/O problems if that is the culprit. And I very much agree with @AaronBertrand on his point that disk queue length isn't a give-away for I/O problems. Are you actually experience performance problems? You will always have top wait types. For CXPACKET, it means your instance is utilizing parallelism. If the waits are high, and you're seeing poor parallel perf on OLTP then consider raising cost threshold for parallelism. – Thomas Stringer Aug 15 '13 at 12:32
  • @Thomas - thanks fellow SQL dude. This environment is all things to all people with a variety of apps attached, OLTP + reporting. The OLTP related activity which is happening now (which has poor performance) might be suffering from parallelism suited to a reporting environment. – the_good_pony Aug 15 '13 at 13:32
  • @Aaron - thanks fellow SQL dude. I have made some modifications to perfmon, monitoring the disk reads/sec. The database has one app attached which has the poor performance. – the_good_pony Aug 15 '13 at 13:34
  • @DBAWaffle What is your cost threshold for parallelism currently set to? The default 5? If so, that's pretty low for OLTP. – Thomas Stringer Aug 15 '13 at 13:38
2

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:

http://blogs.msdn.com/b/dpless/archive/2010/12/01/leveraging-sys-dm-io-virtual-file-stats.aspx

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.

  • That last video was really useful :) - I used some queries similar to those when trying to diagnose the issue but wasn't sure whether to be confident in my assumptions. – the_good_pony Aug 27 '13 at 21:15
  • Great, glad I could help! – Brent Ozar Aug 28 '13 at 1:24

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.