Page split with Clustered index
-
I am testing Page Split with Clustered Index and Non-clustered Index.
I have created Single Clustered Index on a table and Inserted incremental values in the table. I do have others columns also but only one clustered index.
My question is still I am getting page splits. What is the reason for the page split if I am inserting always incremental values.
Another test I did with single non-clustered index on the same key and inserted incremental values. But I found less page split here.
Please clear this doubt as why there is page split if I have inserted data in incremental order.
-
How are you measuring whether or not you're getting page splits?
-
I am using perfmon to track Page Split/Sec event
-
page split will happen when the page is full and also you want to insert data continually.
can set the index fill factor less than 100 to keep some space for inserted data.:-P
-
RakeshRSingh (7/13/2010)
I am using perfmon to track Page Split/Sec eventThe problem with page split counters is that they will fire whenever a new page is allocated, and not just when a page splits and data is moved. This is likely to be what you are seeing.
-
So How can I get a perfect picture as when the page Split is occuring.
-
Thanks Jim for the Reply.
Does the Page Split counter misguiding.. How can I get the page split then for a clustered index entry.
-
RakeshRSingh (7/13/2010)
I am testing Page Split with Clustered Index and Non-clustered Index.I have created Single Clustered Index on a table and Inserted incremental values in the table. I do have others columns also but only one clustered index.
My question is still I am getting page splits. What is the reason for the page split if I am inserting always incremental values.
Another test I did with single non-clustered index on the same key and inserted incremental values. But I found less page split here.
Please clear this doubt as why there is page split if I have inserted data in incremental order.
Any chance of you posting the table creation code, the index code, and your inserts? It's the only way someone is going to be able to help you on this one.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
changbluesky (7/14/2010)
page split will happen when the page is full and also you want to insert data continually.Not if the data is inserted in the same order as the clustered index.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff, Here is the script I am running and using perfmon I am measuring page splits/sec.
CREATE TABLE [dbo].[Tab1](
[ORG_KEY] [bigint],
[PROD_KEY] [bigint],
[TIME_KEY] [bigint],
[CST_NON] [float],
[CST_RPL] [float],
[RTL_NON] [float],
[RTL_RPL] [float],
[UNT_NON] [float],
[UNT_RPL] [float],
[UPDATE_DATE] [datetime]
)
with all the columns allows null
CREATE UNIQUE CLUSTERED INDEX [IX_ORGKEY] ON [dbo].[Tab1]
(
[ORG_KEY] ASC
) with all the default value and fill factor of 100.
DECLARE @cnt INT
SET @cnt = 1
WHILE @cnt <= 100000
BEGIN
INSERT INTO Tab1 VALUES(@cnt,19280,10255,250,500,350,450,375,275,GETDATE())
SET @cnt = @cnt + 1
END
-
Dear Experts, I want to know why perfmon is showing page split while I am inserting incremental values for Clustered Index
Thanks in advance.
-
I took a look at the code you posted and see nothing obvious for why you should be getting such page split indications. I won't be anywhere near a machine with SQL Server on it until Moday. I can do a deeper dive on it then but, hopefully, someone will pick up on this in the meantime.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
you could be seeing a few page splits due to the index maintenance. All indexes can incur splits as they grow.
I suggest that after running this tsql code to load your table that you run DBCC SHOWCONTIG('tab1') and show the results here. my guess is that it will show no evidence of fragmentation in your table.
The probability of survival is inversely proportional to the angle of arrival.
-
I am leaning towards what sturner has written.
Your data pages should not be splitting if you are always adding to the end of the row. However the intermediate pages in the index could potentially be splitting to handle overflow as new ones are needed.
http://www.sqlservercentral.com/articles/Indexing/68563/
Are you seeing lots of page splits? If you run the code, how high does the page split/sec counter get? I wouldn't expect many.
-
Using DBCC LOG ('SplitPageTest', 4), I've tracked the entries in the log for the example given in your sample code, and a slightly modified version that uses a UNIQUEIDENTIFIER as the clustered key, inserting as newid().
Looking at the transaction log for the bigint clustered key, for the insert for Transaction ID 0000:00001ed3, we can see that a page split occurs (Transaction 1ed4) and then our insert occurs when the TransactionID goes back to 1ed3. Take note of the Log Record Length - it's fairly small.
With the UNIQUEIDENTIFIER, we can see Transaction 1910b immediately splitting (Transaction 1910c), but the split operation is a bit more work - look at the Log Record Length of 4088 in the middle there, followed by the LOP_DELETE_SPLIT. This operation was not seen in the previous example, and is related to the page split moving half the rows off the original page.
It's worth noting that the Page Splits/sec counter and the Allocations/sec counter stayed locked with each other as I ran the two tests. Test #2 had double the amount of page splits occurring, which makes sense due to the random insert pattern, whereas Test #1 only called a "page split" when it ran out of room on the page. Test #1 gave me about 20 "splits" per second, whereas Test #2 was anywhere from 40-60 per second.
My screenshots are with SQL Server 2005, but the Allocations/sec and Page Splits/sec counters stay locked in 2008 too.
Now, if we bump the row size up to over 7000, we can see a page split/allocation operation occurring after every insert, which disproves sturner's idea of clustered index maintenance in the upper levels.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply