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 :
UPDATE EMP SET ISActive= 0 WHERE DEPTID in (11,22)
Will only nonclustered index on
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