One of the first things I look for are tables that have too many indexes. How many is too many? Well, it depends...
As a general rule, the fewer indexes the better. Typically, I screen for any tables that have more than five indexes. Now, this doesn't mean that these are bad. This is just a way for me to quickly find which tables I should spend some more time on analyzing their indexes.
By querying a couple of DMVs, sys.tables and sys.indexes, we can do a quick check of our index counts.
Number Five is Alive
-- Steven Ormrod
-- index counts
-- tables with N or more indexes
select t.name as 'Table Name', COUNT(i.name) as 'Index Count'
from sys.tables t
join sys.indexes i
on t.object_id = i.object_id
group by t.name
having COUNT(i.name) > 5
order by 'Index Count' desc;
Here is an example of a database that appears to have several tables with way too many indexes. These would be the first tables I look at when reviewing the indexing strategy of the database.
|Way Too Many Indexes|
Here is another example of a database with only a couple of tables with six indexes. I probably wouldn't worry about these.
|Indexes on the Borderline|
Indexing is a critical, but often overlooked, aspect of performance tuning. One of the first steps is identifying how many indexes you have. Once you find some fish to fry, get cooking.