SQL Server World

Recently, I had an opportunity to attend Adam Mechanic’s “Parallel Execution Performance” class. He offered this class as a Pre-Con to SQLSaturday #291. I do not need to mention he is super intelligent guy who wrote universal stored procedure called “sp_whoisactive” and is used by most of the DBAs all over the world. But he is also a very good teacher and presenter. Here I will try to explain some of the nuggets of wisdom I collected from the class.

He mentions world in general and technology in particular has changed. So today’s DBA also need to change how they think about performance and bottlenecks. What was true 10 years ago maybe not true at all today or partially true. Adam said in Query Execution Plan there are 2 types of zones; parallel zones and serial zones. It is the serial zones where opportunity of performance tuning lies.

Although I was familiar about the Moore’s Law, but there were other laws that apparently I was not aware of. One is Amdhal’s law and other is Guftan’s Law. He gave an example to understand Amdahl’s law. Consider a restaurant Chef preparing a dish containing 3 items. Each item takes 10 minutes to prepare. And final plating takes 10 minutes. Altogether it will take 40 minutes to prepare one plate. She hires 3 cooks and now each cook prepares the single item and gives it to chef for plating. So instead of 40 minutes, dish is ready in 20 minutes. This is 50% increase in performance. This is not a linear relation ship and it is explained by the following formula.

improvement = 1/(1-P) +p/N

= 1/(1-.75) + .75/3 = 2 ( 50% increase than serial task. There were 3 cooks who can work in parallel, plating time was serial)

Let us now add one more cook. P = .75, N = 4

improvement = 1/(1-.75) + .75/4  = 2.385 (42% increase than serial task. There were 4 cooks but 3 tasks. Plating time was still serial)

Let’s add one more task. P = 0.8, N= 4

improvement = 1/ (1-.8) + .8/4) = 2.5 (60% increase than serial task. Serially it would have taken 50 minutes for chef to finish the dish with 4 tasks plus plating.)

I hope you get the idea. To me this is very interesting to note. I will try to find other areas where I can use this logic. Back to the earlier example, we see that if no. of tasks equals to no. of cooks, parallelism gives optimal result but the serial part of plating the dish is constant. If we improve that part of the process, it will give more performance improvement.