June 10, 2017 at 4:53 pm
Are there any negative consequences of adding too many indexes, such as in performance. What is considered too many?
June 10, 2017 at 6:21 pm
Yes there are absolutely negative consequences to having too many indexes and/or the wrong indexes... As they say, there is no free lunch.
You can think of an index as a ordered, physical copy of your data. In fact, the clustered index IS the table.
So, while a nice covering index will speed up a given select query, that index must be maintained, Every time you insert or update data, not only is the table (clustered index) inserted into or updated, the same has to be done to every other nonclustered index. Too many indexes can really bog down these operations.
The other big consideration is the space they take up on disk, the increased size of backups and the increased time it takes to create backups.
In short, very careful consideration should be given when creating new indexes... and I don't think I'm alone in saying that you should NEVER rely on the DTA to create indexes.
As far as what constitutes "too many", that's hard to say, it really is dependent on circumstances...
I think the more important question is, is a given index hurting more than it's helping. If you have an index that very low seeks or scans but lots of writes, you may want to consider dumping it as the cost of maintaining it is greater than it's benefit.
Another thing to look for are multiple, redundant, indexes. If you have multiple indexes that have the same key columns (all in the same order) but different included columns, consider consolidating them into a single index.
June 11, 2017 at 12:38 pm
Like Jason said, there is such a thing as over-indexing and it does come with consequences. What's too many? It depends. 😉
Start out by defining what a NCI really is - it's duplicated data. Granted, it's structured in such a way (the B-Tree) to be searchable in an efficient manner, but it's still duplicated data and therefore requires disk space, statistics maintenance, backup space, and so on. Whenever you fire an update of your base table, each NCI has to be updated. So, if you have 50 NCIs on a table, then you have 51 update points - 1 for the base table (the CI) and 50 NCIs. All these updates take time.
Jason is definitely not alone in saying to not blindly follow the DTA. I've seen recommendations from it and following it would lead to a tremendously over-indexed table. You really have to think about what NCIs you need and design them intelligently. You can start with a list from the DTA or the DMVs (https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-details-transact-sql) but really look at them and consider them carefully. What you need stems from your workload.
When looking at if your existing indexes are being used, I'd have a look at sys.dm_db_index_usage_stats (https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply