I like Kalen Delaney's internals book:
I also like the Brent Ozar / Christian Bolton book:
For T-SQL tuning, you can't get much a better T-SQL wizard than Itzik Ben-Gan:
And query tuning overall, Grant Fritchey and Sajal Dam:
There are also some interesting solutions and opinions presented in various chapters in the two MVP Deep Dives titles (from which all proceeds go to two charities, War Child and Operation Smile):
And a couple of the Red Gate titles @Kev left out, but which are both excellent:
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.
Best Answer
Using a
DELETE+INSERTpattern instead of also involvingUPDATEs can be highly problematic. IMO, it's an anti-pattern. In 99.9% of cases, it's better to useUPDATESwhen possible.Usually the
DELETE+INSERTpattern is implemented such that the subset of rows is blindly deleted in the target, which can create a tremendous amount of unnecessary write activity because the data is always re-written even if nothing changed.Not only does this put load on the I/O subsystem, but it can also kill data access concurrency unless snapshot isolation is used (which will multiply the wasted disk write workload). If the default isolation level is used and the data is concurrently read, this pattern can produce deadlocks very readily and in large quantity.
Having said that, one needs to implement
UPDATEs carefully because they still have the potential to rewrite rows that haven't changed. Thankfully that's solved easily with a bit of extra code. All in all, it's well worth the effort to useUPDATEs despite the slight added code complexity.Depending on what you're doing, it may be easier and more reliable to send the entire desired state as a whole to the database using something like a table-valued parameter and sorting things out there instead of on the client side.