/**//* ** Now calculate the summary data. * Note that LOB Data and Row-overflow Data are counted as Data Pages. */ SELECT @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 ), @rowCount=SUM ( CASE WHEN (index_id <2) THEN row_count ELSE0 END ) FROM sys.dm_db_partition_stats WHEREobject_id=@id; /**//* ** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table */ IF (SELECTcount(*) FROM sys.internal_tables WHERE parent_id =@idAND internal_type IN (202,204)) >0 BEGIN /**//* ** Now calculate the summary data. Row counts in these internal tables don't ** contribute towards row count of original table. */ SELECT @reservedpages=@reservedpages+sum(reserved_page_count), @usedpages=@usedpages+sum(used_page_count) FROM sys.dm_db_partition_stats p, sys.internal_tables it WHERE it.parent_id =@idAND it.internal_type IN (202,204) AND p.object_id= it.object_id; END SELECT name =OBJECT_NAME (@id), rows =convert (char(11), @rowCount), reserved =LTRIM (STR (@reservedpages*8, 15, 0) +' KB'), data =LTRIM (STR (@pages*8, 15, 0) +' KB'), index_size =LTRIM (STR ((CASEWHEN@usedpages>@pagesTHEN (@usedpages-@pages) ELSE0END) *8, 15, 0) +' KB'), unused =LTRIM (STR ((CASEWHEN@reservedpages>@usedpagesTHEN (@reservedpages-@usedpages) ELSE0END) *8, 15, 0) +' KB')
用于存储和管理分区中的行内数据的总页数。该计数包括非叶 B 树页、IAM 页以及 in_row_data_page_count 列包含的全部页。
in_row_data_page_count
分区中存储行内数据所用的页数。如果分区是堆的一部分,则该值为堆中的数据页数。如果分区是索引的一部分,则该值为叶级别中的页数。(未计入 B 树中非叶页的数目。)以上两种情况都未计入 IAM(索引分配映射)页。 针对我们目前该表的情况,仅是一个堆表,那么可知前者是包含了IAM页,而后者不含有IAM页,那么sp_spaceused中的index_size在这里就是一个IAM(索引分配映射)页。