Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40

    Unanswered: Indexes update when table is modified

    Hello again,

    Two Short questions this time,

    I have a table with several indexes, currently most of them are very narrow (one column), and the question is, when I modify the table by updating a record, does all the indexes are calculated again?? Even if the modified field isn't indexed??? Or the server is smart and knows what indexes to calculate if any.

    Second question, can I give to a query a low priority(In dynamic SQL), for example when I don't want my query to exploit too many system resources so it won't interfere the main system ?


    Inon.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    update only affects the modified data

    second one I have no idea what a priority setting is....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can't normally set the scheduler priority within SQL Server, because the timeslice manager affects so many other things if it gets even a little bit confused. A given spid can elect for lower priority treatment by setting its DEADLOCK_PRIORITY to LOW.

    -PatP

  4. #4
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40
    Quote Originally Posted by Brett Kaiser
    update only affects the modified data
    Ok, just allow me to be sure, if I have a table with 10 indexes and I modify a field in some record which is not indexed, none of the indexed are recalculated right?

    I'm asking because I read something that made me unsure about this process, see this link: http://www.sql-server-performance.com/q&a59.asp
    Look at the part where he writes: "For every data modification you have, each index in your table needs to be updated".

    Thanks,

    Inon.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, somebody else can correct me if I'm wrong, but SQL Server does not strictly "update" a record. It copies the record with the modifications and then deletes the original. And it has to copy the entire page of records. That entails suffling of the clustered index, and for non-clustered indexes all the references have to be updated.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    To be honest I have to get Kalen's book

    BUT copying the entire page for an update?

    Sounds like a lot of IO and overhead...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    There is no deletion-insertion going on. An update of "circus" to "circle" only overwrites the last 2 bytes ("us" to "le"). The whole process can be easily seen when analyzing a transaction log with Log Explorer.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    There is no deletion-insertion going on. An update of "circus" to "circle" only overwrites the last 2 bytes ("us" to "le"). The whole process can be easily seen when analyzing a transaction log with Log Explorer.
    In terms of changes to the data page, yes, but the log page still gets written in its entirety. As a second issue, if you change "circus" to "circuses" then things may (or may not) get complex if the page is full.

    -PatP

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Of course complexity increases when circus is in town! But in respect to log page being written, - you answered your own question, - it does get written!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I can't find squat on Microsoft's site that deals with this. Thanks, MS.

    But here is some info from Kalen:

    http://www.winnetmag.com/SQLServer/A...8031/8031.html

    In SQL Server 7.0 (and presumably 2000...blindman), updates can happen in place or as a delete followed by an insert. An in-place update is one where SQL Server changes the bytes in the row with no movement of data necessary.

    The leaf level of nonclustered indexes contains a row locator for every row in the table. If the table has a clustered index, the row locator in every nonclustered index is the clustering key for that row. So if—and only if—the clustered index key is updated, modifications are required in every nonclustered index.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That's correct. But as Pat mention (not fully, but kinda hinted) that if the data modification of non-indexed fields affects physical location (page) of indexed fields, and no clustered index is defined, - that "may" (!!!) require an update of non-clustered index pages.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Inoni
    Ok, just allow me to be sure, if I have a table with 10 indexes and I modify a field in some record which is not indexed, none of the indexed are recalculated right?

    I'm asking because I read something that made me unsure about this process, see this link: http://www.sql-server-performance.com/q&a59.asp
    Look at the part where he writes: "For every data modification you have, each index in your table needs to be updated".

    Thanks,

    Inon.
    Below is the reply to my question from the author of the article you were referring to:

    If you modify a non-indexed column, then other indexes are not affected.

    Brad

    _____________________________________________
    From: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
    Sent: Tuesday, June 08, 2004 11:34 AM
    To: webmaster@sql-server-performance.com
    Subject: SQL Tuning

    In your Q&A posting (http://www.sql-server-performance.com/q&a59.asp) you're stating that "every data modification you have, each index in your table needs to be updated". Does it hold true even if you modify non-indexed fields?

    Thanks in advance.
    Last edited by rdjabarov; 06-08-04 at 14:42.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  13. #13
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40
    Great, Thanks a lot for the help!

    I thought I knew the answer, but after I accidentally bumped into that article I wasn't sure and had to be sure.


    Inon.

Posting Permissions

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