Thank you for your inputs. Would need one more direction here on tuning. All indexes are in place.

This is the only piece of code -

WHILE @VersionID <> 0

BEGIN

UPDATE dbo.[VersionTable]

SET [StatusCd] = 'Success' ,UpdateDttm = GETUTCDATE()

WHERE VersionId = @VersionId

EXECUTE @rc = [dbo].[GetVersionID] @Threadid,@VersionID OUTPUT

END

------------------------------------------

Procedure: dbo.GetVersionID

------------------------------------------

CREATE PROC dbo.GetVersionID

@Threadid int,

@VersionId int OUTPUT

AS

BEGIN

IF

(SELECT TOP 1 1

FROM dbo.VersionTable WHERE StatusCd = 'New')

UPDATE TOP (1) dbo.VersionTable

SET StatusCd = 'In Progress' ,ThreadId = @ThreadId,UpdateDttm = GETUTCDATE()

WHERE StatusCd = 'New'

IF (SELECT COUNT(1) FROM dbo.VersionTable

WHERE ThreadId = @ThreadId AND StatusCd = 'In Progress') = 1

SELECT @Versionid = VersionId

FROM dbo.VersionTable

WHERE ThreadId = @ThreadId

AND StatusCd = 'In Progress'

ELSE

SET @VersionId = 0

SELECT @VersionID

Note - GetVersionID proc does two tasks-

1. Picks the top 1 record that has StatusCd='New' for that passed ThreadId.

2. Changes the status of that to StatusCd = 'In Progress' and returns the VersionID.

===============

Table script

=================

CREATE TABLE [dbo].[VersionTable](

[VersionID] [int] NOT NULL,

[ThreadID] [int] NOT NULL,

[UpdateDttm] [datetime] NOT NULL,

[StatusCd] [varchar](10) NOT NULL,

CONSTRAINT [PK_VersionTable] PRIMARY KEY CLUSTERED

(

[VersionID] ASC

)

) ON [PRIMARY]

==========

Data

===========

INSERT INTO [dbo].[VersionTable]

([VersionID]

,[ThreadID]

,[UpdateDttm]

,[StatusCd])

SELECT 1,1,GETUTCDATE(),'New'

UNION ALL

SELECT 2,1,GETUTCDATE(),'New'

UNION ALL

SELECT 3,1,GETUTCDATE(),'Success'