Parameters and Presses

 

Parameters & Presses

I was in the gym on Tuesday and had this idea whilst executing some shoulder presses. About how picking a weight is similar to a common issue in stored procedures: Parameter Sniffing.

Before I can make the comparison, let's define Parameter Sniffing. Oftentime, stored procedures in SQL are driven by a parameter being passed to define the criteria for a query. These can be incredibly simple to ultra complex. For the purposes of my example today, I have an extremely simple procedure. See below.

I've got a procedure called ParamSniff which accepts one parameter called Location. Then I have a very simple SELECT query that uses that parameter in the where clause. When you execute the procedure it passes the required parameter value through and searches for that.

Now, if this is the very first time the procedure has been run or the server and/or SQL services have been restarted recently, there is no cached execution plan for this procedure. So the SQL Engine will build an execution plan that effectively uses our indexes and resources to find the requested data based on the value that was passed. 

For this demonstration, I am using the StackOverflow database, created a simple index on the Location field, cleared the plan cache and run the following statement: 

EXEC ParmaSniff @Location='Near Stonehenge'

Since no cached plan exists, the engine will build one based on that value of "Near Stonehenge". It checked the index heuristics and built the following execution plan:

This returned a single row in less than a second.

Not too shabby. Reading our plan from right to left, top to bottom, we find that it estimated 9 rows would be returned and found 1. It used my index to get that info. So that's pretty efficient! But that's a small data set. What happens now when we pass in a much larger value? Say India for example.

This returned 49,358 rows in about a second

SQL Estimated it would fine 9 rows and came back with 49,358! That's a pretty hefty margin. So what happened? The engine had a cached plan based on what came in the first time. It holds on to the parameter value because it believes each time it's run, it'll be close to that. But in our case, it's vastly different. So the engine used the cached plan to execute for a value of "India". Which is inefficient and could lead to long read times, not enough memory granted, disk spills and more. Will it work? Yeah, but it could take longer and not make proper use of resources.

How does this relate to my shoulder presses?

For the purposes of this illustration, my body (muscles, joints, mind, etc.) is the execution plan. I've come into the gym today to work shoulders and I'm about to execute the procedure Shoulder Press. I select a small weight, say 15 lbs. My body builds the execution plan based on the small weight.

Starting with the weights at shoulder level, elbows below the chest, extend the arms straight overhead bringing the weights together and then bring them back down. With 15lb weights, it's easy. I'm returning results in moments. My body (execution plan) is built for the small weight and knows what to expect.

I've done a couple of sets of those and I know exactly what to do. Out of nowhere, I want to see results with 50lb weights! My execution plan isn't ready for that, I've only been pressing 15's. Now all of the sudden it's being asked to put up 50's! Can I do it? Yeah, but I'm expending more energy and not utilizing the proper plan I'd established.

Let's turn it around

What happens then if we turn the parameters around? Use the larger data first. The engine builds things a little differently.
I clear the plan cache, so the SQL engine doesn't know the best plan for my procedure yet. I execute again, this time passing "India" first. Let's take a look.

This time it didn't use the index at all. Went straight to the clustered primary key index and fully estimated the results correctly. It also ran very quickly.  So what happens if we pass in "Near Stonehenge" now? Remember, it's got an execution plan cached based on the value of "India"

Because of the cached plan, where it thought 49,358 rows would be returned, it thought the results would be similar. But we know that "Near Stonehenge" only produces 1 row. This is an ineffective plan as it's not using our index, granted too much memory and oh yeah, went parallel. For a singular row! Wasted resources.

In the same way, if I start out lifting 50lb weights, I get my body accustomed to that, and all of the sudden switch it up to 15's, that's just wasted effort for a simpler weight.