Used Pages vs Data Pages

• PiMané

  SSChampion

  Points: 13965

  More actions

  November 15, 2012 at 4:44 am

  #265437

  Hi,

  I executed the following code to determine the size that a certain table takes no the database:

  select

  o.name AS Table_Name,

  i.index_id, i.type as i_type, p.rows,

  a.total_pages, a.used_pages, a.data_pages, a.type alloc_type

  from sys.objects o

  inner join sys.indexes i on o.object_id = i.object_id

  inner join sys.partitions as p on i.object_id = p.object_id and i.index_id = p.index_id

  inner join sys.allocation_units as a on p.partition_id = a.container_id

  where o.is_ms_shipped = 0 and o.name= 'AlertasUtilizadorLog'

  order by o.name, i.type

  exec sp_spaceused 'AlertasUtilizadorLog'

  I also executed the sp_spaceused to compare both results.

  The output was:

  Table_Nameindex_idi_typerows total_pages used_pages data_pagesalloc_type

  AlertasUtilizadorLog003195754531

  AlertasUtilizadorLog0031920920802

  AlertasUtilizadorLog223194421

  name rows reserveddata index_sizeunused

  AlertasUtilizadorLog319 2160 KB2088 KB40 KB 32 KB

  the reserved size is the sum of total_pages.

  the data size is the sum of data_pages for alloc_type = 1 and used_pages for alloc_type = 2 and index_type <= 1 (CLUSTERED OR HEAP).

  the unused size is the sum of total_pages less the sum of used_pages.

  now comes my "problem"... the sum of used_pages for indexes (i_type > 1) is 4 and sp_spaceused says 5...

  How is that?! I can get that value, just subtract used_pages to total_pages and I get that value but why hasn't the sum of all indexes have that value?!

  Just one more... total_pages or reserved is the size the data occupies on disk... but used_pages isn't suppose to be the data space or does it have "trash" and the real data is data_pages?

  Thanks,

  Pedro

  ---

  
  If you need to work better, try working less...