Silent Performance Killers in SQL Server

The Top 3!

I get a lot of calls from IT leaders complaining about slowly degrading performance of their SQL Servers. It’s the most common call I get.

Sometimes it’s easy.? One missing index stands about above all the rest. Usually, it’s a combination of things.

Here are 3 things you may not even be aware of that can slow your SQL Server down:

Unused Indexes

Indexes that are not being used by the query optimizer when plans are generated are considered “unused”. All indexes on columns that are in your CRUD operations (CReate, Update, Delete) have to be updated for each operation. Index pages take up space on disk, and in memory when being updated. They may also be taking up resources when being defragmented or when stats on them are being updated.

I had a client with 132 indexes on one table recently. 6 were being used. We disabled the others and EVERYTHING on the server went faster, particularly tempdb performance and utilization.

Storage Controllers

This is not my area of expertise per se, but we see a lot of VMWare VMs that only have one virtual controller. Up to 4 are allowed. There are best practices articles available for most of the various virtualization layer offerings. It’s a great idea to give your SQL Server multiple pathways to the disks if you can.

Database Compatibility Level

I see databases every day that are set to Compatibility levels as far back as SQL 2008 (level 100). In some cases, this is completely necessary, especially if you have a ton of legacy code or an old 3rd party app with no ability to update.

One of my clients recently hit me up asking for help with a simple ‘Select Into’ statement that took 9 minutes for 30K rows. It was a cross-database query, but within the same SQL instance. We dug into the execution plans and found a reference to compatibility levels and found that the source database was compatibility level 100 instead of 130 (SQL 2016). That change alone dropped execution time to 3 seconds. This is great for the target query, as well as all the other queries that needed the resources that were being hogged.

Wrapping up

This is most definitely NOT an exhaustive list of things that can slow SQL Server performance to a crawl, but they are some things that Accidental DBAs and even some Intentional DBAs may not think of at first.

What would you add?

(Code sections for items 1 and 3 are in the full newsletter )

My Recent LinkedIn Posts:

Leaders Making Tech Decisions

SQL Server Performance Tuning in 5 Years

Are Your Indexes Outdated?

SQL tidBITs:

When you need to make changes to the SQL Server Services, such as the account/password starting and stopping them, use the SQL Server Configuration Manager. Don’t use Control Panel - Services. Config Manager will make necessary permissions changes for you.