I have a query that was on the top of my activity monitor as the most expensive query at average duration > 18,000ms. My table is currently at 2,533,081 rows. My index is on an alternative bigint column, which i have generated so i can have a clustered unique index. The GUID is a uniqueidentifier datatype of which i have no control over (comes from an interface) and the design decision was made a decade ago.
Unfortunately, the update query needed to check the GUID (unique identifier). This is the one that is causing performance issues now after 2 months of execution. The only parameter i receive for the reset is the GUID, so i cannot use date, time or any other parameter for resetting the log record.
update Log
set reset = @sockettime
where guid = @guid
and reset is null
I created yesterday a second nonclustered index on the uniqueidentifier GUID only, and it returned the performance back to good levels again.
What i want to ask in this forum is: what speaks against creating such a second index on uniqueidentifier? What should i do to maintain performance on the uniqueidentifier index?
Thanks in advance.
I don't see anything wrong in creating of the second index. The guid has perfect cardinality. The searching using such index is perfect. Regarding the maintenance, there is nothing special - just like any other index. If you perform a lot of deletes then some rebuild or reorganize will not make harm from time to time. You can find ready to use maintenance procedure in the Internet or maybe you even have one applied and you don't even know it.