Monday, December 10, 2012

Where are your indexes located?

When taking over a database or looking at it for the first time, one area I like to focus on is the indexes. There are the standard things, such as index fragmentation, usage, etc. However, one area that is often overlooked is where the indexes are.

But aren't they in the database?

Yes, but where in the database. If your database only has one filegroup, PRIMARY, then that is where your indexes are. However, some databases have multiple filegroups. They could be for archiving purposes, security segregation, performance, or any number of reasons.

A fairly common setup to encounter is to have a separate filegroup for non-clustered indexes. This is not as common a technique as it used to be, and there is some debate as to whether this helps performance. The point here is not to debate the wisdom of index filegroups, but rather to get a sense of how your database is laid out.

The following script will give you a high-level breakdown of your database and show you which filegroup your clustered indexes, non-clustered indexes, and heaps are living in.

[sql]

-- index location aggregations
select ds.name as 'FG Name',
i.type_desc as 'Index Type',
count(*) as 'Index Count'
from sys.objects o
join sys.indexes i
on o.object_id = i.object_id
join sys.data_spaces ds
on i.data_space_id = ds.data_space_id
where o.type_desc = 'USER_TABLE'
-- and i.type_desc = 'CLUSTERED'
-- and i.type_desc = 'NONCLUSTERED'
-- and i.type_desc = 'HEAP'
-- and ds.name = 'PRIMARY'
-- and ds.name = 'MyDataFG'
-- and ds.name = 'MyIndexFG'
group by ds.name, i.type_desc
order by 'FG Name', 'Index Type'
go

[/sql]

Index Filegroup Aggregations

What catches my eye in this example, aside from all the heaps, are any clustered indexes that are not in the data filegroup, and any non-clustered indexes that are not in the index filegroup. So I might flag those indexes to be moved to the appropriate filegroup during a future maintenance window.

The next part of the script will give you a detailed list of each index or heap and where it lives.

[sql]

-- index location details
select
ds.name as 'FG Name',
object_name(i.object_id) as 'Table Name',
i.name as 'Index Name',
i.type_desc as 'Index Type'
-- , o.*
-- , i.*
from sys.objects o
join sys.indexes i
on o.object_id = i.object_id
join sys.data_spaces ds
on i.data_space_id = ds.data_space_id
where o.type_desc = 'USER_TABLE'
-- and i.type_desc = 'CLUSTERED'
-- and i.type_desc = 'NONCLUSTERED'
-- and i.type_desc = 'HEAP'
-- and ds.name = 'PRIMARY'
-- and ds.name = 'MyDataFG'
-- and ds.name = 'MyIndexFG'
order by 'FG Name', 'Table Name', 'Index Name', 'Index Type'
go

[/sql]

Index Filegroup Details

You can modify the where clause to filter the results based on the type of index or the specific filegroup you are interested in.

Go forth and analyze...

No comments:

Post a Comment