Post reply

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

  • May 14, 2018 at 11:34 pm

    #334700

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

    Mike Byrd

  • May 15, 2018 at 5:40 am

    #1990493

    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.

  • May 15, 2018 at 8:38 am

    #1990531

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

  • May 15, 2018 at 10:39 am

    #1990553

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

    Mike Byrd

  • May 15, 2018 at 11:29 am

    #1990568

    Thanks.

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

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