Might as well check out the chat discussion for the topic. Seem to be making time to answer more question, we'll have to see what you fine folks are up to behind the scenes. Of course I had to do a quick search for "mike" to make sure I wasn't doing anything too poorly ;-)
@Mike good to have you with us. We talk a lot of rubbish in here but some sense too and as your contributions to the site are quality I don't think your search will have turned anything incriminating :)
@MarkStoreySmith On the sofa, wish I had a scotch, have to go write up a lot of details for my social goings ons ... like our local TEDx talks that we need to start organizing, and our training sessions that I've got to setup.
I honestly had to email a client earlier along the lines of "Someone appears to have compressed the C: drive of your server. This is generally considered to be a bad idea."
@MarkStoreySmith Yeah, I'd run sp_WhoIsActive in a tight loop, dumping the data to table, while it's happening. That'll show the actual waits for each query. Just gathering the system-level waits won't show what the specific wait is for the drop-table query.
He says it's taking >5 sec on the one server - that's really a slam-dunk for sp_whoisactive there. He won't even need to run it in a loop.
The only other thing, and I hate to even recommend this, but when I'm dealing with out-of-support versions like that, I usually tell clients to try to get it to a supported SP before we start troubleshooting.
As a consultant it's easier to say, "I would hate to do hours of troubleshooting only to find that it's been fixed in a service pack - and end up billing you thousands of dollars for something you could have fixed for free. Plus you're out of support anyway."
sp_WhoIsActive will catch task-level waits. sys.dm_os_wait_stats is good, don't get me wrong, but when I'm troubleshooting one specific query, I need to see what each task is waiting on.
dba.se is a good break from looking at this reallly reallly bad customer database :) Feel free to direct me anywhere I can go to help for a little bit.. Still relative n00b with the whole thing here, just click on questions and look for new questions that haven't been answered or few answers
ouch @MarkStoreySmith - I've been independent as a consultant for about 4 months now. The more clients I meet, the more secure I end up feeling in that "there is plenty of work for us out there!" ;-) Compressing your C:\ drive is one good example. I got to send the "if you change these two little things in this ugly query (for now) you'll see a 150x-300x reduction in both duration and reads" e-mail late last night.
So @MarkStoreySmith on those Questions - what more are you looking for? Those are pretty good answers/discussions already. Not to sound daft, but what's the goal? I don't think I could add too much new and useful to either of those questions :-)
@MarkStoreySmith I was full time with a consultancy as their SQL Server practice lead but that wasn't where their heart was, so I left with them as my first client, @MarkStoreySmith
On the inaccurate row counts one, we've got repro for when it occurs but no explanation as to why its happening. My answer to my own question is "paused" while I try and figure out the behaviour.
On the insert/sort one, Martin has dug up all sorts of variations for when a sort occurs or not but we've not got to a root cause for the optimiser taking different paths
@MarkStoreySmith I just asked the inaccurate row counts on the MVP discussion list. I think Denny had intended on doing the same. We'll see if a healthy answer comes up from the others on the discussion list or the folks at Microsoft. @MarkStoreySmith
@MarkStoreySmith Paul White took the bait on the actual counts question. His answer is going where the thought process was heading anyway but provides a link to back it up as well. Paul really knows the Query Optimizer.
