Parallelism in Databases and the Gym

 Parallelism - Databases and Isometric Movements

The other day I was doing single leg hamstring curls and came up with the analogy of comparing this movement with parallelism in a database. So let's cover a couple of things up front.

For the purposes of this topic, consider my legs. They are representing my processors (CPU or core) inside a computer today. In this machine, we start with legs fully extended and then using the hamstring muscle, flex and pull the bar toward you lifting the weight stack. Obviously, utilizing both legs we can lift way more weight than with a single leg. 

Similarly, the more processors you have in a system, the more work it can accomplish. In terms of databases, we call this Parallelism. If a query is big (heavy) enough, the engine will spread the workload across multiple processors, divvying up the work. Within SQL Server, you can configure a couple of components around Parallelism.  

First you have to determine how many processors you have available, then decide how many you want to allocate to your queries when they go parallel and what the cost threshold for that will be. I'm going to be using some screenshots from my laptop, not nearly as powerful as a full blown server, but you'll get the picture.

In this image, I am examining my server properties. I've highlighted that my laptop has 16 processors (or cores) available to handle tasks.  

In the next image below, you can see I've allocated 4 processors to handle SQL related work. Why only 4? Why not at least half or even all 16? You want to leave some hardware available for your other system tasks. Your operating system will need some, anti virus, other applications, etc. In my example, this setup was being used to test various settings and methods recently and I was throttling the processor count. So in my case, 4 is a touch low. Typical recommendations is half of your available cores. However you may need to do some testing and find the best setting for your environment.

The other setting highlighted here is the true subject of today's lesson. Cost Threshold for Parallelism.  Behind the scenes, when you run a query, SQL estimates a cost for running said query. If that value exceeds the configured value, the engine will spread the workload across the specified number of cores. Up until recently, the configured value out of the box has been 5. That's ridiculously low and even Microsoft recommends changing it to something higher.  Newer versions of SQL have it set to 50 upon install.

So I wrote up a simple query to demonstrate this. I am using the StackOverflow database. The same one used for many a demo I've seen and is available freely online.

Nothing crazy. The next images show the query cost and a snippet of the execution plan. Parallelism in the execution plan is denoted by the yellow circle icon with a couple of arrows. Affectionately referred to as Racing Stripes. If you see these, your query went parallel.

Here we see the plan cost was just over 43 "query bucks". Well above the 5 value specified in the settings. So the engine took my query, divvyed up the work among my 4 processors, each one did something and then notice the circled operator below. Parallelism (Gather Streams). This step takes all of the work from each processor, re-stitches it together and provides the result.

Now here's a real kicker. It's not distributing the work evenly or even across all of the processors allocated. In fact, in some cases the work is probably so simple, that it never even left the first processor! However, since we have that value set at the server level, it did exactly what we told it to.

I'm going to modify my threshold to 50 and watch what happens.

The plan cost is the same, 43.08. However now, with the threshold set to 50, we didn't go parallel. The query is still simple enough that a single thread could handle the workload and still deliver results in less than a second!

Ok, so how does this relate to my hamstring curl machine? I said already, my legs are the processors in this scenario. I have two available. The weight stack is our query cost. Let's say I set the weight stack to 20lbs. Can I use both legs to complete that work? Of course! But it's kind of overkill. I'm doing it so fast that it's not really beneficial for me. So I can go single leg (or single threaded), still complete the lift and get more efficient results with my processors.

Bump the weight stack up to 50lbs. I can do that with one leg, but I might struggle to complete the work, it's going to take me a little bit longer. Throw in parallelism (my other leg) and now I'm completing 50lbs much easier.

Find the settings that work for you, both in the gym and in your databases. Get a baseline of your query costs and see where to set that threshold for queries going parallel.