SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Page Splits


Page Splits

Author
Message
mark.hall
mark.hall
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 18
I've got an SQL server where my avg. page splits per second is 3-4. However there is process that runs twice a day when this process is running my page splits go to 20-25.
My question: Is this a high number for page splits or not.
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2894 Visits: 1807
There is no hard and fast answer to whether it is problemattic or not. It really depends a lot on what the impact of the page splitting is. If it creates waits or blocking/locking delays in your database then it may be problemattic. Unfortunately, finding out which specific object/index is getting the page split is not so intuitive. You can find the count of splits by object for the active portion of the transaction log with fn_dblog()

SQL 2005

select parsename([AllocUnitName], 3) [Schema],
parsename([AllocUnitName], 2) [TableName],
parsename([AllocUnitName], 1) [Index],
count([Current LSN])
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
group by parsename([AllocUnitName], 3), parsename([AllocUnitName], 2), parsename([AllocUnitName], 1)



SQL 2000

-- http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2004/11/27/37.aspx
select [Object Name], [Index Name], count([Current LSN])
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
group by [Object Name], [Index Name]



The examples above are based on Greg Linwoods blog post whose address is the comment on the SQL 2000 version. I just changed it to work for SQL 2005.

Somethings to consider when looking at page splits:

1. How many/how often are they occuring?
2. What is the actual impact to the system when they occur?
3. What is the residual impact to the system when they occur? (index fragmentation)

Once you consider the above, the way you go about fixing it is to change the fillfactor on the index in question and leave more freespace at the leaf level by rebuilding the index with the new fillfactor.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
mark.hall
mark.hall
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 18
Thanks, Jonathan

Here's the data I'm getting back.
What I'm wondering is what does it mean when theres a "(0)" in the index name column.

dbo.GroupAttribs^BODY^BOM (1074102867) (0) 69
dbo.GroupAttribs^BODY^PRODUCTION_FAULTS (0) (0) 51
dbo.GroupAttribs^BODY^TRACK_POINTS (1554(0) (0) 32
dbo.GroupAttribs^ORDER^BROADCAST_STATUS_(0) (0) 4
dbo.Items (2089058478) (0) 5
dbo.TrkAttributes (2105058535) (0) 274
dbo.TrkAttributes (2105058535) IX_TrkAttributes (2) 147
dbo.GroupAttribs^BODY^BOM (1074102867) PIX_GroupAttribs^BODY^BOM (1) 1
dbo.GroupAttribs^BODY^TRACK_POINTS (1554PIX_Gro PIX_GroupAttribs^BODY^TRACK_POINTS (1) 1
dbo.GroupAttribs^ORDER^BOM (1650104919) PIX_GroupAttribs^ORDER^BOM (1) 4
dbo.GroupAttribs^BODY^BOM (1074102867) PK_GroupAttribs^BODY^BOM (2) 1
dbo.GroupAttribs^BODY^PRODUCTION_FAULTS PK_GroupAttr PK_GroupAttribs^BODY^PRODUCTION_FAULTS (2) 2
dbo.GroupAttribs^ORDER^BOM (1650104919) PK_GroupAttribs^ORDER^BOM (2) 1
dbo.TrkAttributes (2105058535) PK_TrkAttributes (1) 2
dbo.GroupAttribs^BODY^BOM (1074102867) UIX_GroupAttribs^BODY^BOM (4) 48
dbo.GroupAttribs^BODY^PRODUCTION_FAULTS UIX_GroupAtt UIX_GroupAttribs^BODY^PRODUCTION_FAULTS (3) 18
dbo.GroupAttribs^BODY^TRACK_POINTS (1554UIX_Gro UIX_GroupAttribs^BODY^TRACK_POINTS (3) 18
dbo.GroupAttribs^ORDER^BOM (1650104919) UIX_GroupAttribs^ORDER^BOM (4) 6
dbo.GroupAttribs^ORDER^BROADCAST_STATUS_UIX_GroupAttribs^ORD UIX_GroupAttribs^ORDER^BROADCAST_STATUS_HISTORY (3) 5
dbo.GroupAttribs^BODY^BOM (1074102867) UIX_PART_NUMBER (3) 49
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86117 Visits: 41096
mark.hall (11/3/2008)
I've got an SQL server where my avg. page splits per second is 3-4. However there is process that runs twice a day when this process is running my page splits go to 20-25.
My question: Is this a high number for page splits or not.


I'd say "Yes" especially if it's on a single table. It could indicate that you've selected the wrong clustered index for an OLTP table. It may also indicate that you should be feeding a staging table that will be used to update a larger storage table later. Also, you may be able to reduce the number of page splits by reducing the FILL FACTOR on your clustered index to something in the range of 60-70 depending on the size of the rows in the table.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2894 Visits: 1807
mark.hall (11/3/2008)
Thanks, Jonathan

Here's the data I'm getting back.
What I'm wondering is what does it mean when theres a "(0)" in the index name column.


Mark, from what I can tell in a SQL 2000 VM, it is the clustered index. I base this on the value of the Context column returned by the TVF in a SELECT * query. Lowering your fill factor would probably not be a bad idea on the high splitting indexes to allow more leaf level space to exist in the index. Be careful how far you go with this, as this will cause you to have to do more reads from the database to get the same amount of data since the pages maintain freespace in them to prevent splitting.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
mark.hall
mark.hall
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 18
Thanks Jeff/Jonathan,

I won't get a change to try the changes till next week.
But, I'll let you know the results.

Thanks again for all your help.

Mark
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search