• Bhuvnesh (11/20/2012)


    Jeff Moden (11/18/2012)


    make sure that you have a good FILL FACTOR on the index for the GUID.

    Please explain 🙂

    Sure... it's a huge subject with lots of caveats that you need to read about in Books Online but here's a quick overview concerning GUIDs and FILL FACTORs.

    GUIDs in SQL Server are really just huge random numbers (Type 4 GUIDs are used. Type 1 was used in version 7 but were still mostly random in nature). That means that if you have an index with a GUID in it (especially if it's the first column), new data will usually be inserted somewhere in the middle of a page on an index. If the page is full because of a 0 or 100 FILL FACTOR (or other reason), you get a page split on the index. Page splits use a lot of time and a lot of resources and can actually cause some pretty serious "timeouts" on frequently inserted tables. Lookup "page splits" in Books Online for more information.

    Having a lower FILL FACTOR means that some free space is left on every page after and index rebuild or reorg. That free space is used by inserts instead of doing page splits (until the page is filled, of course).

    An 80% FILL FACTOR (for example) will leave approximated 20% free space (depending on row size) on every page after an index rebuild or reorg. The rowsize thing is another good reason to keep columns narrow by using the correct datatype. More rows per page means faster SELECTs.

    Fill factors other than 0 or 100% (0 is technically the same as 100), make SELECTs take longer because you have to read more pages to get the same amount of data so you need to be careful as to which indexes you assign a fill factor to and how big they are. Again, all of that is much better explained in Books Online under "Page Splits".

    And, no... the new SEQUENTIAL GUID datatype won't help for individual inserts. You can read about that in Books Online, as well.

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)