Covering Indexes: Not Just for SELECT but also for UPDATE statements

  • Mike Byrd

    Ten Centuries

    Points: 1176

    Comments posted to this topic are about the item Covering Indexes: Not Just for SELECT but also for UPDATE statements

    Mike Byrd

  • dragonharper

    SSC Rookie

    Points: 35

    I recently had a similar issue with a scratch table.  One of my predecessors had created a scratch table to perform intermediate sums and verify that in/out times added up to the total reported time.  Recently we have been receiving deadlock errors from the application, third party software.   My initial thought was why is it deadlocking now nothing has changed, then I realized that the only time it became an issue was on Monday mornings when everybody submitted their time.  Ah, a traffic issue!  My research pointed to a lack of a primary key on the scratch table causing a table lock to be applied for the updates and deletes.  I added a primary key to the table and the problem was solved.  Lesson learned, always define a primary key it makes everything more efficient not just faster.

  • corsair1

    Old Hand

    Points: 308

    Mike you have Query Cost statistics throughout your post. How are you calculating that?

  • Mike Byrd

    Ten Centuries

    Points: 1176

    From the estimated query plan.  Statistics were up to date, so estimated rows were close to actual rows.

    Mike Byrd

  • corsair1

    Old Hand

    Points: 308

    Thanks.

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

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