Joey on SQL Server

How To Be a Better DBA: 4 Strategies To Improve SQL Server Performance

There are plenty of ways to get the most out of SQL Server without just upgrading to the latest version. For database admins working with older (maybe much older) SQL Server systems, here are four ways to make life easier.

• By Joey D'Antoni
• 02/10/2020

While I enjoy writing and talking about the future and the bleeding edge of technology as it relates to databases and the cloud, I realize many organizations are using much older hardware and software.

So in this month's column, I wanted to focus optimistically on spring (and spring cleaning) and talk about four ways to get the most out of your existing SQL Server deployments and applications.

1. Patch Your Servers
I write about security a lot and sometimes it feels like I'm yelling into to void. I get it -- most non-technology organizations (and frankly, many technology organizations) are not wont to spend money on enhancing their security stance. Security is not viewed as a value-add and building a secure environment inherently makes life more difficult for business users, who do make the company money.

However, there are some basic things you can do. The most basic is applying Windows updates and SQL Server cumulative updates (CUs) -- and service packs if you are on a version older than SQL Server 2017 -- to your database servers. This isn't just for security, either; Microsoft builds a lot of performance and programmability features into CUs nowadays, and sometimes it'll even backport features from newer versions of SQL Server (for an example, see this Microsoft SQL Server team blog).

2. Review Your Indexes
As a consultant, I see a wide variety of database servers in all sorts of conditions. There are a couple of universal truths. One of them is that on 95 percent or more of the SQL Server instances I've looked at, the ratio of reads to writes is somewhere between 3.5 to 4 reads for every single write. Depending on the system, it can be much more dramatic than that.

What this means is you will likely see a much bigger benefit from carefully adding indexes to your tables than a negative impact for write activities. (When you write into a table with indexes, each write has to happen in each index in addition to the base table or clustered index.) Now, this doesn't mean you should go crazy and add every index ever that SQL Server recommends, but it does mean that you should review your frequently-run queries and gain an understanding of which indexes would benefit you.

If you're reading this, I'm assuming that 80 percent of your databases are purchased from third-party software vendors, who may or may not let you make changes to their database objects. In my corporate career, I was fairly successful making these sorts of changes; your users work in your company, and you don't work for the vendor -- it works for you. Just make sure to get clearance with your boss, test in lower environments and, finally, use a naming standard for your indexes that lets you quickly identify them. (When the vendor upgrades its app software, the indexes you created may not be relevant.)

3. Execution Plans and the Query Store
If you are on SQL Server 2016 or higher and you aren't using the query store feature, you are missing out on the single best database performance diagnostic tool I've used in 21 years of working with databases.

The query store captures your running queries, their estimated execution plans and the runtime statistics associated with each run of the query. This tool lets you quickly isolate your most expensive queries and observe changes in execution plans that may negatively impact your query performance. The query store does come with a small amount of performance impact (most estimates place it at 1 to 2 percent), but the benefits far outweigh its cost. The only systems where I've turned it off are those that are 100 percent dynamic SQL (and not the nice parameterized kind), where each query string is unique to the database engine.

While we're here, if you can't confidently read an execution plan, you should brush up on that skill (here's a free e-book on it, written by an expert). It's the most critical part of performance-tuning SQL Server. If you are on a version of SQL Server older than 2016, you may want to look for a third-party performance tool to help you get this information. Much of the same information that the query store captures is available in SQL Server's plan cache. However, it's tricky to retrieve, and tabulating things like execution and runtime performance history is challenging to do yourself.

4. Build a Baseline
This ties into the query store and possibly into performance-management tools, but one of the more important tools you can have as a DBA is a performance baseline. This could be a column onto itself, so I won't get overly complicated here.

You can use Windows Performance Monitor (perfmon) to build a simple counter profile:

CPU Percentage
I/O Sec/Read
I/O Sec/Write
I/O Writes/Sec
I/O Reads/Sec
SQL Server Page Life Expectancy

That's not complete, and it's also not all of the data that exists in the ecosystem. However, it lets me quickly analyze and narrow my focus to a very specific area of performance difficulty. You might notice I'm not capturing any OS-related memory counters. For the most part, they are useless on a database server, as a database server will always consume nearly all of its available RAM.

Page Life Expectancy is a metric that shows you how quickly SQL Server is churning through data pages in its buffer pool, and can be indicative of poorly performing queries, an under-resourced server or both. There are many third-party tools you can use for this, or you can simply deploy perfmon templates to your SQL Servers. 

Summary
Trying to distill everything you can do as a DBA into a single column is impossible. However, these are some tips that will let you have more consistent performance in your environment and have happier users.

And as a final tip: Don't ever forget to do backups (and test restoring those backups) and database consistency checks on a regular basis so your users have data to hit.