IAM pages are allocated in the same district, or distribution in the mixed zone?

IAM pages are allocated in the unified area or in the mixed zone distribution?

IAM page role here is not to say, online information a lot

The tools used in: To view the SQLSERVER internal data page plugin Internals Viewer

To establish the four tables, forms, clustered index on the table, a non clustered index on the table, clustered and nonclustered table

1 USE master 2 GO 3 --The new databaseIAMDB 4 CREATE DATABASE IAMDB 5 GO 6 7 USE IAMDB 8 GO 9 10 11 --DROP TABLE heaptable 12 --DROP TABLE clusteredtable 13 --DROP TABLE nonclusteredtable 14 --DROP TABLE clusteredandnonclusteredtable 15 16 --Create table test 17 CREATE TABLE heaptable(c1 INT IDENTITY(1,1), c2 VARCHAR (5000)) 18 GO 19 CREATE TABLE clusteredtable(c1 INT IDENTITY(1,1), c2 VARCHAR (5000)) 20 GO 21 CREATE TABLE nonclusteredtable(c1 INT IDENTITY(1,1), c2 VARCHAR (5000)) 22 GO 23 CREATE TABLE clusteredandnonclusteredtable(c1 INT IDENTITY(1,1), c2 VARCHAR (5000)) 24 GO 25 26 --Create index 27 CREATE CLUSTERED INDEX cix_clusteredtable ON clusteredtable([C1]) 28 GO 29 CREATE INDEX ix_nonclusteredtable ON nonclusteredtable([C1]) 30 GO 31 CREATE CLUSTERED INDEX cix_clusteredandnonclusteredtable ON clusteredandnonclusteredtable([C1]) 32 GO 33 CREATE INDEX ix_clusteredandnonclusteredtable ON clusteredandnonclusteredtable([C1]) 34 GO 35 36 --Insertion of test data 37 DECLARE @a INT; 38 SELECT @a = 1; 39 WHILE (@a <= 3) 40 BEGIN 41 INSERT INTO heaptable VALUES ( replicate('a', 5000)) 42 SELECT @a = @a + 1 43 END 44 45 46 47 DECLARE @a INT; 48 SELECT @a = 1; 49 WHILE (@a <= 3) 50 BEGIN 51 INSERT INTO clusteredtable VALUES ( replicate('a', 5000)) 52 SELECT @a = @a + 1 53 END 54 55 56 DECLARE @a INT; 57 SELECT @a = 1; 58 WHILE (@a <= 3) 59 BEGIN 60 INSERT INTO nonclusteredtable VALUES ( replicate('a', 5000)) 61 SELECT @a = @a + 1 62 END 63 64 65 DECLARE @a INT; 66 SELECT @a = 1; 67 WHILE (@a <= 3) 68 BEGIN 69 INSERT INTO clusteredandnonclusteredtable VALUES ( replicate('a', 5000)) 70 SELECT @a = @a + 1 71 END 72 73 --Query data 74 SELECT * FROM heaptable ORDER BY [c1] ASC 75 SELECT * FROM clusteredtable ORDER BY [c1] ASC 76 SELECT * FROM nonclusteredtable ORDER BY [c1] ASC 77 SELECT * FROM clusteredandnonclusteredtable ORDER BY [c1] ASC

The table is characterized by a row just take a page

Create table DBCCResult

1 USE [IAMDB] 2 GO 3 CREATE TABLE DBCCResult ( 4 PageFID NVARCHAR(200), 5 PagePID NVARCHAR(200), 6 IAMFID NVARCHAR(200), 7 IAMPID NVARCHAR(200), 8 ObjectID NVARCHAR(200), 9 IndexID NVARCHAR(200), 10 PartitionNumber NVARCHAR(200), 11 PartitionID NVARCHAR(200), 12 iam_chain_type NVARCHAR(200), 13 PageType NVARCHAR(200), 14 IndexLevel NVARCHAR(200), 15 NextPageFID NVARCHAR(200), 16 NextPagePID NVARCHAR(200), 17 PrevPageFID NVARCHAR(200), 18 PrevPagePID NVARCHAR(200) 19 )

We see what a heaptable IAM page

1 --TRUNCATE TABLE [dbo].[DBCCResult] 2 3 INSERT INTO DBCCResult EXEC ('DBCC IND(IAMDB,heaptable,-1) ') 4 5 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC

The IAM page number is 80

See what a clusteredtable IAM page

1 --TRUNCATE TABLE [dbo].[DBCCResult] 2 3 INSERT INTO DBCCResult EXEC ('DBCC IND(IAMDB,clusteredtable,-1) ') 4 5 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC

The IAM page number is 120

See what a nonclusteredtable IAM page

1 --TRUNCATE TABLE [dbo].[DBCCResult] 2 3 INSERT INTO DBCCResult EXEC ('DBCC IND(IAMDB,nonclusteredtable,-1) ') 4 5 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC

The IAM page number is 121 and 110

See what a clusteredandnonclusteredtable IAM page

1 --TRUNCATE TABLE [dbo].[DBCCResult] 2 3 INSERT INTO DBCCResult EXEC ('DBCC IND(IAMDB,clusteredandnonclusteredtable,-1) ') 4 5 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC

The IAM page number is 126 and 93

IAM pages, respectively.: 80, 120, 121, 110, 126, 93

We open the SSMS plug-in for Internals Viewer

Click the PFS button

You can see all the IAM pages are in the mixed zone

In fact, if any one of your IAM page using the Internals Viewer view other database are assigned in the mixing zone

We have also seen on the Internet

Address:

Address:

Why always from the mixing area distribution.? ?

Unified area: all individual objects. All 8 pages only area by the use of an object

The mixed zone: shared by up to 8 objects. Area in the 8 page of the page can be made of different objects all. But a page always can only belong to one object

BecauseIn most casesAn object (a table is generally only a few IAM pages), in this case, if a unified district is wasted

Why most cases? If a table is partitioned, LOB data page, row overflow data pages, there are many IAM pages will, though,

IAM page or from the mixed zone distribution

A detailed look at the article, the number of IAM pages and what the relevant


And use DMV:dm_db_index_physical_stats to check the number of pages in the table, IAM pages are not the results in Statistics

The heaptable table

1 SELECT 2 [database_id], 3 [index_id], 4 [index_type_desc], 5 [index_level], 6 [fragment_count], 7 [page_count], 8 [avg_fragmentation_in_percent], 9 [record_count] 10 from sys.dm_db_index_physical_stats(DB_ID('IAMDB'),object_id('heaptable'),null,null,'detailed')

The clusteredtable table

1 SELECT 2 [database_id], 3 [index_id], 4 [index_type_desc], 5 [index_level], 6 [fragment_count], 7 [page_count], 8 [avg_fragmentation_in_percent], 9 [record_count] 10 from sys.dm_db_index_physical_stats(DB_ID('IAMDB'),object_id('clusteredtable'),null,null,'detailed')

The nonclusteredtable table

1 SELECT 2 [database_id], 3 [index_id], 4 [index_type_desc], 5 [index_level], 6 [fragment_count], 7 [page_count], 8 [avg_fragmentation_in_percent], 9 [record_count] 10 from sys.dm_db_index_physical_stats(DB_ID('IAMDB'),object_id('nonclusteredtable'),null,null,'detailed')

The clusteredandnonclusteredtable table

1 SELECT 2 [database_id], 3 [index_id], 4 [index_type_desc], 5 [index_level], 6 [fragment_count], 7 [page_count], 8 [avg_fragmentation_in_percent], 9 [record_count] 10 from sys.dm_db_index_physical_stats(DB_ID('IAMDB'),object_id('clusteredandnonclusteredtable'),null,null,'detailed')


If have a wrong place, welcome everybody clap brick o(∩_∩)o

Posted by Heidi at November 24, 2013 - 4:55 AM