Adam Machanic : parallelism

More Fun in Atlanta: Parallelism at SQL Saturday 220

Adam Machanic — Mon, 25 Feb 2013 19:25:00 GMT

May 18, SQL Saturday returns yet again to the Atlanta area. At this point I've become a bit of a regular at Atlanta's events; this will be my third one in a row. The team that puts them together is amazing, and produces top quality, super fun and educational...(read more)

SQL Saturday 111: Manhandling Parallelism - Demos

Adam Machanic — Mon, 16 Apr 2012 01:30:00 GMT

Another year, another fantastic Atlanta SQL Saturday. Hats off to the team that created the event for delivering a top notch day for the attendees. Thanks to everyone who attended my "Query Tuning Mastery: The Art and Science of Manhandling Parallelism"...(read more)

SQLbits London 2012 - Demos

Adam Machanic — Wed, 04 Apr 2012 00:50:00 GMT

Thanks to everyone who attended my sessions last Friday and Saturday at SQLbits! It was great to meet many new people, not to mention spending some time exploring one of my favorite cities, London. Attached are the demos for each of the two talks I delivered:...(read more)

SQL Saturday 89 - Atlanta GA - Materials

Adam Machanic — Sat, 24 Sep 2011 16:57:00 GMT

Last Saturday, September 17, I was lucky to be able to present two sessions at an excellent SQL Saturday in the Atlanta area. The day drew a large crowd and had a great speaker lineup. All in all, a huge success, and a very well-managed event. Congratulations...(read more)

Performance, Discounts, and an Excuse to Visit New York City

Adam Machanic — Thu, 02 Jun 2011 14:21:00 GMT

A couple of weeks ago I announced a two-day advanced performance seminar in New York City, which will be delivered in July. This seminar will cover SQLCLR and parallelism techniques to help you take performance well beyond the levels that typical tuning...(read more)

Two Days of Advanced Performance Techniques - July 14-15, New York City

Adam Machanic — Wed, 18 May 2011 16:19:00 GMT

I am pleased to announce that I will be delivering two days of training in New York City, July 14 and 15. This seminar focuses on achieving "next-level" performance--going beyond that which you can gain via normal tuning methodologies . The vehicles for...(read more)

Recent Webcasts on Parallel Processing Available for Download

Adam Machanic — Thu, 07 Oct 2010 13:03:00 GMT

While preparing the material for my PASS Summit post-con on parallelism and parallel processing I realized that I could easily take a few chunks out, modify them a bit, and create some nice standalone webcasts. So I did just that, and delivered two sessions,...(read more)

Webcast - September 28, 2010 - Next-Level SQLCLR: Parallel Processing and Bulk Load

Adam Machanic — Mon, 27 Sep 2010 18:24:00 GMT

Tomorrow at 12:00 p.m. EDT (4:00 p.m. GMT) I'll be doing a webcast for the PASS Application Development Virtual Chapter . Next-Level SQLCLR: Parallel Processing and Bulk Load The power of SQLCLR as a performance tool has been well-documented at this point;...(read more)

RunAs Radio Goes Parallel!

Adam Machanic — Wed, 11 Aug 2010 14:42:00 GMT

Just a quick note to let you know that I did an interview with my friend Richard Campbell for his IT podcast, RunAs Radio . The topic is -- of course -- the common theme of my blog as of late: parallelism. Check it out!...(read more)

PASS Summit 2010 Post-Conference Seminar on Parallel Processing

Adam Machanic — Wed, 04 Aug 2010 16:37:00 GMT

I am honored to have been selected for a second year in a row to present a full-day seminar at the PASS Summit. This year's seminar will be a "post-con", and will be delivered on Friday, November 12 . The title of the seminar is " A Day of Doing Many...(read more)

PASS Performance Virtual Chapter: Parallelize Your Queries!

Adam Machanic — Mon, 02 Aug 2010 15:51:00 GMT

Last month a new PASS Virtual Chapter was introduced, one with a theme that is near and dear to much of what I like to work on: performance . Tomorrow, Tuesday August 3rd at noon Eastern time , the chapter will have its second meeting, and I will be doing...(read more)

SQL University: Parallelism Week - Part 3, Settings and Options

Adam Machanic — Fri, 28 May 2010 12:00:00 GMT

Congratulations! You've made it back for the the third and final installment of Parallelism Week here at SQL University . So far we've covered the fundamentals of multitasking vs. parallel processing and delved into how parallel query plans actually work...(read more)

SQL University: Parallelism Week - Part 2, Query Processing

Adam Machanic — Wed, 26 May 2010 12:00:00 GMT

Welcome back for the second part of Parallelism Week here at SQL University . Get your pencils ready, and make sure to raise your hand if you have a question. Last time we covered the necessary background material to help you understand how the SQL Server...(read more)

SQL University: Parallelism Week - Introduction

Adam Machanic — Mon, 24 May 2010 12:00:00 GMT

Welcome to Parallelism Week at SQL University . My name is Adam Machanic, and I'm your professor. Imagine having 8 brains, or 16, or 32. Imagine being able to break up complex thoughts and distribute them across your many brains, so that you could solve...(read more)

PASS 2008, Friday Keynote: Parallel Scale

Adam Machanic — Fri, 21 Nov 2008 18:36:00 GMT

Friday Morning. Alarm sounding around six hours earlier than I would prefer. A quick shower and I find myself literally running through the convention center. Settling down at the media tables, my laptop boots just in time for the opening strains of the official PASS theme song.

Guitars blaring.

Born. To. Be. Wild!

