Query Tuning Mastery at PASS Summit 2012: The Demos

re: Query Tuning Mastery at PASS Summit 2012: The Demos

Mbourgon — Mon, 12 Nov 2012 06:22:42 GMT

Having watched this twice (thanks PASStv!) I can honestly say this is a stunner. I personally think we'll hear the word repartitioning and parallel apply a lot in the next couple years. Congrats, Adam, you've done a great thing.

re: Query Tuning Mastery at PASS Summit 2012: The Demos

Jens Gotthardsen — Mon, 12 Nov 2012 19:30:40 GMT

I attended your session at pass and I must say I was impressed by how you got to figure all this out. That was really manhandling :)

Thanks for a great show!!!

re: Query Tuning Mastery at PASS Summit 2012: The Demos

NChaldysheva — Tue, 13 Nov 2012 02:07:08 GMT

I attended your session, was among them who applauded and since that think where and how I can implement this art. Great job, thanks

re: Query Tuning Mastery at PASS Summit 2012: The Demos

Mike Petri — Tue, 13 Nov 2012 14:49:30 GMT

I also attended the session, and in my opinion, yours was the best session of the entire summit. Thanks for all the amazing insight Adam!

Mike

Query Tuning Mastery at PASS Summit 2012: The Video

Adam Machanic — Tue, 13 Nov 2012 14:54:49 GMT

An especially clever community member was kind enough to reverse-engineer the video stream for me, and

re: Query Tuning Mastery at PASS Summit 2012: The Demos

Adam Machanic — Tue, 13 Nov 2012 15:17:41 GMT

Thanks all, for the kind words!

re: Query Tuning Mastery at PASS Summit 2012: The Demos

Anders Borum — Tue, 13 Nov 2012 21:41:38 GMT

Adam,

thanks for sharing the talk with those of us not able to attend the session. The techniques you presented in the talk are definitely going to make a difference for our team going forward - and driving performance is always important.

We're deploying more and more instances of our software on Windows Azure / SQL Azure. Is there anything regarding these techniques we should keep in mind for SQL Azure or is it fair to expect the query analyzer to work equally for SQL Server enterprise (supports parallelization) and SQL Azure?

re: Query Tuning Mastery at PASS Summit 2012: The Demos

Adam Machanic — Tue, 13 Nov 2012 22:10:10 GMT

Hi Anders,

No clue; I've never touched Azure. Does it even support parallel queries?

re: Query Tuning Mastery at PASS Summit 2012: The Demos

Anders Borum — Wed, 14 Nov 2012 13:57:48 GMT

Adam,

I checked up on SQL Azure and it seems MAXDOP is set to 1 (which makes sense per the multi tenant environment and requirement of protecting shared resources from rogue queries).

Another question on parallelism: I'm currently working with some of our queries, checking if they generate serialized or parallelized query plans. I'm running a typical query with a couple of joins against a table with 250.000+ rows and regardless of using your cross apply or other technique, the query analyzer keeps generating serialized query plans.

I'm running against an enterprise version of SQL Server 2008 R2 and I've checked that the MAXDOP setting for the instance is set to 0.

re: Query Tuning Mastery at PASS Summit 2012: The Demos

Adam Machanic — Wed, 14 Nov 2012 15:18:46 GMT

Hi Anders,

Lack of a parallel plan is due either to inhibition or cost. So your job is to figure out which one it is :-) ... Two things to do:

A) Check out the cost for the serial plan. Is it greater than the server-level cost threshold for parallelism? If not, and a parallel plan is appropriate, you'll have to figure out how to increase the estimated cost for the query. (I've done this in the past primarily by either creating nonsense extra sorts on small sets where a sort doesn't matter, or by UNIONing in huge tables with OPTIMIZE FOR set to include all of the rows, but runtime values set to ignore them.)

B) If the serial cost is greater than the threshold, then enable the trace flag I demoed in the talk, and if you get a parallel plan you'll know that you have to manipulate costs a bit to get the optimizer to play ball. If you still don't get a parallel plan, you'll know that you have an inhibitor somewhere in the query. You can check the list of inhibitors from the session, although I don't guarantee that it's 100% complete; there may be cases I am not aware of.

--Adam

re: Query Tuning Mastery at PASS Summit 2012: The Demos

Kumar Setty — Fri, 16 Nov 2012 01:58:50 GMT

Adam,

I attended your session in SQLPASS, it was an excellent and the best one.

re: Query Tuning Mastery at PASS Summit 2012: The Demos

Chris Yates — Sat, 17 Nov 2012 03:13:33 GMT

I was able to view this on PASS TV, up there with one of the best I saw. I had a colleague who attended in person and he was blown away, I wanted to pass on his appreciation for your efforts and for mine. Many thanks.

re: Query Tuning Mastery at PASS Summit 2012: The Demos

Adam Machanic — Mon, 19 Nov 2012 19:25:42 GMT

Glad you enjoyed it, Kumar and Chris!

re: Query Tuning Mastery at PASS Summit 2012: The Demos

Saptagiri Tangudu — Tue, 20 Nov 2012 16:08:07 GMT

Adam-

I thoroughly enjoyed the talk. Especially, a big slide that talks about how algorithms don't scale, which I believe most programmers don't think about as much.

Have you posted the slides? I am not able to find them.

re: Query Tuning Mastery at PASS Summit 2012: The Demos

Eugene Karpovich — Tue, 05 Mar 2013 20:09:08 GMT

Hi Adam, I attended your PASS 2012 session in person, and I also downloaded it and watched a few times afterwards.

Just wanted to let you know that I recently used your technique/trick with using TOP(big_number) to eliminate threads skew to solve a serious performance issue with one of the DW-type reports at the production client I am currently working on (a huge NY-based hedge fund).

And the results were really really good.

So, I just wanted to thank you for sharing your knowledge and insight.

I need to tell that I don't feel I really understand all the internals of how this trick works, but I will try to dig into it further.

Thanks again

Eugene

re: Query Tuning Mastery at PASS Summit 2012: The Demos

Adam Machanic — Tue, 05 Mar 2013 20:16:24 GMT

@Eugene: Great news! Huge NY-based hedge fund? I'm happy to accept, as gratitude, a small percentage of any alpha the fix helped to generate :-)