October 26, 2004 at 12:58 pm
We have documentation for standardizing SQL queries, etc. One of the sections referenced the following:
3. Tuning performance tips
B. Build temp tables with “CREATE/INSERT”, don’t use “SELECT INTO”. The “SELECT INTO” method is easy to code, but requires exclusive locks on the tempdb system tables for the duration of the query. It will block all other processes running at the same time that require creating temp tables, degrading system performance.
I have never heard this before. Is it correct?
Thanks,
Farrell
I wasn't born stupid - I had to study.
October 26, 2004 at 1:29 pm
Not currently. I believe that was a problem in SQL Server 6.5, and was fixed in 7.0.
--
Adam Machanic
whoisactive
October 27, 2004 at 7:36 am
It's correct. There will be exclusive locks on system tables e.g. objects in tempdb.
October 27, 2004 at 8:39 am
peterhe,
Post a reproduction that proves that.
... Or better, I'll just debunk this claim, which -- annoyingly -- keeps popping up all over the web in various "tips and tricks" pages:
(query analyzer window #1)
set transaction isolation level serializable
begin transaction
select *
into #blah
from sysobjects
(now flip to qa window #2)
select *
into #blah
from sysobjects
... if creating a temp table using INTO blocked other processes, given the serializable transaction level, the query in window #2 should now be blocked. But it isn't. That issue was fixed a long time ago.
--
Adam Machanic
whoisactive
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply