Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210

    Unanswered: How can I see page split statistics?

    I'm only now discovering DBCC. In particular; I'd like to tune my fill factors on Clustered Tables with the goal of reducing excessive page splits over time yet not allowing so much space that paging space gets wasted.

    Question 1:
    How can I run a statistic on one (or all) tables that gives me the information to do this tuning and do you know a good link/reference to describe it's use?

    For columns that I know will always be populated later, and the size is also known (integers, char(1), etc) I plan to assign initial values where the business logic doesn't need to distinguish between Null and Zero, for example.

    Question 2: Does a reindex (Create Index ... with Drop_Existing ...) reorder these and eliminate splits and fragmentation? If so; then I can be more aggressive with assigning higher numbers on very large tables since I re-index weekly.


    Thanks; I hope I'm not over-using you guys and I appreciate all the help. Hope the questions aren't lame. I do try my own searches but sometimes the voice of experience ... (wading boot alert).

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Vich
    Quote Originally Posted by vich
    Question 2: Does a reindex (Create Index ... with Drop_Existing ...) reorder these and eliminate splits and fragmentation? If so; then I can be more aggressive with assigning higher numbers on very large tables since I re-index weekly.
    Drop .. existing will yes. Be aware though that not all defragmentation processes handle in-page fragmentation. Specifically DBCC INDEXDEFRAG moves pages around but does not move data between pages. Create Index ... with Drop_Existing & DBCC DBREINDEX both recreate the index and so fill all pages up to the fill factor.

    For the 2005 processes (reorganise and rebuild) I know rebuild does the same as the two above. Interestingly, I read something recently that hinted that reorganise (which is the 2005 DBCC INDEXDEFRAG equivalent) does move data between pages too - I really need to follow that one up thouigh if anyone knows please clarify for me.

    I don't know how to get page split info. You could, however, compare results of page density just before reindexing and just after and infer how much page splitting occurred in the interim from the last reindex and adjust your fill factors accordingly. Don't forget this is all a balance - as the fill factor goes up so page splits are less likely but the less efficient selects are.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by pootle flump
    Hi VichDrop .. existing will yes. Be aware though that not all defragmentation processes handle in-page fragmentation. Specifically DBCC INDEXDEFRAG moves pages around but does not move data between pages. Create Index ... with Drop_Existing & DBCC DBREINDEX both recreate the index and so fill all pages up to the fill factor.

    For the 2005 processes (reorganise and rebuild) I know rebuild does the same as the two above. Interestingly, I read something recently that hinted that reorganise (which is the 2005 DBCC INDEXDEFRAG equivalent) does move data between pages too - I really need to follow that one up thouigh if anyone knows please clarify for me.

    I don't know how to get page split info. You could, however, compare results of page density just before reindexing and just after and infer how much page splitting occurred in the interim from the last reindex and adjust your fill factors accordingly. Don't forget this is all a balance - as the fill factor goes up so page splits are less likely but the less efficient selects are.

    HTH
    Thank you for the comprehensive response. Oh boy, lots of new toys (I mean, tools).

    My weekly reindex is via the IE Maintenance Plan on SS 2000 Std. I don't know what it invokes, although maybe a glance at the status report txt file will tell me. If it's the lesser version, (page shuffle only), well, that doesn't sound like much.

    The point was for a clustered table - does it basically lay down a new table?

    Case in point; my main table (tbInvoiceLine) - that's 3.2 million rows and at least half the database's mass. They may add 4000 rows in a day (small potatoes). Also; old rows don't change after a couple of months. So; if I have an 85% fill factor I waste big time. If I make it 98%, then I'll get page splits on some new data (ones that change), but that won't affect reads of old data because by that time, a reindex has fixed the problem.

    Yesterday I changed that from 85% to 96%, and at the same time set initial values for the columns that were $null after a typical Insert but I KNOW will get populated later by the pricing program, invoicing program, BOM Explode program, etc. Related text data is that might be added later are kept in linked tables. It's possible for them to alter a row after Insert, but additional values being added (apart from these mentioned) will be very rare. These Init values aren't wasteful because they are ALWAYS populated on any order that isn't voided.

    After that; I did a "Create Index ... with Drop_Existing ...".

    Sooo; I'm keen to know if I caused page splits or not. I would imagine that I'm fine unless "... with Drop_Existing" isn't respected for Clustered tables.

    Finally; it's STILL a huge mystery why my databse grew from 2GB to 3GB after I first changed that index to Clustered. All I can figure is that my database is set to never contract and the DB Engine built it's working tables there while it did the CREATE. It's more of a curiosity, but I'd love to know what's going on.
    Last edited by vich; 08-08-07 at 06:04. Reason: grammar - proofreading

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is your clustered index key always increasing (e.g. identity) or is it one where inserts might be "in the middle" of the table?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by pootle flump
    Is your clustered index key always increasing (e.g. identity) or is it one where inserts might be "in the middle" of the table?
    Identity key.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In that case you will *never* get a page split on insert. You will only get splits on updates where the row is made larger by an amount that exceeds the available space on the page. You want a very high fill factor and I would not worry much about page splits unless people are regularly adding lots of in page data to existng records.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Beg your pardon - DBCC INDEXDEFRAG and reorganise both do the same - move the leaf level pages about, leaving the page content alone, BUT they do compact the index pages. Interestingly, it says it does this based on the fill factor but this should be irrelevent if PAD_INDEX is not set to on.
    Quote Originally Posted by BoL
    DBCC INDEXDEFRAG defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes, therefore improving index-scanning performance.
    DBCC INDEXDEFRAG also compacts the pages of an index, taking into consideration the fill factor specified when the index was created. Any empty pages created because of this compaction are removed. For more information, see Fill Factor.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by pootle flump
    In that case you will *never* get a page split on insert. You will only get splits on updates where the row is made larger by an amount that exceeds the available space on the page. You want a very high fill factor and I would not worry much about page splits unless people are regularly adding lots of in page data to existng records.
    Not regularly. That was happening before the INIT values. Not much data, but probably 50 bytes; 4 or 5 integers, a couple of decimal (18,2), and a couple of char(1) columns - the bit columns shared bytes with others.

    I think my before/after DBCC CONTIGUOUS showed that CREATE INDEX ... WITH DROP_EXISTING" did get rid of fragments and reduced average page waste (ie: moved rows to other pages).

    The whole concept of "page split" was new to me a few months ago (from this forum). I don't really know the physical or logical layers of a "Balanced Tree", or what the internal sequence of events are for INSERT. I'm a "show me" person and I just don't have the tools where I can see stuff happen a few times. I gather from the absolute "NEVER" that incrementing values are always added at the end (or at least, to new pages being added to the tree).

    I get hung up on how the absolute position (block number) is always known just from the key value. I understand (in rough terms) a tree index, and that finding an element involves reading through the levels and, well, it's not a single absolute address.

    Anyway; that's way off topic to this thread. I merely accept that keyed Clustered Lookup is far faster than keyed Indexed Lookup. I don't know why, but I've seen the query times halve when I make something clustered so it must be true. Sigh. (visual "and they let him vote? No wonder this country ..." lol)

    EDIT: Hold the phone! Are Clusters where the LEAFs are the actual data (as in, interemediate branches are still just key values)? OK; I need to go read a Clusters-101 and not waste your time. Any good link for a SIMPLE explanation (that still cuts it)?
    Last edited by vich; 08-08-07 at 07:12.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What are INIT values?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If you are interested in just how many page splits you are getting, there is a perfmon counter (I forget which category it is in offhand). You can feed this into a database in a Windows 2003 environment (Windows 200 had the option, but it was broken, as I recall), and see what your peak times of the week are.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You got any idea what INIT values are MCrowley? I'm wondering if he means the new defaults he is using....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This thread devolved into a discussion of indexes in SQL Server. It may help a little here:
    http://www.dbforums.com/showthread.php?t=1621095

    This is a much better link:
    http://www.microsoft.com/technet/pro.../ss2kidbp.mspx

    I am guessing he means an initial load of the table, but I am a tad distracted, at the moment.

  13. #13
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    The original question was "how do I monitor page splits?"

    The answer is with Performance Monitor with the SQLServer: Access Methods performance object and the Page Splits/sec counter. I find it odd the verbbose and wise flump did not know this, but I guess this comes down to the difference between knowledge and wisdom.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasymachus
    The original question was "how do I monitor page splits?"

    The answer is with Performance Monitor with the SQLServer: Access Methods performance object and the Page Splits/sec counter. I find it odd the verbbose and wise flump did not know this, but I guess this comes down to the difference between knowledge and wisdom.
    Lol - quite right I'm really behind the class with perfmon.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Thanks. I'm digressing in trying to understand why page splits are bad in the long haul (as in; if they get resolved next week) - and why this baggage that comes with Clustered Indexes is worth it (ie: why are they faster). Too much 2AM thinking aloud - suffice to say they're faster for tables requiring the best direct value lookups.

    By "Init Values" I refer to the initial default values instead of just $null. So they are placeholders assigned at Insert time in order to prevent a page split from a later Update.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •