Does adding with (Nolock) reduce contention when selecting from temp tables, or is SQL Server smart enough to not create contention on temp tables in the first place?
PS: Yes I am aware of the dangers of READUNCOMMITTED.
select * from #myTempTable
vs
select * from #myTempTable with (nolock) --is this faster?
You can use trace flag 1200 (on a development machine as I think this is global) to see the locks taken out for yourself
SET NOCOUNT ON;
CREATE TABLE ##T
(
X INT
)
INSERT INTO ##T
SELECT number
FROM master..spt_values
CREATE TABLE #T
(
X INT
)
INSERT INTO #T
SELECT *
FROM ##T
/*Run the commands first with the trace flag off so the locking
info is less full of irrelevant stuff about plan compilation
*/
GO
PRINT '##T Read Committed'
SELECT COUNT(*) FROM ##T
PRINT '##T NOLOCK'
SELECT COUNT(*) FROM ##T WITH (NOLOCK)
PRINT '##T Finished'
GO
PRINT '#T Read Committed'
SELECT COUNT(*) FROM #T
PRINT '#T NOLOCK'
SELECT COUNT(*) FROM #T WITH (NOLOCK)
PRINT '#T Finished'
GO
DBCC TRACEON(-1,3604)
DBCC TRACEON(-1,1200)
GO
PRINT '##T Read Committed'
SELECT COUNT(*) FROM ##T
PRINT '##T NOLOCK'
SELECT COUNT(*) FROM ##T WITH (NOLOCK)
PRINT '##T Finished'
GO
PRINT '#T Read Committed'
SELECT COUNT(*) FROM #T
PRINT '#T NOLOCK'
SELECT COUNT(*) FROM #T WITH (NOLOCK)
PRINT '#T Finished'
GO
DBCC TRACEOFF(-1,3604)
DBCC TRACEOFF(-1,1200)
DROP TABLE ##T
DROP TABLE #T
For a global temp table it unsurprisingly makes more of a difference.
There is still a small difference in type of lock for local #temp tables though. I reproduce that part of the output below
#T Read Committed
Process 56 acquiring IS lock on OBJECT: 2:301244128:0 (class bit0 ref1) result: OK
Process 56 acquiring S lock on OBJECT: 2:301244128:0 (class bit0 ref1) result: OK
Process 56 releasing lock on OBJECT: 2:301244128:0
#T NOLOCK
Process 56 acquiring Sch-S lock on OBJECT: 2:301244128:0 (class bit0 ref1) result: OK
Process 56 acquiring S lock on HOBT: 2:9079256880114171904 [BULK_OPERATION] (class bit0 ref1) result: OK
Process 56 releasing lock on OBJECT: 2:301244128:0
Edit: The above results are for a heap. For temp tables with a clustered index the results are below.
#T Read Committed
Process 55 acquiring IS lock on OBJECT: 2:1790629422:0 (class bit0 ref1) result: OK
Process 55 acquiring S lock on OBJECT: 2:1790629422:0 (class bit0 ref1) result: OK
Process 55 releasing lock on OBJECT: 2:1790629422:0
#T NOLOCK
Process 55 acquiring Sch-S lock on OBJECT: 2:1790629422:0 (class bit0 ref1) result: OK
Process 55 releasing lock on OBJECT: 2:1790629422:0
#T Finished
The reason for the BULK_OPERATION lock on the heap version is explained here. But it can be seen that the locking overhead is pretty minimal whatever.
It's unlikely to make much difference since the scope of temporary tables is the same connection.
You're only competing for locks with yourself in the same connection, and if you're reading the temporary table, then you're probably the only one doing so.
In general, its probably better not to over-optimise and trust the SQL Server query engine to do its job well. Wait till you have a problem before you try to fix anything.
EDIT (slightly off-topic): (updated link)
However, there is some discussion around the impact on the whole of the tempdb when you create a temporary table. It may be possible to optimise - see options here (however that is an old article - SQL Server 6.5 / 7.0) and its likely SQL Server 2000 upwards deals with that scenario automatically
Again though, I recommend wait till you have a problem before complicating your solution.
It can make a huge difference depending on your query. I reduced a query execution time from 1295 to 590 simply by adding WITH (NO LOCK) on my temp table. Results may vary.
