In continuation with previous post,
Page Split -- A page is 8kb of data whether Index data, table data or LOB data. If new row could not fit into the page, then new page created and moves rows to the right or left of modifications on to new page. This is known as page split. For example we have a series of rows from 1 to 10 (except 4th row) in two pages 21, 22 that means we have 1, 2,3,5,6 in first page and 7,8,9,10 in second page. And assume we have a clustered index on that column. Then when a new row comes example ID-4 then it should keep after 3 in page 21 because it should be physically in that order in that case two pages becomes three pages as below
Page 21 consists 1,2,3,4 and It creates a new page (page 33) to store 5 and 6 and then page 22 consists 7, 8,9,10. This is known as page split. When there is page splits then all non-clustered indexes needs to be updated. The more page splits causes fragmentation and impacts performance.
Fill Factor -- In the above topic we discussed about page splits, A page split happen when there is no room for newly coming row then SQL Server will move rows into other page and creates a new page to fit the newly inserted data, To avoid this situation to some extent we need to specify Fill Factor, If you specify fill factor to 80% then SQL Server will fills only 80% of data and leaves 20% free, this will help to reduce the page splits. We need to specify fill factor percentage based on your table design and environment.
IAM Pages -- As discussed earlier the SQL Server will create one partition for every table and every index. Inside that partition we have three allocation units IN_ROW, ROW_OVERFLOW, LOB_DATA. For each allocation unit SQL Server will creates IAM Pages. This IAM Page will keep tracks of all data pages belongs to particular table. In the result of DBCC IND we have columns like PagePID, IAMPID and PageType etc., PagePID is unique (within the database file), IAMPID defines a parent/child relationship between pages and PageType defines type of page
Type 1 is Data Page
2 is Index Page
3 and 4 are Text Pages
8 is GAM Page
9 is SGAM Page
10 is IAM Page
11 is PFS Page
In our first part if we run the DBCC IND we got result as NULL for first two columns IAMFID, IAMPID and value for PagePID. That row indicates IAM Page (if you observe page type then it shows as IAM Page). From second rows onwards of that result IAMPID hold the first row pagePID and keep tracks of all pages information of that particular table. As discussed and single GAM Page can hold extents information of a 4GB space in data file, In similar to that IAM page also holds 4 GB of information about allocation units (pages / extents) for an object. If it gets filled then another IAM page will be created for the same allocation unit, see the below picture for detailed explanation (Table Organization picture).
No comments: