People want to make things faster. It’s in our nature as IT professionals. What are you tuning for? I’ve been asking that a lot later. I have to sit back and ask some very basic questions to try to see where people’s IT-related pain is at.
What is your end goal? In my world, I tune SQL Servers on any infrastructure out there – on-premises and in the cloud. I tune for many different factors, all of which are specific to that organization’s needs. I always want to know what we’re tuning for. It makes your efforts more effective, and gives you means to define success criteria.
Are you tuning to improve your SQL Server licensing footprint? You can tune for CPU reduction in repetitive queries. You can index and statistics tune to make certain queries faster and be more efficient, which in turn reduces the CPU, memory, and storage thrash while the commands are executing. You might even be able to use less of the things that SQL Server licensing is based on, namely CPUs (and a distant second is the memory limitations per instance of Standard edition).
Are you tuning for end-user productivity? Can you quantify the pain points the users are ‘feeling’ each and every day? Can you pinpoint the database commands that are underneath those application features? Are they database-driven, or is it more application data handling that is slowing down the function? Maybe the volume of data that the business accesses daily is so high that all-flash storage is the biggest gain you can make. What if faster CPUs, and not just more cores, would get your users a larger bang for the buck? Are your repetitive queries optimal? Can you even access the commands to tune them, such as queries underneath third-party applications? What if tuning for end-user productivity meant increasing parallelism or adding to your licensing footprint? Is this an acceptable tradeoff to the business?
Are you tuning to make certain nightly processes or reports improve? Gauging results is easy if you can define the end goal. Do you care about resource consumption? Are you tuning them to make them faster, or just fit within a certain maintenance window of time? Are you trying to do more in the same period of time, or have lots of concurrent processes that run simultaneously?
Are you tuning for space consumption? Data types matter. Poor primary key design can bloat your index space. Over-indexing can magnify it. Repetitive copies of data can make it even worse. Auditing and logging tables can eat disk space for data that no one cares about after some sliding window of time.
Are you tuning for concurrency? RCSI can go a long way here. TempDB contention, inefficient code, poorly designed database tables, etc., can all make or break concurrency on highly accessed databases.
Are you tuning for peak demand, such as a busy ‘season’, or for month or quarter-end processing by the business? Can you simulate this worth of traffic, either with custom tools or things like SQL Server Distributed Replay, so you can validate that today’s system changes don’t wreck your day the next time the system usage ramps up?
What about upwards scalability? What if your organization is going to acquire another and double your user load in just two months? How will the database usage patterns change? What bottlenecks, which may or may not be a major concern today, could rear their ugly heads and interrupt your acquisition?
Don’t tune just to tune. You might just be wasting your time. Tune for a reason to solve a problem or achieve a longer-term goal. Be objective. Measure your results. Rinse and repeat.