Deadlifts and Execution Plans

How in the world could I possibly relate these two things? At first blush they appear to have nothing in common with each other. But let us look a little deeper.

This article is less about performance tuning today and more focused on the gym movement. However I'd like to get you thinking about how you setup for success in both.

The Deadlift

This is one of the core movements in the gym and also one of the more complex to setup for. There's a couple of ways to begin setting up for a deadlift; conventional and sumo. In a conventional style, the lifter sets up feet about shoulder width apart and your arms will come down on the outside of your knees. You don't want to go too wide here, the elbows should brush the knee. Squat down low and roll the bar to your shins. Pull your shoulders up and back, thing about squeezing the lats applying just enough tension to the bar. Keeping your backside low begin the lift by simply standing up. Don't let those hips come up before the bar does. That opens the door for a back injury. Stand completely straight upright, then lower the bar back to its original position attempting to follow the same path working towards that low hip position at the bottom.

A sumo deadlift will be similar, but in this case, your feet go wide and the toes point at a slight angle. The arms come inside the knees this time about shoulder width apart. You'll still get into that squatted position, hips low, pull those shoulders up, engage lats and then stand. Some folks find sumo to be easier when lifting heavy weight.

This is a move where form can degrade quickly. So personally, I like to stick to sets of 6-8. As soon as I feel form start to diminish, that's when injury can creep in and I stop. Find a weight that you can work in this range for 4-6 sets comfortably before you consider moving up in weight.

The Execution Plan

Now let's talk about the Execution Plan in SQL Server. Much like our deadlift movement, we need proper setup in order to carry out the task without hurting our server. You'll need things such as proper indexing, correct memory and processor configurations and of course a well written query in order to carry out the task. Can you still run a process without those things? Certainly. Will your server (and your body) not thank you for your proper preparation and not simply rushing into it? Lest you end up with an injured back.

Let's talk memory and processor configurations first. When it comes to memory, SQL Server will gobble up as much as you give it and not let it go until the server or service is restarted. In the server configuration panel, you can specify how much memory gets allocated to SQL. A good rule of thumb is to set this value to where you have enough memory leftover to run the OS functions, any monitoring software you might have or any other minor applications or services so as to not interrupt operations. In my example laptop, I have 16 GB of RAM and I've allowed SQL to take up to 13 GB of that. 

Much like memory, your server has a finite amount of processor power. Enter Max Degree of Parallelism (or MAXDOP). I touched on this in another blog post recently. This is how many processors you allow SQL to take advantage of. The default is 0 meaning it could take all of the processors for a query. I typically setup boxes usually to be half of the processors. Again, that leaves room for your OS and other services to still function without bringing the server down.

There's no hard and fast rule for this, it's whatever makes sense for your organization. In my laptop, I have 16 cores, but only allowing 4 to SQL. That's because I run more apps (Chrome, Word, Discord, etc) regularly and they all want processing power too! In a perfect world, a SQL server only contains SQL. However we all know that's not true.

Another component to garnering a successful execution plan is the Compatibility Level. This is a database level setting where you can actually backwards date your SQL database. As new versions release, new features and functions become available and that has an impact on how SQL builds your execution plan.  I have SQL Server 2022 installed, but if I back date my database to say 2017, it could drastically change how the estimator builds the plan. 

The next topic I'll cover here is indexing. I am extremely passionate about this subject and could drone on for hours. I'll spare you that misery and simply state that indexing is possibly one of the most important features when it comes to running successful queries and getting a good execution plan. Think of an index just as you would in a book. You go to the back and want to find all of the references to pasta. The index tells you it's on page 15, 83, 96 and 102. So you can directly to the pages you need. Otherwise, you'd have to read page after page after page until you found the reference you were seeking.

Our databases perform the same way. When you write a query looking for records where lastname='Jones', if there's no index on your table for the lastname field, the engine has to read through the entire table row by row until it finds the records in question. Painful. But with an index, the engine can dive directly to the records it needs, it knows exactly where to find them. 

The last topic I'll mention here is good query writing. This is an amorphous subject because what's good to one person, might be garbage to another. But there are things you can avoid that will make them perform better. A few spring to mind:

• Sub Select statements inside a where clause
• scalar functions inside where clause
• Excessive joins
• Querying views that call views that call views (I've worked on one that's nested 4 deep and across linked servers)
• Joins to linked servers

That's just a handful I see and deal with on a regular basis. I'm certain there's more and way worse out there. The point being, take the time to analyze your queries and procedures and see if a more efficient way exists.

The Conclusion

Much like when it comes to a deadlift, you need to be conscious of your form, posture, think about what muscles you're using, setting up correctly a good execution plan for your queries requires some preparation. Now it is true that you cannot control precisely how the execution plan gets built, the SQL Engine does that for you. You don't get to dictate the shape, but you can help control the circumstances around how it gets built. Utilize the tools at your disposal to help guide SQL towards the best possible outcome. Then your queries, much like your deadlifts, will get stronger and help you lift waits and weights.