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.update only affects the modified data

second one I have no idea what a priority setting is...|||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 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_8ynt.asp) to LOW.

-PatP|||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.|||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.|||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...|||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.|||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|||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!|||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/Article/ArticleID/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 ifand only ifthe clustered index key is updated, modifications are required in every nonclustered index.|||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.|||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.|||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.