Non clustered index : performance issue I have table which  has schema  as follows 

TABLE  EMP 

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  : 

UPDATE EMP SET ISActive= 0  WHERE DEPTID in (11,22)


Questions :


Will only nonclustered index on ISActive get updated? 
Will all the nonclustered indexes and clustered index get updated ?
If I remove some of the rows as they are quite old will that help with performance ? 
Why this query take some minutes to execute and sometimes it will take less then second ?
What is the best possible solution for this ? 


Any pointers are highly appreciated 

Thanks in advance 

Regards,

Phani
1&amp;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.