on April 21, 2009
I am going to post my monstrously big index query.
Why? Because it’s AWESOME. No really, it actually is awesome. At least, if you like that sort of thing. I use some variant of this almost daily, and I tweak it fairly regularly to suit the needs of whatever I’m working on. So it’s a work in progress, but I find it constantly valuable.
Awesome? Oh Really? Why?
This query describes the size, basic definition, location, number of rows, partition status, and enabled/disabled status for all clustered and nonclustered indexes in a database. I typically sort them by descending size, since my primary usage is when a drive space alert fires, or when someone asks one of the million “how much space would it take if we wanted to [x]?” questions.
When you are working with a database which has many indexes that are partitioned over multiple filegroups, which are spread out over multiple drives, this can be very useful when a reindex fails due to a file filling up. Or when you want to estimate how much free space you need to main in a given filegroup in order to be able to reindex the indexes using it.
And it’s also just a really useful big picture reference for what you’ve got, where it is, and how much room it’s taking up.
And it’s fast.
Well, Indexes aren’t Everything.
That depends who you ask. ;)
And I’m actually cheating, because I include index id 0, which is actually no index at all. As an extra bonus for the introductory offer, you get HEAPS for free in this query. Yes, you heard it here first.
But I don’t have index usage in this. At least not yet! I’m considering working in a rating since last sql start, but that’s for another day.
Extra disclaimer: As I said, I modify this periodically, and I very recently changed this so it will show disabled indexes. I’ve used it a bit after the changes and it seems to be working well, but I haven’t exactly put it through a rigorous amount of tests at this point.
So Let’s See this “Awesome” Index Query Already
SELECT
schemaName=sch.name
, tableName=o.name
, indexId=i.index_id
, indexName= case i.index_id when 0 then '(HEAP)' else i.name end
, isDisabled = i.is_disabled
, columnNames= case i.index_id when 0 then '(HEAP)' else c.indexColumns end
, totalSizeGB = cast(sum(au.total_pages)*8./1024./1024. as numeric(10,1))
, numRows = (select sum([rows]) from sys.partitions p2 (nolock)
where p2.object_id=o.object_id and p2.index_id in (0,1))
, partitionInfo = cast(p.partition_number as nvarchar(5)) + ' of ' +
cast((select max(partition_number) from sys.partitions p2 (nolock)
where p2.object_id=o.object_id and p2.index_id = I.index_id) as nvarchar(5))
, filegroup = (select ds2.name + N' (' + cast(ds2.type as nvarchar) + N')'
from sys.data_spaces ds2 (nolock)
where ds2.data_space_id=au.data_space_id) COLLATE SQL_Latin1_General_CP1_CI_AS
, dataFiles = (select df.name + ' (' + left(df.physical_name,2) + ')' as 'data()'
from sys.database_files df
where df.data_space_id=au.data_space_id
FOR XML PATH (''))
, createDate= o.create_date
, modifyDate= o.modify_date
, inRowGB = cast(sum(
case au.[type] when 1 then au.total_pages else 0 end
)*8./1024./1024. as numeric(10,1))
FROM sys.indexes AS i (NOLOCK)
JOIN sys.objects AS o (NOLOCK) on
i.object_id = o.object_id
and o.type in ('U','V')
JOIN sys.schemas AS sch (NOLOCK) on
o.schema_id=sch.schema_id
LEFT JOIN sys.partitions AS p (NOLOCK) on --outer join here so we still include disabled indexes
p.index_id = i.index_id
AND i.object_id = p.object_id
LEFT JOIN sys.allocation_units AS au (NOLOCK)ON
au.container_id = p.partition_id
LEFT JOIN (select distinct
object_id
, index_id
, indexColumns = (SELECT col_name(object_id,column_id )
+ case is_included_column when 1 then '(INCLUDED)' else '' end as 'data()'
FROM sys.index_columns t2 (NOLOCK)
where t1.object_id =t2.object_id
and t1.index_id = t2.index_id
FOR XML PATH (''))
FROM sys.index_columns t1 ) c on
c.index_id = i.index_id
and c.object_id = i.object_id
GROUP BY
sch.name
, o.name
, o.[object_id]
, o.create_date
, o.modify_date
, i.name
, i.is_disabled
, i.index_id
, p.partition_number
, c.indexColumns
, au.data_space_id
ORDER BY TotalSizeGB desc;
GO