November 18, 2009 at 9:47 am
Does anyone have a query that calculates the storage usage of an index? I was trying to tie in the results of the below qry with sys.dm_db_index_physical_stats but it seemed overkill for what i was looking for while trying to create this as dynamically as possible.
[Code="SQL"]
SELECT DB_NAME() AS DATABASENAME,
OBJECT_NAME(B.OBJECT_ID) AS TABLENAME,
B.NAME AS INDEXNAME,
B.INDEX_ID
FROM SYS.OBJECTS A
INNER JOIN SYS.INDEXES B
ON A.OBJECT_ID = B.OBJECT_ID
WHERE NOT EXISTS (SELECT *
FROM SYS.DM_DB_INDEX_USAGE_STATS C
WHERE B.OBJECT_ID = C.OBJECT_ID
AND B.INDEX_ID = C.INDEX_ID)
AND A.TYPE in ('U', 'V')
AND OBJECT_NAME(B.OBJECT_ID) not like 'sys%'
AND OBJECT_NAME(B.OBJECT_ID) not like 'dtp%'
And B.NAME is not null
ORDER BY 1, 2, 3
[/Code]
John Zacharkan
November 18, 2009 at 9:58 am
maybe something like this...
select
i.[object_id],
i.index_id,
p.partition_number,
p.rows as [#Records],
a.total_pages * 8 as [Reserved(kb)],
a.used_pages * 8 as [Used(kb)]
from
sys.indexes as i
inner join
sys.partitions as p
on i.object_id = p.object_id
and i.index_id = p.index_id
inner join
sys.allocation_units as a
on p.partition_id = a.container_id
where
i.[object_id] = object_id('dbo.Person')
and i.index_id = 1 -- clustered index
order by
p.partition_number
go
Oraculum
November 18, 2009 at 10:29 am
With IndexSize as (
SELECT objectname = object_name(object_id) ,reservedpages = SUM (reserved_page_count),usedpages = SUM (used_page_count)
,pages = SUM (
CASE
WHEN (index_id < 2)
THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
)
,srowCount = SUM (
CASE
WHEN (index_id < 2)
THEN row_count
ELSE 0
END
)
FROM sys.dm_db_partition_stats
Where OBJECTPROPERTY(object_id, 'IsUserTable') = 1
group by object_id
)
select objectname, index_size = LTRIM (STR ((CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
from IndexSize
Order By ObjectName Asc
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 18, 2009 at 10:46 am
And to combine the two:
With IndexSize as (
SELECT objectname = object_name(object_id) ,reservedpages = SUM (reserved_page_count),usedpages = SUM (used_page_count)
,pages = SUM (
CASE
WHEN (index_id < 2)
THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
)
,srowCount = SUM (
CASE
WHEN (index_id < 2)
THEN row_count
ELSE 0
END
)
FROM sys.dm_db_partition_stats
Where OBJECTPROPERTY(object_id, 'IsUserTable') = 1
group by object_id
)
SELECTDB_NAME() AS DATABASENAME, OBJECT_NAME(B.OBJECT_ID) AS TABLENAME, B.NAME AS INDEXNAME, B.INDEX_ID
,index_size = LTRIM (STR ((CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
FROMSYS.OBJECTS A
INNER JOIN SYS.INDEXES B
ON A.OBJECT_ID = B.OBJECT_ID
Left Outer Join SYS.DM_DB_INDEX_USAGE_STATS C
ON B.OBJECT_ID = C.OBJECT_ID
AND B.INDEX_ID = C.INDEX_ID
Inner Join IndexSize I
On I.ObjectName = Object_Name(B.Object_Id)
WHEREC.Object_Id Is Null
AND A.TYPE in ('U', 'V')
AND OBJECTPROPERTY(a.object_id, 'IsUserTable') = 1
And B.NAME is not null
ORDER BY DB_Name(), OBJECT_NAME(B.OBJECT_ID), B.NAME
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 18, 2009 at 12:35 pm
oraculum - thank you for responding so quickly and providing input :-).
Jason - exactly what i was looking for thank you for putting that together.
John Zacharkan
November 18, 2009 at 12:46 pm
NP.
One last tidbit. It was written in 2k5. If it needs to work for SQL 2000, you will need to modify as you need.:cool:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply