THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Removing Single Page Allocations

This post is not for the faint of heart. Read on at your own risk. It illustrates a change in behavior between SQL Server 2000 and SQL Server 2005 regarding how and when pages will be allocated one at a time.

 

In my SQL Server Internals classes, I describe the way that SQL Server performs single page allocations until a table has 8 pages, and then it starts allocating full extents (called Uniform extents) to give a table or index 8 pages at a time. The single page allocations are done so that if it turns out the table is a very small table, with just a couple of pages in it, we will not be wasting whole pages of space. The script below illustrates this behavior by creating a table with very large rows, so that only one row will fit per page. I do this so that you can easily fill lots of pages.

 

USE tempdb

GO

SET NOCOUNT ON

GO

CREATE TABLE LargeRows

     (col1 int identity,
      col2 char(8000) default '8000 bytes of data')

GO

 

After creating the table, I start filling it will pages of data, and look at the space used after each page insert. Run the following two statements 8 times. You can do this in a query window by either highlight the INSERT and the EXEC lines, and hitting the EXECUTE button 8 times, or by including the GO 8 and hitting the EXECUTE button once.

 

INSERT INTO LargeRows DEFAULT VALUES

EXEC sp_spaceused LargeRows

GO 8

 

If you look at the values for reserved space and used space, they should be increasing at the same rate. The first row allocates one page (8K) for data, and one page for an IAM (Index Allocation Map). Every new row allocates one more data page, so reserved is always 8K more than data, for the first 8 pages. When your run the INSERT one more time, you should notice that RESERVED space jumps to 200 KB, and now we have 56K of unused space. An entire extent of 64K was allocated when the 9 page was needed. For the next 7 pages, SQL Server will use one of the already allocated pages, and DATA will increase while UNUSED decreases, and RESERVED stays constant.

 

The undocumented procedure DBCC EXTENTINFO shows you one row of output for every allocation. So if I look at the EXTENTINFO output for my LargeRows table, after 10 pages have been allocated, the first four columns will look like this:

 

DBCC EXTENTINFO(tempdb, LargeRows, -1)

GO

file_id     page_id     pg_alloc    ext_size   

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

1           1009        1           1          

1           1011        1           1          

1           1014        1           1          

1           1015        1           1          

1           1016        1           1          

1           1017        1           1          

1           1018        1           1          

1           1024        2           8

 

Of course, your actual page numbers (and file number) may vary. The ext_size value represents how many pages were allocated in a single allocation. The pg_alloc value indicates how many of those pages have been used. For the first 8 row, both values are one. The 9 row indicates we allocated a full 8-page extent, but only 2 pages have been used. For these full extents, the page_id is the first page of the extent and should always be a multiple of 8.

 

In my class last week one of the students asked how we could get rid of the single page allocations.  In SQL Server 2000, you could do this by just creating a clustered index on the table. I assumed this was the case, because if SQL Server knows right when it starts building an index that there will be more than 8 pages, there should be no need for single page allocations.

 

But when I tried to illustrate that behavior on SQL Server 2005, I was taken by surprise. The single page allocations remained, even though they now all had new pages numbers, as building a clustered index completely moves all the data.

 

I found out that the algorithm has changed. In SQL Server 2005, single page allocation is only disabled for the leaf level of the clustered index (the actual table data) if the table has least 3 extents (24 pages). Allocations to the upper levels use a different allocator, and if there are less than 24 pages for the upper index levels, they will also use single page allocations. So in SQL Server 2005, I need at least 24 data pages to remove the single page allocation for the data. So now I can drop the table, recreate it, and populate it with 25 pages.

 

DROP TABLE LargeRows

GO

CREATE TABLE LargeRows

     (col1 int identity, col2 char(8000) default '8000 bytes of data')

GO

INSERT INTO LargeRows DEFAULT VALUES

GO 25

DBCC EXTENTINFO(tempdb, LargeRows, -1)

GO

CREATE CLUSTERED INDEX LargeRow_index on LargeRows(col1)

GO

DBCC EXTENTINFO(tempdb, LargeRows, -1)

GO

 

 

The second execution of DBCC EXTENTINFO will show 4 extents, one of which only has a single page used. There is still one single page allocation, which is for the index. I would need a lot of data rows to force SQL Server to use more than 24 pages for the upper level index pages, so you can test that out on your own.

 

Of course, you could also take a look at the KB article entitled:

FIX: Concurrency enhancements for the tempdb database

http://support.microsoft.com/kb/328551

 

Among other things, this article describes a traceflag that inhibits most single page allocations across the whole server. The article claims it was last reviewed in July 2006, and does not list SQL Server 2005 as one of the products it applies to. However, the trace flag definitely does still work in SQL Server 2005. Be careful if you decide to use it however, as it is a server-wide flag. Read the KB article to understand the caveats.

 

Have fun!

 

-- Kalen

 

Published Monday, October 23, 2006 7:05 AM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AGiotti said:

Hello Ms. Delaney,

I have a question regardnig single page allocation. This is an actual production database. Output from showcontig

- Pages Scanned................................: 87294

- Extents Scanned..............................: 54352

The majority of these extent allocation only have 1 page. This has caused the database to expand dramatically. This table is a heap and only has one nonclustered index. Below is a subset of the DBCC EXTENTINFO output. Adding a clustered index has repaired this issue. However, I need to understand the internals on why this had occured. I would appreciate any valuable input.

file_id page_id pg_alloc ext_size obj_id index_id

1 4287920 1 8 101575400 0

1 4287928 1 8 101575400 0

1 4287936 1 8 101575400 0

1 4287944 1 8 101575400 0

1 4287952 1 8 101575400 0

1 4287960 1 8 101575400 0

1 4287968 1 8 101575400 0

1 4287976 1 8 101575400 0

June 7, 2007 1:06 PM
 

AGiotti said:

Hi Ms. Delaney,

I have just found out that these tables are subjected to an archiving process. Therefore, the unused pages in these extents is, in my opinion, a result of the archive process. Many of these tables do not have a clustered index, therefore, to remove the unused space/pages I just created a clustered index and then dropped it. I cannot leave the clustered index in place because this is owned by a third party vendor.

June 7, 2007 5:15 PM
 

Mohammad Aslam,KPIT said:

Excellent Post ,Kalen.

January 18, 2011 8:00 AM
 

Rajesh said:

Hi Kalen,

If I run dbcc extentinfo command, and it shows that I have two pages left in a extent, now I run delete some records that freed two pages,

so if now I insert 2 records, that require 2 pages, will they allocated new pages or is it allocate them the pages whcih got free as a result of delete operation?

I just wanted to know, if some previously allocated pages got free, so for a new insert operation, is it insert data in those pages or data is allocated from the new extents ?

October 7, 2012 3:38 AM
 

Rajesh said:

I am running it in SQL Server 2008

October 7, 2012 3:40 AM
 

Kalen Delaney said:

Rajesh

In general, if there are already unallocated pages in allocated extents, those will be used for new data in a table before SQL Server will allocate whole new extents, Whether it will reuses pages that were used before or use ones that have not been used yet, I couldn't say. The thing SQL Server will try to avoid is allocated new extents.

I hope this helps

Kalen

October 10, 2012 9:54 PM
 

prince kumar rastogi said:

Hi Kalen,

In SQL Server 2000 it seems fine that after creating clustered index on a table which is having more than 8 pages , it starts using extents.

But why microsoft team change this thing for 2005 and 2008 i.e. after creating clustered index on a table which is having more than 24 pages, it starts using extents?

Is there any reason behind this to do this change?  

November 12, 2012 2:33 AM
 

SKIM said:

how can this solve allocation errors that i have on one of my dbs. Extent(1:230039) in db id is marked allocated in the gam but no sgam or iam has alloacted it.will appreciate input.

September 19, 2013 4:33 AM
 

Kalen Delaney said:

SKIM

If the extent is a uniform extent, it will not be set in the SGAM. The SGAM is only for pages that were allocated individually.

How do you know there are no IAMs referencing this extent? If this is an error message you are getting during DBCC, it may indicate corruption. And it is beyond the scope of a simple reply to a blog comment to deal with corruption issues.

~Kalen

September 19, 2013 10:17 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement