3

Are locks taken out when querying a temp table? If so, how granular are they by default? Is there a performance hit similar to locking normal rows/table?

I assume no locks are taken because temp tables (at least as of SQL 2008) are created per instance.

select x,y,z into #MyTempTable
from SomeOtherTable
Dustin Davis
  • 14,482
  • 13
  • 63
  • 119
  • http://stackoverflow.com/questions/1302670/sql-server-select-into-and-blocking-with-temp-tables – Mitch Wheat Jun 08 '11 at 03:46
  • @Mitch that was a good read, I didn't think about the TempDB. I'm asking specifically about the #MyTempTable. These long running queries make use of temp tables everywhere like it's a religion and i'm just wondering if locks could be hurting performance. – Dustin Davis Jun 08 '11 at 03:53
  • have you determined that you have a tempDB bottleneck? Is it on its own RAID 10 array? – Mitch Wheat Jun 08 '11 at 03:55
  • @Mitch no, I have not done any testing on locking or lock contention yet. This was a late night thought before I head into the office tomorrow. Yes, it's on it's own RAID 10 as is data+indexes and logs. – Dustin Davis Jun 08 '11 at 04:15

1 Answers1

1

  • A temp table like this is local to your connection. It can't affect concurrency because no-one else can read it.

  • Temp tables generally behave like normal tables for concurrency, isolation, locks, transactions etc

  • You may (or may not!) get tempdb contention (link about TF 1118) under heavy load which isn't the same as table locking

  • Is your question prompted by one of those SQL Server 6.x myths (DBA.SE)?

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • @Andriy M: that is mostly impossible. Each connection can have one request and all request are serialised to the database. An app using multiple threads in the same connection object would simply crash very quickly (and is it even possible?) – gbn Jun 08 '11 at 05:19
  • You are right. I started thinking about sharing a connection and forgot about concurrency (even though the question is basically about concurrency, silly me). – Andriy M Jun 08 '11 at 05:57
  • my question is, are locks taken out on temp tables? not the tempdb, the temp table #MyTempTable. Should I be providing nolock hints or possibly forcing tablock on the queries selecting, inserting, updating, deleting from #MyTempTable – Dustin Davis Jun 08 '11 at 12:50
  • I said "Temp tables generally behave like normal tables for concurrency, isolation, locks, transactions etc" as my 2nd point. You don't need hints generally for any table: not just temp tables – gbn Jun 08 '11 at 12:51