Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have table which has schema as follows


EMPID  - Primary KEY 
DEPTID - NON Clustered Index
ISActive - NON Clustered Index

It has other non index columns as well

This table is highly transactional and will have lots of data. Currently I have 824392 rows in this table.

Now I am executing query which takes lot of time sometime and sometime it works like a charm.

PFB the query :


Questions :

  1. Will only nonclustered index on ISActive get updated?

  2. Will all the nonclustered indexes and clustered index get updated ?

  3. If I remove some of the rows as they are quite old will that help with performance ?

  4. Why this query take some minutes to execute and sometimes it will take less then second ?

  5. What is the best possible solution for this ?

Any pointers are highly appreciated

Thanks in advance



share|improve this question

migrated from Feb 2 '12 at 14:49

This question came from our site for professional and enthusiast programmers.

1&2 - Every update query must update the data for the table, wherever it may appear - in the clustered index (always), in any index that has the column in its definition, and in any index which just includes this column.

3 - Removing records may or may not help, it depends on 4:

4 - Probably, because sometimes it will not use the index, but will instead choose to scan the entire table. If it's scanning the entire table, then reducing the size of the table (via (3) above) will help. It will scan the entire table if, so far as the optimizer is concerned, that will be more efficient than using the index, then accessing the clustered index to complete the operation. It may choose to do this because it's actually true that the index will not help, or if statistics are out of date, or just sometimes it gets this wrong. Examine execution plans to determine if this has happened.

4a - If there's a lot of activity on the table, then other operation may have locks that prevent your query from processing as fast as it could if it was the only activity occurring in the database. Waiting for locks can take seconds, minutes, or hours - it depends on how timely the other operations are

5 - Solution to what?

824392 isn't that many rows, but it may make for a big table - it depends on how wide those rows are, really.

share|improve this answer
Thanks Damien for the valuable information. i did not the explanantion for the point 1 and 2 . Could you please dig in to this more and explain it . – Jaddu Feb 2 '12 at 2:59
@Jaddu - there's no concept of an update just affecting a particular index - it affects the entire table (clustered index + all non-clustered indexes) and maintains their consistency. So the answer to 1 is no, and to 2 is yes. – Damien_The_Unbeliever Feb 2 '12 at 5:17
That should be: "it affects the entire table (clustered index + all non-clustered indexes that include an updated column)", right? – ypercubeᵀᴹ Sep 28 '12 at 18:45
@ypercube - that would be correct (if the column is in the index, or is an included column in the index) – Damien_The_Unbeliever Sep 28 '12 at 19:07

EMPID is PK Key which is customer index. And your query is using cluster index. Mean.. there should not be problem with index. However, you may need to check index fragmentation. If index fragmentation % is high, more than 30%, you should reubilt your index otherwise, need to reorganize.

According to what you are saying that someitmes take less than second, sometimes take minues. mean..because of others query process that make your query to wait.

share|improve this answer

Just curious, if your IsActive is one of your non-clustered index, and it appears to me as it is boolean, why not drop the non-clustered index for IsActive before the update and recreate the index after update.

share|improve this answer

Partition on Indexes ... may help in increasing the Performance... So avoiding a whole table scan.. (A local may Help.. )

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.