• george_at_sql - Tuesday, June 19, 2018 9:15 PM

    Jeff Moden - Tuesday, June 19, 2018 3:03 PM

    Heh.... "Updated_By".  One of the most commonly overlooked reasons for massive fragmentation both physical and logical.

    could you please explain a bit more how that occurs.

    When you "update" a table with a varchar or nvarchar column getting changed in such a way as to increase the length, the database has to shift the data to accommodate the larger value size.  That could mean that you run out of available space on a "page" within the database file, and thus be forced to split some of the data for the row between database pages, and thus a "page split" occurs, which is the cause of what Jeff is talking about.   He'll refer to any such update as an "expansive update".   Just know that page splits can be bad, especially when they start becoming a regular event.   The reason people overlook it is because they assume that just updating such a limited size column can't cause a problem, and that's just not the case.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)