Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Thursday, March 03, 2011

I'm just going to decorate this post with a much more upbeat photo of Denise Milani in a captivating, top-heavy series of photographs in a Southwest desert setting. 

Thinking of how big things are, I recently ran into an issue of an index file group growing pretty large, and I wanted to know which indexes were in it, and how big they were.  My biggest concern was non-clustered indexes that were changed to clustered indexes (causing the whole table to move into the index file group).

I have previously found two great scripts, one that shows the size of an index and another that shows what is in a file group.  I combined these two together to get a complete picture of what I needed.

Script for Indexes Size and What File Group they are in:

Select FileGroup = FILEGROUP_NAME(a.data_space_id),index_listing.* from (
Select
    s.[name] as [schema],
    Case
  When i.[name] is null
  Then o.[name]
  Else i.[name]
 end as name,
    i.type_desc,
    space_used_in_mb = (page_count * 8.0 / 1024.0),
    space_used_in_kb = (page_count * 8.0),
 i.object_id,i.index_id
From
    sys.indexes I
Inner Join
    sys.dm_db_index_physical_stats(db_id(), object_id('.'), null, null, null) P
  On I.[object_id] = P.[object_id]
  and I.[index_id] = P.[index_id]
Inner Join
 sys.objects o
  on i.[object_id] = o.[object_id]
Inner Join sys.schemas s
  on o.[schema_id] = s.[schema_id]
) index_listing
INNER JOIN sys.partitions p on index_listing.object_id = p.object_id
 AND index_listing.index_id = p.index_id
Left Join sys.allocation_units a ON a.container_id = CASE WHEN a.type in(1,3)
 THEN p.hobt_id ELSE p.partition_id END AND p.object_id > 1024
--where FILEGROUP_NAME(a.data_space_id) = 'Index_data'
-- and index_listing.type_desc = 'Clustered'
Order by space_used_in_mb Desc

Thursday, March 03, 2011 9:26:54 AM (Central Standard Time, UTC-06:00) |  | SQL Server#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
Default Trace - Heavy load, turn it...
SQL 2008 R2 License / Cost = Open S...
Door Installation
Recent Posts
Archive
October, 2019 (1)
September, 2019 (1)
August, 2019 (1)
May, 2019 (1)
April, 2019 (1)
March, 2019 (1)
February, 2019 (1)
January, 2019 (1)
December, 2018 (1)
November, 2018 (3)
October, 2018 (1)
June, 2018 (1)
May, 2018 (3)
April, 2018 (2)
February, 2018 (3)
January, 2018 (3)
November, 2017 (1)
October, 2017 (1)
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll