SQL Server Performance

Identify Page Splits

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by melvinlusk, Jun 10, 2009.

  1. melvinlusk Member

    I have a SQL Server 2005 Enterprise Edition box with SP3.
    I have a high number of page splits occurring, and I'd like to know which tables/indexes have the most splits. I know that fragmentation is caused by page splits, but this isn't always the case.
    I realize that in 2008 I can find this using Extended Events, but upgrading isn't an option at this point.
    Any ideas?
  2. ndinakar Member

    How did you conclude that you have page splits?
  3. melvinlusk Member

    I ran a perfmon trace to poll for Page Splits/Sec.
  4. satya Moderator

    As you know what page split mean, I don't want to cover them and to avoid them the best strategy I follow is to use a low fill factor when you create and index. Unless you are aware of database design and having this low fill factor will cause heavy read operations for those queires, to avoid that you could add relevant indexes.
    Say a varchar type column that is frequently updated to different sizes then perhaps consider putting that in its own table. I suggest to go thru Paul Randal's blog about lot of coverage on thsi subject.
  5. melvinlusk Member

  6. FrankKalis Moderator

    [quote user="melvinlusk"]
    Satya, I think you may have phrased this incorrectly. Having a low fill factor would cause more page splits. I typically use 90.
    http://www.sql-server-performance.com/tips/sql_server_performance_monitor_coutners_p1.aspx
    [/quote]
    There is now much confusion going on.[:)]
    When you read the article you referenced, it states: "If you find out that the number of page splits is high, considerincreasing the fill factor of your indexes. An increased fill factorhelps to reduce page splits because there is more room in data pagesbefore it fills up and a page split has to occur."
    This looks like a typo to me. If you INCREASE the fillfactor, you end up with LESS space on the page. Thus page splits can occur MORE frequently. It may make sense to decrease the fillfactor, but this leaves you with potentially wasted space.
    This one might be interestin:.
    http://sqlblogcasts.com/blogs/tonyr...hat-happens-why-does-it-happen-why-worry.aspx
  7. FrankKalis Moderator

    Sorry forgot to post that "An increased fill factorhelps to reduce page splits because there is more room in data pages" is a contradiction in itself. Can I say so?
  8. melvinlusk Member

    You're right, Frank. I remember now that a lower fill factor can decrease disk I/O, so it makes since that it would decrease page splits.
    Looks like somebody needs to correct that white paper!
  9. mst New Member

    [quote user="melvinlusk"]
    You're right, Frank. I remember now that a lower fill factor can decrease disk I/O, so it makes since that it would decrease page splits.
    Looks like somebody needs to correct that white paper!
    [/quote]
    (Arriving late to the party)
    I could be wrong, but...
    The lower your fill factor the fewer records you will initially have per page. So to get N records you will have to read more pages (assuming of course that you DO pull multiple pages of data at once). I think your performance would then improve as pages (on average) got closer to full... and then start to fall off as more pages are filled and page splits start to happen.
    If you have a regular scheduled job to do rebuild/reorg, I think the optimal set up would be to figure out where that elbow is... you should set fill factor high enough to minimize page reads but small enough (adjusted by table usage as alreadt discussed in this thread) that you don't have a huge number of page splits by the time you do your next rebuild/reorg. The more static a table the higher you should be able to set your fill factor without worrying about page splits
  10. ndinakar Member

    BOL has a misleading description:
    FILLFACTORSets the percentage of free space in the leaf level of each index page during index creation.
    When you set a value for FILLFACTOR, it means you are setting how much % of the page you want filled. The above description says the value you set is the % of free space. Its actually the % of "fullness" you want in the page. If you want your page to be 90% full you set fillfactor to 90, if you want only 70% full, you set it to 70. So its the amount of % you want your page to be full, not free.
    Back to the point, the default value is 100 which means SQL Server will use ALL the space on the page. So unless you are manually changing this value during your index creation script, this is not the cause for your page splits. What can cause page splits is - a heap, heavy deletes, random PK inserts etc.
    Did I cause add to the confusion? [;)]
  11. melvinlusk Member

    Not totally. [:D]
    My default fill factor is 90. I was always told that using 100 was just like using 0.....
  12. FrankKalis Moderator

    <p>[quote user="ndinakar"]</p><p>What can cause page splits is&nbsp; - a heap, heavy deletes, random PK inserts etc. </p><p>Did I cause add to the confusion? <img src="http://sql-server-performance.com/Community/emoticons/emotion-5.gif" alt="Wink"> <br></p><p>[/quote]&nbsp;</p><p>Yes, I think so. [;)]</p><p>Page splits can never happen on a heap, methinks. See this: <a href="http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/21/impact-of-dml-operations-on-a-heap-with-compression-enabled.aspx" target="_blank" mce_href="http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/21/impact-of-dml-operations-on-a-heap-with-compression-enabled.aspx">http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/21/impact-of-dml-operations-on-a-heap-with-compression-enabled.aspx </a><br></p>
  13. ndinakar Member

    <P mce_keep="true">[quote user="FrankKalis"] <P>Yes, I think so. <IMG alt=Wink src="http://sql-server-performance.com/Community/emoticons/emotion-5.gif"></P><P>Page splits can never happen on a heap, methinks. See this: <A href="http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/21/impact-of-dml-operations-on-a-heap-with-compression-enabled.aspx" target=_blank>http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/21/impact-of-dml-operations-on-a-heap-with-compression-enabled.aspx </A><BR></P><P>[/quote]</P><P mce_keep="true">&nbsp;</P><P>you are right.. in case of heaps sql server just stuffs the data wherever there is space and uses forwarding to identify the next row.. thanks for correcting. </P>

Share This Page