Monday, April 16, 2012

Too Many Indexes

When troubleshooting a SQL Server, I invariably look at the indexing on the database. As with most troubleshooting efforts, I try to start with a high-level view, and then drill-down to a detailed level.

One of the first things I look for are tables that have too many indexes. How many is too many? Well, it depends...

Index Counts

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

[sql]

-- Steven Ormrod
-- 6/4/2011
-- 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;
go

[/sql]

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

Get Cookin'

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.

No comments:

Post a Comment