April 19, 2012 at 1:42 pm
I have a spreadsheet that someone else run from dm_db_index_physical_stats
here are the headings
Object Nameindex_idRecord CountPage CountSize KB
I only know the he runs from the dm_db_index_physical_stats, but not know the exact query is?
It is running for a specific database we have, and I see it has all the tables in the spreadsheet.
Also what is the record count, page count and size KB means, are they only for Indexes size, and page counts, record counts, or it also include data size and data page counts?
Thanks
April 19, 2012 at 1:54 pm
April 19, 2012 at 1:55 pm
Probably a simple query of sys.dm_db_index_physical_stats joined to sys.tables with the page count used to compute the index size. Easiest way to be sure would be to ask the person who sent you the spreadsheet what query he used.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2012 at 4:38 pm
Here is the query:
SELECT OBJECT_NAME([object_id]),index_id,SUM(record_count),SUM(page_count)
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,0,'DETAILED')
GROUP BY OBJECT_NAME([object_id]),index_id
ORDER BY 3 DESC
My question: do the record_count and page_count mean for both data and index, or for index only?
Thanks,
April 23, 2012 at 4:40 pm
It's the size of that particular object, so the entry for index id 1 will show the size of the table (because that's the clustered index), the entry for index id 2 will show the size of that particular nonclustered index, etc, etc
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2012 at 4:58 pm
GilaMonster (4/23/2012)
It's the size of that particular object, so the entry for index id 1 will show the size of the table (because that's the clustered index), the entry for index id 2 will show the size of that particular nonclustered index, etc, etc
Thank you , could you explain a little more : the entry for index id 1 will show the size of the table (because that's the clustered index)
April 23, 2012 at 5:24 pm
What more is there to say?
Index 1 is the clustered index, the actual data pages are at the leaf level (review Books Online the definition of the clustered index) As such, the size of the clustered index is the data size of the table (well, the size of the for the leaf level is).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2012 at 2:59 am
Thank you very much, that helps a lot.
April 24, 2012 at 8:52 am
sqlfriends (4/24/2012)
Thank you very much, that helps a lot.
If all you're looking for is index sizes and row counts, etc try this
selectobject_name(i.object_id) as TableName
, ISNULL(i.name, 'HEAP') as IndexName
, i.index_id as IndexID
, i.type_desc as IndexType
, p.partition_number as PartitionNo
, p.rows as NumRows
, au.type_desc as InType
, au.total_pages as NumPages
, au.total_pages / 128 as TotMBs
, au.used_pages / 128 as UsedMBs
, au.data_pages / 128 as DataMBs
from sys.indexes i inner join sys.partitions p
on i.object_id = p.object_id and i.index_id = p.index_id
inner join sys.allocation_units au on
case
when au.type in (1,3) then p.hobt_id
when au.type = 2 then p.partition_id
end = au.container_id
where object_name(i.object_id) not like 'sys%'
and object_name(i.object_id) <> 'dtproperties'
and p.rows > 0
order by TableName
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 24, 2012 at 4:42 pm
Thank you, will give it a try
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply