Post reply

Index Writes

  • July 30, 2011 at 6:41 pm

    #245372

    Hi,

    In my company, the hosting team schedule indexes once a week. My understanding is that is when SQL server runs the index and updates the index with the new values.

    I am reading for my SQL server MCITP exam it says the following:

    If you have only one index on the table, one write to the table also causes one write to

    the index. If there are 30 indexes on the table, one write to the table causes 30 additional

    writes to the indexes.

    Which one is correct?. I think I am confusing 2 different things.

    Can someone help me, please?

    Thanks

    Shiyam

  • July 30, 2011 at 9:27 pm

    #1361282

    All indexes get written to (provided it isn't ignored with a filtered index's where clause). That is why you need to really plan, because additional indexes lead to more overhead with oltp.


    Twitter: @SQLife
    Email: sqlsalt(at)outlook(dot)com

  • July 30, 2011 at 9:48 pm

    #1361283

    Thanks.

    But Why should the index be scheduled to run if every write operation is written to the index at real time..

  • July 30, 2011 at 9:54 pm

    #1361284

    What do you mean, "run the index"? Im guessing you're talking about a rebuild/reorganize. Yes, that should be part of your maintenance plan to defrag the index. What I was talking about originally is how each index is hit with insert/update/delete queries against the object.


    Twitter: @SQLife
    Email: sqlsalt(at)outlook(dot)com

  • July 31, 2011 at 1:08 pm

    #1361324

    If you have 30 indexes on a table, you have potentially 31 writes when a change is made to the table. If one of the 30 is a clustered index, then you can have 30 max ,but if there is no clustered index, you could have a write to the heap + 30 writes.

    However that depends on the update made. An insert or delete definitely causes every index to be written to, but an update may not. If the column(s) updated are not part of the index, then that index is not touched.

    There is no "run" the index. As you make changes, you potentially spread out the pages of the table across the database file, which is fragmentation. An index rebuild/reorganize looks to try and correct this fragmentation.

  • August 1, 2011 at 4:45 pm

    #1361889

    Thanks a lot for explaining this to me. Great.

Viewing 6 posts - 1 through 5 (of 5 total)

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