Tags: SQL Server Internals, webinar
Thanks to everyone who attended! You can get the slides and demo files here:
Webinar 2.2 — Allocation Information
If you missed the session, or would like to watch the recorded version, you can get a link to access the video here.
Webinar #2.2 Homework:
1. As we saw, with mixed_page_allocation set to ON, rebuilding a small table will still leave pages on mixed extents. However, once a table gets bigger, rebuilding it will put all the pages in uniform extents.
For this exercise, you are asked to find the ‘tipping point’. How many pages does a heap have to have so that when a clustered index is created, it uses uniform extents for the data pages?
This exercise didn’t take a lot of creative effort to solve; it basically just took some time and effort. I gave you the script to add 24 pages to a table and the build a clustered index. That showed that (if mixed_page_allocation was set to ON) the table would continue to use mixed extents. To find the tipping point, you needed to just try bigger numbers. You could have started with something really big, like 200 pages, and seen that a building a clustered index on a heap with 200 pages would use uniform extents, and then try to narrow it down.
I rewrote the query against the DVM (the one that shows whether we had mixed extents or not) so that the output is a little narrower.
1 2 3 4 5 | SELECT allocated_page_file_id AS PageFID, allocated_page_page_id AS PagePID, object_id AS ObjectID, page_type AS PageType, extent_file_id, extent_page_id, is_mixed_page_allocation FROM sys.dm_db_database_page_allocations(db_id('test'), object_id('bigrows'), NULL, NULL, 'DETAILED'); GO |
It turns out that you only need 25 pages (anything greater than 3 extents) turn remove the mixed page allocations. If I just change my script to insert 25 rows, and then build a clustered index, the query above gives me the following:
The last column is what’s important. The data pages (PageType = 1) all show is_mixed_page_allocation = 0, which means they were allocated from uniform extents. The only pages from mixed extents are the IAM page and the single index page.
2. Is the behavior the same when rebuilding the table and leaving it as a heap?
This is actually quite surprising. I’m going to include the whole script here, just for completeness. What you should have found is that rebuilding a heap always uses uniform extents, even if there is just one page!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | ALTER DATABASE test SET mixed_page_allocation ON; GO USE test; GO DROP TABLE IF EXISTS bigrows; GO CREATE TABLE bigrows (col1 INT IDENTITY, col2 CHAR(8000) DEFAULT 'hello'); GO USE test; GO INSERT INTO bigrows DEFAULT VALUES GO SELECT allocated_page_file_id AS PageFID, allocated_page_page_id AS PagePID, object_id AS ObjectID, page_type AS PageType, extent_file_id, extent_page_id, is_mixed_page_allocation FROM sys.dm_db_database_page_allocations(db_id('test'), object_id('bigrows'), NULL, NULL, 'DETAILED'); GO ALTER TABLE bigrows REBUILD; GO SELECT allocated_page_file_id AS PageFID, allocated_page_page_id AS PagePID, object_id AS ObjectID, page_type AS PageType, extent_file_id, extent_page_id, is_mixed_page_allocation FROM sys.dm_db_database_page_allocations(db_id('test'), object_id('bigrows'), NULL, NULL, 'DETAILED'); GO |
The output below is both the before and after results from the DMV. Originally there is only the IAM page and the single data page. After executing the ALTER TABLE, there are 9 pages. One IAM, and 8 pages from the uniform extent starting at page 1:416. Only the first one is indicated to be a data page; the others have not been used for anything so their page type is NULL.
