Post reply

Pagesplits question

  • February 22, 2023 at 11:06 am

    #4152737

    Hi All,

    Does a DELETE operation cause PAGE Split? As far as I know we Page splits occur for only INSERT and UPDATE.

    monitoring for operation : LOP_DELETE_SPLIT

    Thanks,

    Sam

  • February 22, 2023 at 1:17 pm

    #4152771

    Deleting does not directly cause a page split but after a delete there might not be enough space for a new row to be inserted, so it might indirectly cause a page split when new rows are inserted on pages where rows have been deleted.

    LOP_DELETE_SPLIT is the deletion of rows that happens when SQL Server is moving rows from one page to another in a page split.

     

  • February 22, 2023 at 1:42 pm

    #4152803

    Thanks Jonathan. A followup question, why shouldn't care about index fragmentation on indexes which has 1000-5000 page count ? I have read somewhere or some video, i dont remember.  Why we should ignore these indexes? we see a lot of tables accessed very often but getting fragmented very fast now and then but with page counts around 800-900.

  • February 22, 2023 at 6:46 pm

    #4152930

    vsamantha35 wrote:

    Thanks Jonathan. A followup question, why shouldn't care about index fragmentation on indexes which has 1000-5000 page count ? I have read somewhere or some video, i dont remember.  Why we should ignore these indexes? we see a lot of tables accessed very often but getting fragmented very fast now and then but with page counts around 800-900.

    From your comments, it sounds like you may be doing index fragmentation maintenance on a regular basis.  If you are, there's about a 99%+ chance that you're following the supposed "Best Practice" of using RECORGANIZE between 5 ad 30% logical fragmentation and REBUILDs above 30% fragmentation.

    If that's true, I strongly recommend that you stop doing index maintenance at all.  The "Best Practice" index maintenance is not a best practice, was never intended to be a best practice and, for most of your indexes , is actually a WORST PRACTICE.

    If you want "time-well-spent", spend the time on keeping your STATISTICS up to speed, especially if your indexes are based on an IDENTITY column or other "ever-increasing" value such as (but not limited to) a date column.

    Even the guy who came up with those numbers says how he came up with those numbers and why.  See the following article and pay particular attention to the last statement in that article which says, and I quote...

    "In the meantime, take those numbers with a pinch of salt and don’t treat them as absolute."

    https://www.sqlskills.com/blogs/paul/where-do-the-books-online-index-fragmentation-thresholds-come-from/

    I you have any midindex inserts/updates, stop using REORGANIZE on those indexes immediately!  REORGANIZE does NOT work like most people think/say it does (it's correct in the MS documentation but it's worded in such a fashion as to allow people to make up their own ideas about what it does thanks to extreme "Confirmation Bias").

    That also means that about 99% of all blogs, articles, and 'tubes on the subject are actually and seriously incorrect because they don't "get it" either. 😉

    I even offer proof (something that the blogs, articles, and 'tubes never seem to do) in the form of a video that proves that the biggest fragmentation problems with Random GUIDs aren't because they're random in nature.  In fact, they actually work much like everyone in the world expects any index to work.  The thing I prove is that it's the blood "Best Practices" index maintenance methods the most of the world unfortunately believes is a "Best Practice".  I go even deep and prove that the real issue is the use of REORGANZE and how it actually re-configures the data on pages to actually cause and perpetuate massive page splits all day, every day and then what to do about it.

    I'll also tell you that if you're basing your Index Maintenance solely on the percent of logical fragmentation, you're doing it wrong.  The proof starts with Paul Randal's article that I cited above and some articles and 'tubes by major players such as Brent Ozar and Eric Darling.

    Stop doing index maintenance until you know how to do it right and understand that the supposed "Best Practices" that most of the world is using is NOT right.  Spend your time on rebuilding stats until you know a whole lot more about each index and what it needs.

     

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply