cancel
Showing results for 
Search instead for 
Did you mean: 

after creating index on table --reserved and unused space increase

Former Member
0 Kudos

Dear All,

please help me to understand this topic .

after creating index on table --reserved and unused space increase.

create table tmp(id int)

sp_spaceused tmp

name rowtotal reserved data index_size unused

---- -------- -------- ---- ---------- ------

tmp  0        32 KB    4 KB 0 KB       28 KB 

Execution time: 0.058 seconds

sp_spaceused tmp


create index tmp_id on tmp(id)


name rowtotal reserved data index_size unused

---- -------- -------- ---- ---------- ------

tmp  0        64 KB    4 KB 4 KB       56 KB 

Execution time: 0.029 seconds

@@maxpagesize = 4MB

why reserved and unused space increase ? help let me know

regards

abhishek

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member188958
Active Contributor
0 Kudos


ASE allocates space in 8-page chunks that are called extents.  8 pages * 4kb/page = 32KB.

sp_spaceused calls allocated space "reserved".  Extents are allocated separately for the data rows itself and for each index and for LOB data.    Although allocated 8 pages at a time, the pages are used one page at a time.  Any allocated pages that aren't needed to hold data at this time are reflected as "unused" in sp_spaceused.