Review: A day of doing many things at once with @AdamMachanic

At SQLPass this year I was fortunate to attend “A day of doing many things at once: Multitasking, Parallelism, and Process distribution” by Adam Machanic (blog | twitter). This was a day long post-conference.

So, how was it?

This was a fantastic seminar. There was a really good flow to the talk, which started in CPU background and architecture, then moved through Windows Internals, SQL Server internals, and on to specifics of parallelism in queries. Then we finally moved on to administration topics, as well as different methods of process distribution. A full outline of the day is here.

I think the presentation worked very well because of the balance of theory and practice. Essentially, there was a very good ratio between ‘what, ‘why’, and ‘how’.

I’ll look back at the outline for this seminar when designing longer presentations myself.

Did I learn anything useful?

Yes! The information on plan shapes and tricks to manipulate them was incredibly interesting, and is something I know will be useful. I also learned some interesting specifics about how the DAC works, and have a much more holistic view of how SQL Server uses processors and parallelism. Check out my tweets below for a little more insight into what my day was like!

Free webcasts

Adam has some webcasts on parallelism available for download which you can watch for free.

My tweetstream from the session…

Here’s what my day was like, according to Twitter.

• Postcon fun with @AdamMachanic today! #sqlpass Processes do not run, *threads* do.
• Quick discussion of fiber mode for SQL Server: very limiting (http://bit.ly/bn6RoK)
• Thread starvation: pre-emption by high priority threads can prevent some threads from ever running.
• Threads running on client OS get a smaller amount of quantum units than on a server os (more frequent interrupt frequency)
• Three types of processor affinity: none, hard affinity, and ideal affinity
• Lots of love for sysinternals (http://bit.ly/WPxha) and theCPU-Z tool (w/ props to @BrentO for recommending http://bit.ly/1iBcg6)
• Interrupt counts include not just when a quantum expires, but also when a thread finishes.
• Lots of cool WMI queries being run from inside SSMS
• Mine is still getting even better :) RT @whimsql: Amen Tom! RT @SQLRockstar Best. Summit. Ever.
• Meeting the SQLOS! It’s a “cooperative” scheduling system: everyone’s equal
• SQLOS provides an abstraction layer so storage engine, qp, etc can all talk to it instead of directly to the OS
• Proc Affinity at sql server level may be worth testing w/ multi instances. WIth virtualization taking predominance, is less common.
• Differences between resource waits and signal waits being explained
• 484 Wait types in SQL Server 2008– plug for #sqlhelp hash tag for those with limited documentation.
• I totally just got called on in a “what feature uses a hidden scheduler” Pop Quiz. #FAIL
• @PaulRandal yep, we were all “so THAAAAAAAT’S how that works.”
• Don’t think of operators in QPs as being parallelized. Think more of each set of rows as being prallelized.
• Very few iterators are actually parallel-aware. Most do not need to be, even if being used by parallel streams.
• OH: “I trust myself, but I don’t know if you should.” <– always an appropriate comment when referring to production environment
• And now we return to our discussion of the “Big O” and the Query Processor.
• We just covered tempdb spills and @crysmanson ’s old enemy, the resource_semaphore wait type.
• Few outer rows demo showing repartitioning scheme and rows redistributed on threads– very cool
• Verrrrrrry interesting stuff with CROSS APPLY and parallelism
• Cost threshold for parallelism default is still what it was set originally in 7.5, for many contemporary systems it may be too low.
• And that makes me happy to hear since we do raise the default cost threshold for parallelism on our prod servers :)
• @AdamMachanic just actually turned it up to 11.
• If you hit THREADPOOL waits, don’t just up the max worker threads permanently, find the root cause for the situation.
• Finishing up with a monitoring parallelism section – really nice flow to the talk today!
• Piles o’ DMV fun, including the reason sys.dm_exec_requests has some funkiness: it shows wait state only for the root task
• @AdamMachanic is demoing how sp_whoisactive will display your wait types, find your tempdb contention, and wash your dishes.
• Demo of manipulating memory grants to cause a query to spill to tempdb purposefully… we’re not in kansas anymore.
• @TheSQLGuru I’ve enjoyed it a ton– great combo of really interesting demos and information.