Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Page Splits Expand / Collapse
Author
Message
Posted Monday, November 3, 2008 4:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 23, 2010 6:05 PM
Points: 6, 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.
Post #596196
Posted Monday, November 3, 2008 5:05 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
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
Post #596218
Posted Monday, November 3, 2008 6:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 23, 2010 6:05 PM
Points: 6, 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
Post #596239
Posted Monday, November 3, 2008 6:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #596243
Posted Monday, November 3, 2008 6:59 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
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
Post #596249
Posted Monday, November 3, 2008 7:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 23, 2010 6:05 PM
Points: 6, 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
Post #596254
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse