I have below SQL query which runs on avg. 40 secs and need some guidance where can i improve it or possibly re-write:
Declare @status_D INT, Declare @StatusIP INT, Declare @Status_F INT, SELECT @status_D = Id From Batchtablestatus (NOLOCK) Where desc ='Value1'; SELECT @StatusIP = Id From Batchtablestatus (NOLOCK) Where desc ='Value2'; SELECT @Status_F = Id From Batchtablestatus (NOLOCK) Where desc ='Value3' BEGIN TRAN UPdate U SET U.[id]= @StatusIP, U.[EnabledDate]= Getdate() OUTPUT deleted.col1, deleted.col2, deleted.col3, deleted.col4, Inserted.id FROM ( SELECT TOP 5000 * FROM BatchChangeTable WITH (ROWLOCK,UPDLOCK) WHERE [id] IN ( @status_D ,Status_F) ORDER BY [ModifiedTime] )U; COMMIT TRAN
I am getting warning from blitzcache as Forced serialization, Expensive sort, non-sargable and Merge join "Mant to Many" = True
The only index we had is on id which is a NC index with id as leading key column.
I will try to update my question if execution plan if i can. Table in SELECT is around 100 million as of now. Also it has PK/CI on col1.
I have asked users why we do * in that TOP which pulls almost 50+ columns but in update use just fewer, may b i am thinking wrong but just came to my mind.
Adding index to modified time also does not seem to help.
Please suggest what else can be checked. Thanks