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