MS SQL Perofrmance issue with T-sql

Vijay
Vijay used Ask the Experts™
on
Please find the screenshot and suggest us how to fine tune: This is SQL Server 2016
Key.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
Hi,

You didn't provide us with the query. Basing on this screen only you are querying columns which are not included in an index and index has been choosen, therefore even if it has clusterd index key values in its leafs SQL is looking to PK for other columns.
Modify your index accordingly by adding columns (main or included - depends on query which we don't see) or create new covering index if query is worth doing that - is being frequently executed etc.

Regards,
Daniel
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Please tell us on what basis you believe we can answer your question without seeing the code that generated it, the schemas of the queried tables, or a full execution plan:   Mind reading, Seance, or 'The Force'.

Author

Commented:
----This is the Query---

WITH [CTEOrderedUpdate]
		  AS
		  (
			SELECT TOP(@MaxRows)
			  [Uid],
			  [AgentName],
			  [Step],
			  [StepInProgress],
			  [ProcessCount],
			  [DateStarted],
			  [DateFinished]
			FROM
			  [dbo].[BlobMigrationQueue] WITH(ROWLOCK, UPDLOCK, READPAST,Index=IX_BlobMigrationQueueGet)
			WHERE
			  [Step] = 0 AND
			  [Failed] = 0 AND
			  [StepInProgress] = 0
		  )
		  UPDATE
			[CTEOrderedUpdate]
		  SET
			[StepInProgress]  = 1,
			[AgentName]         = @AgentName,
			[ProcessCount]    = [ProcessCount] + 1,
			[DateStarted]     = SYSDATETIME(),
			[DateFinished]    = NULL
		  OUTPUT
			inserted.[Uid]
		  INTO
			@Batch([Uid])

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Database Expert
Awarded 2016
Top Expert 2016
Commented:
How much time the query is taking ?

The query is using an index but that index does not cover all the column the query needs so the query will have to go for that extra column using keylookup 50001 times which looks like an issue. If possible add(include) that extra column in that in that index itself so that we can ignore this key lookup part. Meaning we will get all the data at one place and do not have to traverse to get the additional data.

Also why we are specifying so many locks and an Index..(forced)
 [dbo].[BlobMigrationQueue] WITH(ROWLOCK, UPDLOCK, READPAST,Index=IX_BlobMigrationQueueGet)

Author

Commented:
Thank you

Author

Commented:
Thank you Pawan.
We found the solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial