dcsimg
Results 1 to 14 of 14

Thread: [RESOLVED] Do SQL row updates cause fragmentation?

  1. #1

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    896

    Resolved [RESOLVED] Do SQL row updates cause fragmentation?

    I understand that rows of data are stored in 8K pages and that deleting and inserting can cause fragmentation, but what about updates? In this case my table columns are all normal types like VarChar, Char, Int, Decimal, DateTime, Date, Money, and bit. If I understand it correctly, if I insert a row where only one of the columns is populated, E.G. the PK as an integer and all the rest null, then all the dimensioned space it will ever need is allocated. Therefore if I populate all the columns of that row with data, it will not need to be moved and will not cause fragmentation or any refactoring of the PK index. Is that correct?

  2. #2
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,153

    Re: Do SQL row updates cause fragmentation?

    That depends upon the database and how you are defining fragmentation.

  3. #3

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    896

    Re: Do SQL row updates cause fragmentation?

    Sorry. I should have said Azure SQL Server.

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,300

    Re: Do SQL row updates cause fragmentation?

    I'm not 100% sure but I believe the only way an update would cause fragmentation would be if you updated the primary key or other index.

    Edit> I took a look here. In particular, look at the External Fragmentation section. It implies that updates can cause fragmentation but doesn't really explain why.
    Last edited by FunkyDexter; Aug 14th, 2019 at 03:48 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  5. #5
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,285

    Re: Do SQL row updates cause fragmentation?

    Quote Originally Posted by FunkyDexter View Post
    I'm not 100% sure but I believe the only way an update would cause fragmentation would be if you updated the primary key or other index.

    Edit> I took a look here. In particular, look at the External Fragmentation section. It implies that updates can cause fragmentation but doesn't really explain why.
    I'm definitely not a DB guru and I don't know about Azure but in MS SQL an update can be turned into an insert and a delete depending, I believe, if you are changing indexes or not. And maybe other conditions also.
    Please remember next time...elections matter!

  6. #6
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,326

    Re: Do SQL row updates cause fragmentation?

    Yes it will.... fragmentation can be caused if you update/add data for the row that will extend it (increase the size)…. It could case fragmentation of an index if you change the value you are indexed on also
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  7. #7

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    896

    Re: Do SQL row updates cause fragmentation?

    In this case I would not be changing the index.

    Gary your comment about a row increasing in size is exactly the heart of what I'm trying to discover. Does it really increase in size?
    Long ago I learned that one must specifically limit the size of a column, so that the system could dimension a slot in storage for that. And that we should make the data type just wide enough to the widest value. Let's say I have a integer and I know it will never be more than about a million. The 2 byte SMALLINT is too small, so I should use the 4 byte INT type. But when I have a department code and I don't need more than a doze ever, I can use the single byte TINYINT.
    I appreciate that strings are a different kettle of fish. An INT will always be 4 bytes regardless how how large the numeric value. But variable length strings I thought had a maximum width parameter so that when the row is created, the system would allocate that block of bytes to the column's future value and one would not need to move it later when one put data in there. As I understand it, the rows are stored in the 8k page nuts to butts. If it has an integer PK then the first 4 bytes are for it. If the next is a VARCHAR(8) then the next 8 bytes are reserved for it. If the next column is a TINYINT, then the next byte is set aside for it.
    If I am correct, then there should never be a need to move the location of that row in memory no mater how I change the data. Am I correct?

  8. #8
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,285

    Re: Do SQL row updates cause fragmentation?

    Quote Originally Posted by cory_jackson View Post
    In this case I would not be changing the index.

    Gary your comment about a row increasing in size is exactly the heart of what I'm trying to discover. Does it really increase in size?
    Long ago I learned that one must specifically limit the size of a column, so that the system could dimension a slot in storage for that. And that we should make the data type just wide enough to the widest value. Let's say I have a integer and I know it will never be more than about a million. The 2 byte SMALLINT is too small, so I should use the 4 byte INT type. But when I have a department code and I don't need more than a doze ever, I can use the single byte TINYINT.
    I appreciate that strings are a different kettle of fish. An INT will always be 4 bytes regardless how how large the numeric value. But variable length strings I thought had a maximum width parameter so that when the row is created, the system would allocate that block of bytes to the column's future value and one would not need to move it later when one put data in there. As I understand it, the rows are stored in the 8k page nuts to butts. If it has an integer PK then the first 4 bytes are for it. If the next is a VARCHAR(8) then the next 8 bytes are reserved for it. If the next column is a TINYINT, then the next byte is set aside for it.
    If I am correct, then there should never be a need to move the location of that row in memory no mater how I change the data. Am I correct?
    Well following that..the MS SQL declare Varchar(max) would reserve 65,535 characters for future use? Doesn't seem likely. Maybe I didn't follow you correctly. It also doesn't seem to fit with the use of NULLs. I thought a single bit indicated a NULL value. If it was Char(8) and NULL are you thinking it would still fill up the eight bytes?
    Please remember next time...elections matter!

  9. #9

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    896

    Re: Do SQL row updates cause fragmentation?

    As I understand it MAX is a different animal. I believe that this data is not stored in the same physical location and that VarChar(max) is a fixed width which is to hold the address I think. But i haven't researched that much.
    I'm reading all over and I can't seem to get a consistent answer. Some say one way, some say another. All I want to know is if I have a row with a VARCHAR NULL and I set a value to it later, does this cause the row to be moved and cause fragmentation?
    Moving forward from this I was wondering if I should use fixed width fields of type CHAR so that this doesn't happen. Because I do many row updates.

  10. #10

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    896

    Re: Do SQL row updates cause fragmentation?

    I've found two articles that have me convinced that memory is not allocated for future data in VARCHAR. And nullable adds to the width. So it seems to me that populating a row with many VARCHAR will cause the row to be moved and cause fragmentation. But I also discovered that using a fixed data width like CHAR isn't really better because although it wouldn't cause a relocation, the sheer increase of size in the table means data scans take much longer. So I suppose i just need to come up with a plan to detect high fragmentation and rebuild the indexes when it happens.
    I wish there was a way to tell the server "When you have idle time, check the indexes and if they're fragments much, defrag them." It just seems like this should be something the server maintains for us. Or have some kind of auto-defragging index.

    Anyway, Thanks everyone for your help.

  11. #11
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,326

    Re: [RESOLVED] Do SQL row updates cause fragmentation?

    NO Varchar(max) does not reserve size it just say it can grow to that size. Both varchar and nvarchar types use only the amount of storage required. As to sizing a row in SQL can hold 8K of space (used storage space) so say you have use 4000 of the 8000 in the initial sizing of the row, it fits on one page and has another 4000 space for more rows... say another row in the table needs 3600 of the 8000 now we have 2 rows in the page filled to 7600 bytes.... Now we edit a varchar field and add another 500 bytes of space on the 4000 byte row. We are now over 8000 and the row has to move.... FRAGMENTATOIN..... the row needs to migrate to another page and the indexes get fragments


    Cory I would not change the type to CHAR (or NCHAR) as that will force the size each time and can cause growth issues later
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  12. #12
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,381

    Re: Do SQL row updates cause fragmentation?

    Quote Originally Posted by cory_jackson View Post
    So I suppose i just need to come up with a plan to detect high fragmentation and rebuild the indexes when it happens.
    I wish there was a way to tell the server "When you have idle time, check the indexes and if they're fragments much, defrag them." It just seems like this should be something the server maintains for us. Or have some kind of auto-defragging index.

    Anyway, Thanks everyone for your help.
    That's what a DB Maintenance Plan is for... You can set one up to do deframentation, backups, re-indexing, what ever at regular intervals.

    By having one in place, you can set it to run during down times, such as overnight or on the weekends, say once a month, or every other week, so that your fragmentation doesn't get high enough to become a problem.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  13. #13
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,847

    Re: [RESOLVED] Do SQL row updates cause fragmentation?

    Updates will always attempt to use the location of the original row if it will fit. If the row needs to be moved there is a good chance a pointer to the new location will be slotted into the original location. Remember that we have existing indexes that POINT to that "old" location for the row.

    Fragmentation of data only matters if you are attacking the table WITHOUT using an index, anyway. If you use an index, then the I/O hits are specific and the fragmentation has little effect on speed. If you are doing whole table access - table scans, then all 8K pages are loaded in a disk-physical order and consumed based on pointers in the bucket.

    If you have even one NULL column then a bit-structure is added to each row - one bit FOR EVERY column. This bit-structure is referred to for each row read so that NULL'ness of a column can be determined.

    I saw some other topics in this thread - here's some info on those...

    Any column that is 8000 bytes (or 4000 bytes if n'var) can be stored on the 8K page. Any column MAX or LOB columns will use 16 byte pointers to other disk pages where that data can be stored.

    Old doc - but most likely still relevant.

    https://docs.microsoft.com/en-us/pre...087(v=sql.105)

    Kalen Delaney - one of my DB heros. I have her "Inside MS SQL Server 2000" book - was a great read.

    https://www.sqlserverinternals.com/

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  14. #14
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,847

    Re: [RESOLVED] Do SQL row updates cause fragmentation?

    I should add to what I just said. Since the MAX and LOB columns are already stored off row, they do not come into play while considering if the data row will still fit in it's original location. If some VARCHAR field is changed from ABC to XYZ, the row will still fit. If some MAX field is changed - one that is already off row, then it won't cause the row to be moved - since it's not on the 8K page anyway...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width