THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Query Tuning Mastery at PASS Summit 2012: The Demos

For the second year in a row, I was asked to deliver a 500-level "Query Tuning Mastery" talk in room 6E of the Washington State Convention Center, for the PASS Summit. (Here's some information about last year's talk, on workspace memory.) And for the second year in a row, I had to deliver said talk at 10:15 in the morning, in a room used as overflow for the keynote, following a keynote speaker that didn't stop speaking on time. Frustrating!

Last Thursday, after very, very quickly setting up and getting sound and video checks, the rest of the talk went surprisingly smoothly. My deck--a brand new version created specifically for PASS--helped me get across the message I wanted to communicate, my demos ran without any failure, and my jokes didn't drive too many people out of the room before the end of the talk. I even received a round of applause when I managed to take a 26 minute query plan and, using a few query rewrites, deliver the same exact data in 9 seconds. That, I have to say, was pretty cool.

Here's the abstract for the session:

Query Tuning Mastery: The Art and Science of Manhandling Parallelism

As a database developer, your job boils down to one word: performance. In today's multi-core-driven world, query performance is very much determined by how well you're taking advantage of the processing power at your disposal. Are your big queries using every clock tick, or are they lagging behind? And if your queries are already parallel, can they be rewritten for even greater speed?

In this session, you'll learn to take full advantage of SQL Server query parallelism. After a terminology review and technology refresher, the session will go deep, covering T-SQL patterns that allow certain queries to scale almost linearly across your multi-core CPUs. You'll see when and why the optimizer makes a parallel plan choice and how to impact the decision. Along the way, you’ll manipulate costs and row goals, challenge generally accepted tuning practices, and take complete control of your parallel queries.

Since the talk was being broadcast live on "PASS TV," I had Paul White join me at the front of the room to moderate questions delivered via Twitter. This worked out reasonably well and I hope to do something similar in the future. Huge thanks to Paul for helping out -- and for giving me a really ugly scowl when one of my jokes fell totally flat.

Demos for the talk are attached. Let me know if you have any questions.

Thanks again to everyone who watched, either in person or at home. I had a blast. Hope you enjoyed it even half as much as I did!

Published Monday, November 12, 2012 12:00 AM by Adam Machanic

Attachment(s): Machanic_PASS2012_Manhandling_Parallelism_Demos.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Mbourgon said:

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.

November 12, 2012 1:22 AM
 

Jens Gotthardsen said:

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!!!

November 12, 2012 2:30 PM
 

NChaldysheva said:

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

November 12, 2012 9:07 PM
 

Mike Petri said:

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

November 13, 2012 9:49 AM
 

Adam Machanic said:

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

November 13, 2012 9:54 AM
 

Adam Machanic said:

Thanks all, for the kind words!

November 13, 2012 10:17 AM
 

Anders Borum said:

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?

November 13, 2012 4:41 PM
 

Adam Machanic said:

Hi Anders,

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

November 13, 2012 5:10 PM
 

Anders Borum said:

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.

November 14, 2012 8:57 AM
 

Adam Machanic said:

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

November 14, 2012 10:18 AM
 

Kumar Setty said:

Adam,

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

November 15, 2012 8:58 PM
 

Chris Yates said:

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.

November 16, 2012 10:13 PM
 

Adam Machanic said:

Glad you enjoyed it, Kumar and Chris!

November 19, 2012 2:25 PM
 

Saptagiri Tangudu said:

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.

November 20, 2012 11:08 AM
 

Eugene Karpovich said:

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

March 5, 2013 3:09 PM
 

Adam Machanic said:

@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 :-)

March 5, 2013 3:16 PM
 

Mike Good said:

Our user group (Tampa/Pinellas, FL) used video of this presentation last night to fill in for speaker who had to back out at last minute.  It worked great, your presentation was fantastic.  Dying to try some of this out now.  Thank you!  

July 17, 2013 10:46 AM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement