Monday, April 23, 2012

Zero Index Tables

When doing index analysis, another area I like to explore is how many tables have no indexes whatsoever. While there are legitimate reasons for having a table with no indexes, they are few and far between.

Here is a query to help you ferret out the zero index tables.

[sql]

-- Steven Ormrod
-- 6/4/2011
-- index counts

-- tables with 0 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) = 0
order by t.name;
go

[/sql]

We Don't Need No Stinking Indexes

Time for a Chat

Once you have identified your zero index tables, take the list to your development team for review. It may be that they are unaware of the situation. Or they may have a good reason for this setup. Either way, your application will thank you.

No comments:

Post a Comment