CODEWITHSUNDEEP
sql-serverallocation
OfirD
OfirD

Reputation: 10461

Understanding sp_spaceused values: reserved and index_size

I'm running the following simple script:

create table MyTable (filler char(10))
go

insert into MyTable (filler) values ('a')
go 1

exec sp_spaceused MyTable
go

drop table MyTable
go

and get the following result:

rows   reserved   data   index_size    unused
------ ---------- ------ -----------   -------
1      72 KB      8 KB   8 KB          56 KB

My questions:

  1. Why 72 KB were reserved?

  2. Why index_size is 8 KB if the table is not even indexed?

EDIT:

I'd like to add a follow-up: When changing the script a little:

create table MyTable (filler char(69))
go

insert into MyTable (filler) values ('a')
go 100

I get:

rows   reserved   data   index_size    unused
------ ---------- ------ -----------   -------
100    72 KB      16 KB   8 KB          48 KB

Note that defining filler's size to 68 bytes (and inserting 100 rows) still gives 8KB as the data value (we can continue and set it to 148 bytes, which will result in another 8KB increment, i.e. to 24KB).

Can you help me break down the calculation? If (apparently) only 6,900 bytes are used, What is the cause for the 8KB addition?

EDIT #2: Here's the results of DBCC PAGE:

PAGE: (1:4392)


BUFFER:


BUF @0x00000000061A78C0

bpage = 0x00000001EF3A8000          bhash = 0x0000000000000000          bpageno = (1:4392)
bdbid = 6                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 18482                       bstat = 0x9
blog = 0x15ab215a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x00000001EF3A8000

m_pageId = (1:4392)                 m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8200
m_objId (AllocUnitId.idObj) = 260   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594054967296                                
Metadata: PartitionId = 72057594048151552                                Metadata: IndexId = 0
Metadata: ObjectId = 1698105090     m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 72                        m_slotCnt = 100                     m_freeCnt = 396
m_freeData = 7596                   m_reservedCnt = 0                   m_lsn = (55:8224:2)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = -2116084714            DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

Slot 0 Offset 0x60 Length 75

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 75

Memory Dump @0x0000000012A3A060

0000000000000000:   10004800 61202020 20202020 20202020 20202020  ..H.a               
0000000000000014:   20202020 20202020 20202020 20202020 20202020                      
0000000000000028:   20202020 20202020 20202020 20202020 20202020                      
000000000000003C:   20202020 20202020 20202020 010000                         ...

Slot 0 Column 1 Offset 0x4 Length 68 Length (physical) 68

filler = a                                                                   

-- NOTE: The structure of each Slot is identical to that of Slot #0, so we can simply jump to slot 99:

Slot 99 Offset 0x1d61 Length 75

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 75

Memory Dump @0x0000000012A3BD61

0000000000000000:   10004800 61202020 20202020 20202020 20202020  ..H.a               
0000000000000014:   20202020 20202020 20202020 20202020 20202020                      
0000000000000028:   20202020 20202020 20202020 20202020 20202020                      
000000000000003C:   20202020 20202020 20202020 010000                         ...

Slot 99 Column 1 Offset 0x4 Length 68 Length (physical) 68

filler = a

So we can see the last slot starts after 7521 bytes, and adding its size gives us 7,596 bytes. If we add the size of the slot array (in which each pointer is 2 bytes), we get 7,796 bytes.

However, we need to get to 8,192 bytes to fill the page. What's missing?

Upvotes: 1

Views: 1701

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

The 72K of reserved space includes a 64K extent (8 pages of 8K each) plus the 8K IAM page overhead. Of this 72K, only the IAM page and a single data page is actually used. sp_space_used reports the IAM page in the index_size, albeit not technically an index. You can see these details with the undocumented sys.dm_db_database_page_allocations TVF (use only on a test system):

SELECT extent_file_id, extent_page_id, page_type_desc
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.MyTable'), 0, 1, 'DETAILED');

This database apparently has the MIXED_PAGE_ALLOCATION database option set to OFF so a full 64K extent is allocated a initially. If the option were ON, the single data page would be allocated from a mixed extent instead of a 64K extent dedicated to the table. The space allocated in that case would be 16K - a 8K single data page plus the IAM page.

Although mixed extents do reduce space requirements for small tables (under 64K), mixed extents have more overhead and can cause allocation contention in a high concurrency workload so it is off by default in SQL 2016 onwards. In older SQL versions, mixed extent allocation was on by default and can be turned off at the server level with trace flag 1118.

You can see the mixed extent setting in sys.databases:

SELECT name, is_mixed_page_allocation_on
FROM sys.databases;

To toggle the setting:

ALTER DATABASE Test
    SET MIXED_PAGE_ALLOCATION ON;

EDIT 1:

Space within a data page includes overhead for the page itself as well as records within the page. This overhead, plus the space needed for user data, will determine how many rows can fit on a page and number of data pages required to store a given number of rows. See Paul Randal's anatomy of a page and anatomy of a record articles for details of that overhead.

EDIT 2:

From your follow-up comment:

7998 bytes, so there are more 194 bytes to go for the next allocation. What am I missing?

I almost never use heaps but as you can see in the page dump, the associated PFS (page free space) allocation status for this page is 100 percent full. According to Kalen Delaney's Microsoft SQL Server 2012 Internals book, the PFS status is actually a 3-bit mask of these ranges:

  • 000: empty
  • 001: 1-50% full
  • 010: 51-80% full
  • 011: 81-95% full
  • 100: 96-100% full

So it looks like once heap page fullness crossed the 96% percent threshold it was considered 100% full and a new page was allocated. Note this does not happen on a table with a clustered index because the page for a new row is first determined by the CI key and a new page allocated only if it can't fit in that page at all. Yet another reason to avoid heaps.

Upvotes: 4

Related Questions