Denny's SQL Secrets

Are Too Many SQL Server Indexes Hurting Your Performance?

The question leads to an even more important question: do your users care?

• By Denny Cherry
• 07/16/2014

There's two answers to this question, the short answer and the long answer (which includes a follow-up question).

The short answer is yes

For the longer answer, let's start with the follow-up question: Do my end users care that it's slowing my SQL Server down?

Before we can answer the follow-up question of do my users care, we need to first understand why having too many indexes is a bad thing. The reason that having to many indexes is a bad thing is that it dramatically increases the amount of writing that needs to be done to the table. This happens in a couple of different places. When a write happens the data first is logged to the transaction log. When this happens it is logged for every single index that is being written to. So for a table with nine non-clustered indexes on it there are 10 writes to the transaction log. In addition to that, write changes have to then be made to all 10 data pages (one data page per index) so that the data can be written to the data file as well. All those writes have to happen before the user transaction can be completed. The more nonclustered indexes that you have, the more writing to the disk needs to happen.

Now for the follow-up question, do my end users care that it's slowing my SQL Server down?  The answer here is probably not, unless there's a really high write workload on the server (your users probably couldn't care less if the system is a few milliseconds slower than expected). Now that being said, there are applications where milliseconds matter such as stock trading database applications where a few milliseconds of latency could mean that a trade is delayed (which could mean that money was lost). For most applications, a couple of extra milliseconds aren't going to really matter.

And frankly as long as the end users aren't complaining and they are having a good user experience then what does it matter if we are losing a few milliseconds when inserting or updating rows in the table? It really is going to come down to how many nonclustered indexes you have on the table, how many transactions per second are writing to the table and how the SQL Server's storage is configured. If you hit the perfect storm of slower storage, lots of unneeded indexes and lots of users, then there is going to end up being blocking due to transactions having locks on the table. And these can start stacking up, leading to users waiting into the seconds range (which starts causing users to have a poor user experience).

The best thing to do is to review the index usage information from the sys.dm_db_index_usage_stats dynamic management view and find out which indexes haven't been used for seeds, scans or lookups but are being maintained. Consider disabling those indexes to see if that improves performance. Do keep in mind that the sys.dm_db_index_usage_stats dynamic management view is reset to all zeros every time the SQL Server instance is restarted, so don't restart SQL Server then disable everything which hasn't been used. You'll want to have SQL Server running for at least a month before deciding what indexes to disable. I always recommended disabling first so that if you need the index back you can quickly rebuild the index (making it available again without needing to go and find the SQL Code to create the inde)x. Disabled indexes can simply be deleted after you are sure that they aren't needed.