June 22, 2020 at 8:47 pm
Hi,
A table which has 200+ millions records has 10 non clustered indexes and 1 clustered PK index. Now, I feel these are too many IDX for one single table ( Somewhere I heard there should not be more than 6 IDX for a table for performance reasons), is there a way to find out what IDX are not being used for this table which can be removed? secondly, is it possible to merge indexes, like using INCLUDE COLUMN and add the separate IDX column to other index to reduce the IDX count for that table?
Thanks!
June 22, 2020 at 9:40 pm
As far as I know, there is no magic number to say more than x indexes will impact performance. Having 1 index may impact performance, 2 might, or 100 might not. Well, it is unlikely that 100 won't but it really depends. Each index increases the number of times an INSERT, UPDATE, or DELETE needs to run as each index (unless they are filtered indexes) is a copy of part of the table ordered in a different (or possibly the same) way.
That being said, too many unused indexes (or duplicate indexes) can hurt SELECT performance as well.
To find all Unused indexes - this query will do it:
SELECT
objects.name AS Table_name,
indexes.name AS Index_name,
dm_db_index_usage_stats.user_seeks,
dm_db_index_usage_stats.user_scans,
dm_db_index_usage_stats.user_updates
FROM
sys.dm_db_index_usage_stats
INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID
WHERE
AND
dm_db_index_usage_stats.user_lookups = 0
AND
dm_db_index_usage_stats.user_seeks = 0
AND
dm_db_index_usage_stats.user_scans = 0
ORDER BY
dm_db_index_usage_stats.user_updates DESC
Query taken from - https://www.sqlshack.com/how-to-identify-and-monitor-unused-indexes-in-sql-server/
One thing to note though - if the index is used in a very specific query that is infrequently used, you may end up causing drastic performance problems with a single infrequently used query (such as month end, quarter end, year end queries, or archiving queries that trim out old data).
Before changing indexes, I like to find all of the objects that use the table and build up a script to test all of them. Next, I do index maintenance, and re-run the script. Based on that, I decide which indexes to keep and which to remove. This may be unrealistic in your scenario, but my test systems are copies of live so it is not impossible to do this level of testing. Can be a pain in the butt, but it is possible.
As for merging indexes, it depends. INCLUDE COLUMNS likely are not what you are looking for as they are used so you don't need to go back to the clustered index to get the data you need without needing to go back to the clustered index. Having the index on multiple columns may work for merging your indexes or may not. It really depends on your queries.
My advice with indexes is to have as few as makes sense. If the table feels like it is slow to pull data out, put data in, or change data, then indexes may be the way to go. Or you may have too many and removing them may help. There isn't a "one size fits all" scenario with this. Best option is to test it and see what happens. Indexes can help with a single query (that is easy to test and prove), but may hurt others. Removing indexes may help some queries and may hurt others.
I would check for long running queries first and work with end users to see if they are having problems getting data from that table. If nobody is complaining about the performance of that table, there may be something slow that you will get more bang for your buck and more impact to end users.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 23, 2020 at 1:16 pm
Just to reinforce what @mr.briangale has already said, there is no magic number of indexes. If the indexes are being used and their maintenance is not causing you pain, whatever that number is, 1, 10, 20, that's the right number of indexes for the table. Period.
Now, I do subscribe to the idea of trying to have the minimum number of indexes, as close to one as practical. Why one? Because, with a few exceptions, and exceptions are exceptional, you should always have a clustered index.
In addition to tracking index usage as outlined above, you also want to look to see if you have duplicates. These are determined by the key values. So, for example, an index with ID & Value1 as the keys may be a duplicate with an index that has ID, Value1 and Value2. Depending on the INCLUDE columns, you could eliminate the first index since it's a duplicate of the second.
However, if an index is being used, it's needed. And if that means you have two or more indexes on the table, oh well.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply