O'Reilly logo
  • Dustin Waybright thinks this is interesting:

SELECT index_type_desc, page_count,
 record_count, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
    (DB_ID(N'tempdb'), OBJECT_ID(N'dbo.TestStructure', N'U'), NULL, NULL , 'DETAILED');
EXEC dbo.sp_spaceused @objname = N'dbo.TestStructure', @updateusage = true;


Cover of Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012


Heap allocation check to find out how many pages are used. avg_page_space_used_in_percent is a fragmentation check that indicates how spread out the object is across different pages.