Hidden Killers of SQL Server Performance

I’ve been working with SQL Server for a very long time. Out of the box, SQL Server works great, however there is regular care and feeding that needs to be done to ensure it is running optimally. One of the best analogies is to think about an automobile or airplane and how they need to have regular maintenance performed to keep functioning properly. There are things that we do with SQL Server from updating statistics, checking for corruption, index maintenance, and more. These things help keep SQL Server running smoothly, however there are other things that can creep up and cause issues that data professionals need to also be monitoring or checking for.

Indexes

The number one thing that I find that negatively impacts SQL Server performance is missing indexes, however that isn’t a hidden issue. Any respected data professional knows that you need proper indexes to assist your queries. What many data professionals are not monitoring and managing properly are the unused indexes. These are the ones that are just sitting around that provide little to no added benefit. On the contrary, they are expensive to maintain because every insert, update, or delete for that table requires updates to an index that queries are not taking advantage of. They also take up valuable storage.

I’ve found databases with a hundred or more indexes on a table, indexes that include every single column in the table, an index per column in the table, and countless other examples of index sprawl or mismanaged indexes.

Monitoring index usage is a great habit to form and disabling and ultimately removing unused indexes should become part of a DBAs skillset.

Compatibility Levels

This one can be tricky, so you need to test and not blindly change compatibility levels. That being said, changing the compatibility level is often missed when doing upgrades. The compatibility level for user databases is not automatically changed when you upgrade SQL Server or restore a database to a higher version. You must manually make the change.

I know of multiple applications that are in use today, even with brand new customers to where the compatibility level must still be at pre SQL 2012 levels, meaning 80 or 100. Knowing the potential negative impact, I won’t blindly change compatibility to the latest after an upgrade without the business doing some testing. I must admit, I am not as concerned when I’m going from anything post 2014 to newer, however regression issues do happen, so make sure proper testing is done.

Being able to use a higher compatibility level can unlock new features and improvements of the newer version.

Storage

SQL Server is a complicated technology, some refer to it as a ‘black box’, unless you are very familiar with it. Storage Area Networks (SANs) or storage in general is another ‘black box’. I put networking in that category too.

SQL Servers love to eat storage. Organizations acquire data and don’t want to let it go. The more data you have the higher probability that end users are scanning and pulling back more data they need due to indexing challenges. This puts more and more pressure on the storage layer.

It is very important that data professionals are tracking storage performance, not just storage capacity. I encourage DBAs to track disk latency, stats on reads/writes, and overall storage throughput. You need to have a good baseline of the capabilities of what your storage performance should be and what your averages are for your workload. With that information, you can then determine if your storage tier is under performing and report that to your infrastructure or storage team. You need metrics to provide to them to support your claim that there is a storage issue. Otherwise you are one of those annoying folks that just say, “storage is slow”.

Networking

This one isn’t so much SQL Server, but worth a mention. Similar to storage, networking is major component for a proper end user experience. SQL Server is serving up a large amount of data. Any degradation in network performance will likely be reported as “the database server is slow”. Being able to track metrics related to network traffic and overall network latency can be a huge benefit.