SQL Server Performance Tuning
SQL Server Performance Tuning is a specialty of ours. Most of the folks who work with Straight Path enjoy finding ways to make things run more efficiently in SQL Server. It’s a passion for many of us. There are so many ways to make things better, but our normal approach is to start with what we call “Layers of Tuning”.
All layers aren’t always needed, and we are always looking at the total project from the start and never duplicating effort or wasting time. The goal is to fix in phases, to see how we are – but even during Layer 0 we can often see signs that point to Layer 2 or 3 being needed. We plan accordingly, but we also don’t want to “throw out the baby with the bath water” and if we can get to the goals today and keep the future safe with only going to Layer 1 or 2, we’d prefer to do that instead of creating a very large “one size fits all” project.
Layer 0 – SQL Server Health Check
We often start with one of our SQL Server Health Checks. This allows us to see if any SQL Server settings, resource contention issues, a lack of best practice DBA maintenance, etc are hurting an environment. We nearly always find a few more things here to see some immediate improvements to performance. Sometimes the boost is smaller, but enough to buy time to go to the next layers. Often we stumble across some best practice misses that could pose a reliability concern later. When we’re done with this phase, we all know where the hardware, SQL Server instance settings, etc lie. This proves invaluable as we continue our SQL Server performance tuning work.
Layer 1 – Indexes and Simple SQL Changes
Here we are looking at your code and your database. We are looking to identify and categorize your top queries. We start looking at your indexes and code. The main goal here is: changes we can make with minimal testing required and minimal impact to your development team and users. Often this is index changes, simple straightforward changes to Stored Procedures that are just different approaches to the same methods used. We almost always find serious room for improvement here. With many clients we can even stop here, with others, we have to keep going, but this phase buys even more time and user experience improvements while we go deeper.
Layer 2 – More Code, Some Tables
In this layer, we are starting to go much deeper with SQL Server code changes. We’re looking at your code more, looking at your database design more and formulating a plan for the next steps. We try to avoid table changes if we can here because that has the higher testing burden but we will if we need to.
Layer 3 – The Bigger Changes
Here we are finally helping with some of the foundation design decisions that could be causing issues. We are looking at larger changes, maybe taking sections of an app or database apart a bit and redesigning key problem areas.