Non clustered index : performance issue

Question

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 :

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

Regards,

Phani

Answer 1

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.

• Jaddu: 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 .
• Damien_The_Unbeliever: @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.
• ypercubeᵀᴹ: That should be: "it affects the entire table (clustered index + all non-clustered indexes that include an updated column)", right?
• Damien_The_Unbeliever: @ypercube - that would be correct (if the column is in the index, or is an included column in the index)

Answer 2

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.

Answer 3

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.

Answer 4

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