SQL Server Performance

Page Splits

Discussion in 'Performance Tuning for DBAs' started by HarryArchibald, Jan 15, 2003.

  1. HarryArchibald New Member

    I am evaluating the Entegra audit tool. One of things it shows is that a number of different tables result in Page Splits.

    I realise it is like asking 'How long is a piece of string?', but does anyone have a value for what are excessive Page Splits?

    I would also like to identify what tables are causing it using a trace.
    Has anyone done this?

    TIA
    Harry

  2. bradmcgehee New Member

    When I have watched page splits closely on my production servers, they seem to come in spurts, which seems to make sense. Page splits only occur in those cases when a data or index page with available space is not available. As you know, page splits only occur on tables with a clustered index and during INSERTs or UPDATEs. If a system doesn't have a huge number of INSERTs or UPDATEs, then page splitting is not much of an issue, especially if you have a fill factor of 90 or 95% and regularly rebuild your indexes.

    I have yet to see an authoritative number on what excessive page splits are, and of course, that is what you are asking about. On my production ERP system, that has about 75-100 active users at at time, the page splits on my system average less than one per second, peaking occasionally to over 10. I use Performance Monitor to track them. I would consider this very low. We use a fill factor of 95% and rebuild the indexes each week. I expect this helps to keep our page splits to a minimum.

    And I still haven't answered your question, but if I had to make a guess, I wouldn't worry about page splits until you say them run more than 20 or more a second for a period of time, say more than 5 or ten minutes or more. And even then, if you aren't suffering any I/O or CPU bottlenecks, I doubt even that much would noticably affect performance. Generally speaking, if you have a bad page splitting problem, you will probably also have a bad I/O problem, as the two go hand in hand.

    I am not aware of a way to track page splits to a particular table, but there must be. Anyone with any ideas?

    By the way, what is your level of page splits?


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. HarryArchibald New Member

    Thank you Brad, a good answer as ever.
    I am planning to measure fully the number tomorrow, but currently I'm seeing a similar figure to yours, with a highest figure of 40 or so, also happening in spurts.
    I would take that to meant that it is probably not a problem but I'll measure the figure anyway, as my curiosity is now aroused.



  4. sqljunkie New Member

    How many page splits is excessive is a tough question, but I think you guys are on the right track. The amount of page splits will varying from system to system depending on the amount of active users like Brad mention and also the workload mix. I'm looking at some Perf data from a stress test with 500 active users where there is an average of 100 page splits/sec with spikes up to 800.<br /><br />To answer the question about how you would identify what tables are causing the page splits. The only thing I can think of would be to collect perfmon into a log along with a SQL Profiler trace collecting SQL<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtCompleted with the Starttime and Endtime data columns. You might be able to correlate some page split spikes to particular INSERT or UPDATE statements.
  5. bradmcgehee New Member

    Rortloff, in your example, what was the fill factor used for the tables? Just curious.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  6. sqljunkie New Member

    It was from a test completed last Aug. so I'm not sure what the fill factor was at the time perfmon was collected. I think we settled on 85 as the recommendation for a couple of heavily updated tables and 95 for some of the higher "selected" tables.
  7. bradmcgehee New Member

    With a fillfactor of 85, I am surprised at the high level of page splits. One potential way around this, assuming it is practical, would be to put the clustered index on the primary key. This would place all the INSERTs at the end, preventing page splits. Some people don't like this idea because of the potential for creating a hotspot on the disk, but this rarely a problem in SQL Server 2000, unless your INSERTs are very, very heavy. Only through experimentation would you know if hotspots would become a problem.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  8. JohnBandettini New Member

    Harry

    You can use the DBCC comand SHOWCONTIG to check the effects that page splits are having on your tables. If they show bad fragmentation, page splits being one of the main causes of this, you can rebuild clustered indexes (if present) to reduce the effects. You can also increase the fill factor to reduce splits when you do this

    Regards

    John
  9. HarryArchibald New Member

    John,

    The indexes are rebuilt every night and showcontig shows little fragmentation. My concern is that decreasing teh fill factor will increase access time.
    My conclusion is that page splits are not a problem currently, which what I was trying to find out.
    Cheers,

    Harry
  10. bradmcgehee New Member

    If you are currently having minimal page splits, and if you rebuild your indexes every night, then reducing your current fill factor a little bit at the a time, should not present any problems. Just keep reducing it a little, while watching your page splits. If you see the page splits go up all of a sudden, then you now know that you have gone too far and need to go back a little bit.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page