1

Simple table with an identity table as a clustered primary key. We unexpectedly hit the max value for the column, so I requested our DBA re-seed the table back to 0 to give us some time to examine updating the column. The table is also purged based on time, so we have a substantial gap in Id's to use up before we collide with anything. Since this is a high insert table, the DBA brought up the potential of page splits since we are no longer inserting at the end of the table.

My question is, how will SQL Server behave when the value of the identity column wraps around? Will inserting into gaps cause a performance issue? I want to consider maybe creating a sequence that will automatically cycle rather that up the id column to bigint.

  • 3
    Consider starting at a negative number instead of zero. I don't see how page splits are relevant here though. The inserts are still sequential, so you aren't squeezing a row between two rows unless you are using IDENTITY_INSERT to manipulate the manipulate the value. – Tara Kizer Oct 9 '18 at 21:36
6

My question is, how will SQL Server behave when the value of the identity column wraps around?

Assuming the index is 100% full the first few inserts at the new key value will cause one or two page splits, where half the rows on the full page are copied to the new page, and then the new row will be inserted into one of the half-full pages.

However after that you resume low-cost inserts and page allocations. When the page fills up a new, empty page is allocated and inserted into the doubly-linked list of leaf pages. No rows are copied to the new page, and the new row is written to the new page.

The only difference between this and an end-of-index insert is the presence of a next page after the new page. And the next page must be updated with a reverse page pointer to the new page.

See this post for details: Good Page Splits and Sequential GUID Key Generation

Will inserting into gaps cause a performance issue?

No.

As SQL Continues inserting into the "middle" of the index, when the target page for an insert is full, SQL will always allocate a new page. It won't put the row on the next existing page, even if that page is not full. The non-leaf levels of the clustered index store the starting value for each leaf-level page, so SQL can't insert the new row onto the next existing page without rewriting the non-leaf level of the index.

You can see this behavior easily by forcing page locks and looking at the lock footprint of your inserts (or less easily with DBCC PAGE). In the example below a clustered index table that can fit exactly 4 rows on a page take a series of mid-index inserts.

Assume every page can hold 4 rows. You have full page existing rows with ascending key values from 10-20, and now you want to insert key values 1-9.

use master
drop database ps_test
go
create database ps_test
go
use ps_test 
go
drop table if exists ps_test
set nocount on


go

--8,060=4*(4+2011)
create table ps_test(id int primary key, data char(2011) not null default '')

go
DBCC TRACEON(3604) 
go

insert into ps_test(id) values (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21)--,22

go

dbcc ind('ps_test','ps_test',1)
--three data pages
/*

PageFID PagePID         PageType
------- ------- / /     --------
1       336             10      
1       328             1       
1       329             2       
1       330             1       
1       331             1       
*/


insert into ps_test(id) values (1)

dbcc ind('ps_test','ps_test',1)
--four data pages 332 has been inserted between 328 and 330
/*

PageFID PagePID        PageType PrevPagePID NextPagePID 
------- ---------/ /   -------- ----------- ----------- 
1       336            10       0           0           
1       328            1        0           332         
1       329            2        0           0           
1       330            1        332         331         
1       331            1        330         0           
1       332            1        328         330         
*/

dbcc page(ps_test,1,328,3)
-- 328 has rows 1,10 and 4048 bytes free
-- the page is split
/*
PAGE: (1:328)
...
pminlen = 2019                      m_slotCnt = 2                       m_freeCnt = 4048
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 1                              
...
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 10                             

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/

dbcc page(ps_test,1,332,3)
--page 332 has 11,12,13 and 2024 bytes free
/*

PAGE: (1:332)
...
pminlen = 2019                      m_slotCnt = 3                       m_freeCnt = 2024
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 11                             
...
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 12                             
...
Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 13                             
...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/

insert into ps_test(id) values (2)
insert into ps_test(id) values (3)

dbcc ind('ps_test','ps_test',1)
--no page split.  
/*

PageFID PagePID     PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- -------- ---------- ----------- ----------- ----------- -----------
1       336         10       NULL       0           0           0           0
1       328         1        0          1           332         0           0
1       329         2        1          0           0           0           0
1       330         1        0          1           331         1           332
1       331         1        0          0           0           1           330
1       332         1        0          1           330         1           328

the new rows went on page 328, which now has

pminlen = 2019                      m_slotCnt = 4                       m_freeCnt = 2024
*/

So now we have page 328 is full and the next page 332 has only 3 rows. But the lowest value on 332 is 11. So when we insert the value 4, does go on 332, which will cause another bad page split when 5 is inserted?

insert into ps_test(id) values (4)
dbcc ind('ps_test','ps_test',1)

/*
A new page 333 has been inserted in the list between 328 and 332

PageFID PagePID     PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- -------- ---------- ----------- ----------- ----------- -----------
1       336         10       NULL       0           0           0           0
1       328         1        0          1           333         0           0
1       329         2        1          0           0           0           0
1       330         1        0          1           331         1           332
1       331         1        0          0           0           1           330
1       332         1        0          1           330         1           333
1       333         1        0          1           332         1           328

*/

dbcc page(ps_test,1,333,3)

/*

PAGE: (1:333)
...
pminlen = 2019                      m_slotCnt = 1                       m_freeCnt = 6072
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 10                             
...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

*/

So another "bad page split", but this one only moved the row with id=10. And leaves page 328 full with values 1,2,3,4

dbcc page(ps_test,1,328,3)
/*
PAGE: (1:328)
...
pminlen = 2019                      m_slotCnt = 4                       m_freeCnt = 0
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 1  
...
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 2 
...
Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 3  
...
Slot 3 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 4                              
...                        
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

*/

Now all the larger key values are on seperate pages, and from here on out there are no more bad page splits. The next insert requires a new page, and only the new row is on the new page:

    insert into ps_test(id) values (5)
    dbcc ind('ps_test','ps_test',1)

/*

PageFID PagePID      PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- -----------  -------- ---------- ----------- ----------- ----------- -----------
1       336          10       NULL       0           0           0           0
1       328          1        0          1           334         0           0
1       329          2        1          0           0           0           0
1       330          1        0          1           331         1           332
1       331          1        0          0           0           1           330
1       332          1        0          1           330         1           333
1       333          1        0          1           332         1           334
1       334          1        0          1           333         1           328

*/

The new page 334, has only the last-inserted row.

 dbcc page(ps_test,1,334,3)
/*
PAGE: (1:334)
...
pminlen = 2019                      m_slotCnt = 1                       m_freeCnt = 6072
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 5     
...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
id = 5                              


*/

So after a couple of initial "bad page splits" that required moving rows, subsequently new pages are allocated and spliced into the middle of doubly-linked list of leaf pages. The new pages are allocated when a new row wont fit on the previous page and no rows are migrated to the new page.

  • Consider this example: Assume every page can hold 4 rows. You have full page existing rows with ascending key values from 10-20, and now you want to insert key values 1-9. As you correctly stated, the first page that holds key values 10-13 has to be split for inserting key value 1. Now, you have the original page with key values 1,10,11, and a new page (at the 'end') with key values 12 and 13. Next, The insert for key value 2 will go into the first page with no split as you said, but the next value, 3, will split that page again, and so on. New pages are only allocated at the 'end' AFAIK. – SQLRaptor Oct 10 '18 at 17:43
  • Good point, but that's still just a problem initially. After the insert of 3 the pages will be (1,2,3), (10,11), (12,13), and from there on new, empty pages will be inserted between (1,2,3) and (10,11). There really is no such thing as the "end". New pages are added on an existing extent, or a new extent is added somewhere on the filegroup, and the new pages are linked into the index structures with page pointers. – David Browne - Microsoft Oct 10 '18 at 18:00
  • The problem is not going away after 3 pages. Now you have (1,2,3)<->(10,11)<->(12,13). the <-> are the chain links. now you insert 4, and get (1,2,3,4)<->(10,11)<->(12,13). the insert for 5 and 6 go into second page with no split - (1,2,3,4)<->(5,6,10,11)<->(12,13), but the 7 will split again to (1,2,3,4)<->(5,6,7) <->(10,11)<->(12,13). It's a logical problem that you are trying to fill in internal 'void' in the key values, and can't pre-allocate the space for it, it will keep allocating it and splitting over and over. The only way around it I can think of is the one I suggested below. – SQLRaptor Oct 10 '18 at 18:17
  • Here is a visual representation: Assume you are in standing in front of a mountain (existing range of full pages). You need to lay 1 mile of railroad that must come before the mountain (new range). If you try to move forward, you will have to carve the mountain piece by piece, and move it to the end (page splits). no matter what, the whole mountain will have to move, probably several times. On the other hand, you can just turn 180 and build AWAY from the mountain, laying your railroad in reverse order into the open space. :-) – SQLRaptor Oct 10 '18 at 18:26
  • 2
    I did a blog post on something similar to this a while back (moving the identity into negative values). I recommend taking a look, not because it adds much new information but I did a couple of good visuals on what you might expect to happen, and what actually happened. sqlstudies.com/2016/09/21/… – Kenneth Fisher Oct 11 '18 at 18:23
-1

Your DBA is correct. Once you reset the clustering key value to cycle over and become smaller than the existing minimal value, all inserts will need to go to the first page of the index. Since new EMPTY page allocations only happen at the end of the index, when it will fill up, you will get a split for that, and practically for every page written. Depending on your disk subsystem performance and capacity, and a variety of other factors, this may or may not be an operational challenge.

I don't really see a way around it, except for dropping and recreating the index in DESC order, and using a negative sequence increment to go from the high boundary of your gap, and count backwards towards 0 and beyond.

  • "New page allocations only happen at the end of the index" - disagree. Pages are created in the middle or at the end, wherever required. – Michael Green Oct 10 '18 at 17:33
  • when a page is allocated in the middle, that's a page split... @MichaelGreen – SQLRaptor Oct 10 '18 at 17:46
  • Added the word "EMPTY" to clarify the meaning, as a page split is also a new page allocation – SQLRaptor Oct 10 '18 at 18:47
  • Still disagree. A page splits because there is insufficient space to hold a new row. Half the rows (approx.) remain on the existing page. Half go to the new page. This is the same whether the existing page is in the middle of the key sequence or at the end. – Michael Green Oct 11 '18 at 0:27
  • I have a feeling we are on the same page, just missing each other's point :-) I'm not talking about the end of the sequence on the page, i'm talking about the last page of the index which is the only place empty pages are allocated for new rows with new highest key value. If a new row does not have the highest key value, and there is no room, you will get a page split, exactly as you described it. This is not the same as a fresh last page which will have nothing copied to it except that new row. This is expensive, and can be avoided. See the discussion with David in the comments above. – SQLRaptor Oct 11 '18 at 0:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.