Grumpy Old DBA - Beware the darkside !

--

-- use with great care !!!

--

SELECT object_name(a.[object_id]) as TableName,a.index_id, isnull(b.name,'HEAP') as IndexName, sum(a.page_count) as pages,sum(a.page_count)*1.0/128 as Mb

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'DETAILED') AS a

    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

group by a.[object_id],a.index_id, b.name

order by pages desc;

GO 

TableName

index_id

IndexName

pages

Mb

Employees

1

PK_Employees

86897

678.860351

Sales

1

PK_Sales

72222

564.529296

TableInfo

0

HEAP

13909

108.583007

Areas

1

PK_Areas

5753

44.618164

declare @table table(object_id int,ind_name sysname,index_id int)

insert into @table

select top 25 ui.[object_id],si.name,ui.index_id

from dbo.Unused_Indexes ui join sys.indexes si

on ui.[object_id] = si.[object_id] and ui.index_id = si.index_id

where objectproperty(ui.[object_id],'IsUserTable') = 1

group by ui.[object_id],ui.index_id,si.name

having count(*) >30

order by count(*) desc,ui.[object_id],ui.index_id asc

--

select  object_name(ui.[object_id]) as TableName,ui.ind_name as IndexName,      

max(fps.index_level)as IndexDepth,sum(fps.page_count) as TotalPages,max(fps.record_count) as LeafRows, sum(fps.page_count)/128 as Mb

from @table ui

CROSS APPLY master.dbo.fn_db_index_physical_stats (DB_ID(),ui.[object_id],ui.index_id,NULL, 'Detailed') AS fps

group by ui.[object_id],ui.ind_name

order by sum(fps.page_count) desc ;

TableName

IndexName

IndexDepth

TotalPages

LeafRows

Mb

Employees

Idx_Employees_123

4

54811

11159545

428.526367

Invoices

Idx_Invoices_paper

4

44532

5162428

347.488281

Invoices

Idx_Invoices_ink

4

39539

5162428

308.612304

DepartmentSales

Idx_DS_Promotions

3

16791

3199181

130.397460

Sales2006

Idx_sales2007_opened

2

116

31291

0.893281

Sales2006

Idx_sales2007_area

2

103

31291

0.800585

Sales2006

Idx_sales2007_size

2

87

31291

0.654960