Screens filled with images of choppers manned by guys with too much facial hair. Screens, thankfully, cleared before too much frontal lobe damage is done.

Bill Graziano, PASS VP of Marketing, makes a picture-perfect PASS entry -- on a tricycle -- and gives us some important announcements:

The next PASS Summit will once again take place in sunny Seattle. November 3-6, 2009. (More Steppenwolf goodness? Can we expect the board members to emerge from the darkness of backstage riding a magic carpet? Only time will tell.)

After a brief interlude during which Bill shared with us the joys of serving on the board, he served up the election results. Congratulations to Douglas McDowell, Lynda Rab, and Andy Warren.

Next Ed Lehman and David Reed sauntered onstage to deliver the results of the SQL Heroes Contest. No huge surprises here; BIDS Helper, Extended Events Manager, ssisUnit, CDCHelper, and QPee Tools won. Congrats to all, and thanks for giving us some quality community samples!

Next we had a handful of words from Patrick Ortiz, Global SQL Server Solution Architect, Dell. How to address HA and business continuity within SSAS. I have to admit that I found this piece a bit tough to follow, so I won’t go into further detail.

Finally, the main event. David DeWitt, Technical Fellow for Microsoft at the Jim Gray Center in Madison, WI walks on stage and makes an immediate connection with the audience. A promise that there will be "no slick demos ... this will be a power lecture." (Apparently we've all had more than our fill of marketing this week.)

We're told that the topic of the talk will be key ideas behind parallel database systems. I found this to be a bit heady in my coffee-starved state, but what follows is what I was able to capture. Luckily, Mr. DeWitt is a skilled lecturer and I had no problem following along.

To begin with, a couple of key metrics that define how parallel scale should behave:

Linear Speedup: Add twice as much hardware, get twice as much performance
Linear Scaleup: Add twice as much hardware, and you can scale the database to twice as big while maintaining the same performance characteristics.

The end goal is, of course, to grow your hardware incrementally and scale appropriately. Project Madison, we are told, will enable the Microsoft database platform to do exactly that. So what’s the real challenge? How do we architect for a petabyte?

Some background on today's standard offering, Shared Memory. Spindles are all attached to one machine, and use the same memory. Mr. DeWitt commented that this scheme is "pretty simple ... all of the logs and all the data are accessible by all of the CPUs ... but it doesn’t scale very well."

Another technique that we've seen is Shared Disk. In this scheme commodity nodes attached to "very expensive" shared storage. This, according to Mr. DeWitt, ends up giving us limited scalability. It requires a "complicated distributed lock manager." The primary system that uses the architecture? None other than Oracle RAC. (Tell us how you really feel about the competition!)

Yet another technique: Shared Nothing. This one involves commodity servers, commodity disks, and commodity interconnect. Mr. DeWitt put it concisely: "A bunch of CPUs, a bunch of memory, and a bunch of storage". Simple enough, and apparently it scales "essentially indefinitely; limited only by your pocket." Well sure, but "the hard part is making this work."

A bit of history on the idea of shared nothing: DB2 and Informix experimented with the idea in the mid-'90s. MSN Live, Yahoo, and Google use similar architectures. And today "around 6" database vendors are working on this problem. Mr. DeWitt was quick to point out that "no, Google did not invent clusters." He proved this with an image from 1985, showing of 20 clustered VAXen.

So what are the pros of such a scheme? Commodity hardware; incremental, linear scale; fault tolerance. The primary con? Manageability. (Think anti-consolidation; huge farms of servers have interesting administrative challenges.)

Next Mr. DeWitt got in to how to actually accomplish all of this from an architectural perspective and showed us a few basic techniques. Horizontal partitioning involves distributing rows from every table evenly across all of the nodes and disks. (Think RAID 0 taken to the next level.) This can be slightly modified using a round-robin partitioning scheme, where rows assigned to disks in the order they are inserted, moving from disk to disk and spreading the data love. This ensures that every disk, on every node, ends up with the same number of tuples. (Note, for what it’s worth: Mr. DeWitt apparently prefers the "too-ple" pronunciation over "tuh-ple").

Another technique is range partitioning. This one is pretty straightforward; every node has a range of tuples assigned to it. The system knows where to go look for any given piece of data. That can be done based on IDs, or the system can be modified a bit to use hash partitioning. Instead of dividing things into ranges based on ID, use a hash function to do it. One problem with this: "Partition skew". If your hash function isn't very good, you’ll end up with some nodes with a lot more tuples than some other nodes. This leads to a less-than-ideal circumstance called "execution skew" in which one node is a lot slower than other nodes, so the other nodes need to wait for it in a parallel query. (Distributed CXPACKET waits? No thank you!)

The next interesting point was in regard to partitioned parallelism. It is necessary to use pipeline logic and avoid serializing intermediate results. (As a potentially-interesting aside, this is one of the basic ideas behind LINQ).

Mr. DeWitt went on to share various examples explaining how a parallel query can be processed using a hash partitioning scheme. Indexes partitioned along with the data, various replication schemes, and how to deal with hardware failure, skew, and other issues. I won’t go into detail here as it's rather complex to type in a blog post and you can find plenty of papers on these topics online.

Final takeaway message: "We intend to become the premier supplier of scalable warehouse technology." Sounds good; I’m looking forward to seeing how this all plays out.

And that's that. A slightly anticlimactic end but definitely a more interesting -- and intense -- talk than I expected walking in to the keynote. Hopefully PASS will do more like this in the future; it’s certainly much better than the standard marketing content we get subjected to at these kinds of events.