I recently (within the last 2 years) created a system to monitor our SQL instances from a central instance. Without going into details about the whole process, I had a requirement to run multiple tasks to retrieve data from the monitored instances in parallel (one or more instances unavailable would cause delays in retrieving data from all the other instances).

There's an easier way to do this than messing with lock hints, use the UPDATE ... OUTPUT format of the UPDATE query. Here's a snippet of the code that I use:

-- ******************************************************************************************

-- Create the local temp tables used in this proc

CREATE TABLE #DRT_Update (

DataRetrievalTaskIDINT)

-- ******************************************************************************************

-- ******************************************************************************************

-- Clear the temp table ...

ProcessNextServer:

DELETE #DRT_Update

-- ******************************************************************************************

-- ******************************************************************************************

-- Get the next Data Retrieval Task to be processed - set the status to 2 for the record selected.

UPDATE DataRetrievalTask

SET [Status] = 2

OUTPUT inserted.DataRetrievalTaskID

INTO #DRT_Update

WHERE DataRetrievalTaskID = (SELECT MIN(DataRetrievalTaskID)

FROM DataRetrievalTask

WHERE [Status] = 1

AND StartTime <= GETDATE() )

-- ******************************************************************************************

This process is easily scalable - I just have to create another job to execute the Stored Proc to add another process. This way, you dont have to worry about Locks etc - the SQL Engine does it for you.