sp_spaceused on a heap

• GordonLiddy

  Ten Centuries

  Points: 1047

  More actions

  September 6, 2010 at 2:23 am

  #237956

  Hi there!

  Why is it that sp_spaceused returns the value 8 KB for index_size on a heap table, where no index has been created (at least not on purpose, using CREATE INDEX or PRIMARY KEY)?

  CREATE TABLE dbo.asdf

  (c1 int);

  INSERT dbo.asdf SELECT 1;

  sp_spaceused 'dbo.asdf';

  Result:

  namerows reserveddataindex_size

  asdf1 16 KB 8 KB8 KB

  Is it because the page/data/table structure is "prepared for, and has reserved" an index page, for future indexes?

  BOL says this about column index_size: Total amount of space used by indexes in objname.

  Thanks in advance!

• jncarter

  Ten Centuries

  Points: 1195

  More actions

  September 6, 2010 at 9:07 am

  #1216961

  When you create the table you will see one data page and one IAM page created. The IAM page (PageType=10) tracks the data allocations and is counted towards the index size. DBCC IND will show you the page allocations as below.

  dbcc ind('adventureworks','dbo.asdf',1)

  Result:

  PageFIDPagePIDIAMFIDIAMPIDObjectIDIndexIDPartitionNumberPartitionIDiam_chain_typePageType

  1277NULLNULL2636719870172057594074890240In-row data10

  127612772636719870172057594074890240In-row data1

  *I cut off the last part of the results so it would fit better and they weren't really relevant here...

  You can play around with it by adding additional rows, a clustered index, etc and see how the pages are allocated to store your data.

  -Jeremy

• GordonLiddy

  Ten Centuries

  Points: 1047

  More actions

  September 7, 2010 at 8:25 am

  #1217370

  Thank you for your answer Jeremy!

  That makes it a bit clearer...

  I'll do as you suggest, and run DBCC IND with different combinations of page, data and index allocations.

  Take care.