SQL SERVER myth about SELECT INTO and tempdb locks

Question

I’ve been studying a lot lately about databases and everything. Today, performing a routine check on the processes of the SQL Server instance verified that an instruction that was running in one database was blocking the execution in another database. Performing checks on the queries that were being performed at that time, I saw that there was no direct link between the consultations, their structures are similar to these:

Query running on bank A:

SELECT ... INTO #tempA /*consultas de tabelas no banco A apenas*/

Query running on bank B:

SELECT ... INTO #tempB /*consultas de tabelas no banco B apenas*/

Seeing this, I found that the only part where the queries resemble is in the creation of the table temporary next to select in SELECT ... INTO #tmp I decided to research on this, I found some articles, however, all left me with doubts about the vericity of my conclusion.

SQL Server SELECT INTO and Blocking With Temp Tables

SQL Server lock issues when using a DDL (including SELECT INTO) clause in long running transactions

In these two articles I took as an example, can be verified responses and comments totally conflicting as:

Originals

Does select ... into #temp block Something for the Duration of the stored Procedure containing the select? Hell no. Just Reading that Claim and I Burst into Laugh.

Does select ... into #temp block Something for the Duration of the select? Not really.

Fortunately, it was only a problem for SQL 6.5. It was Fixed in 7.0 and later.

Translations

select ... into #temp blocks something by the duration of the stored procedure that contains select? Not at all. Just reading that statement and I started laughing.

select ... into #temp blocks something by the length of select? Not really.

Fortunately, it was only a problem for SQL 6.5. It was fixed in 7.0 and later.

**Citations were taken from several users.

While in the second link, in its conclusions is said the following:

Original

DDL (including SELECT INTO) statements containing long-running Explicit transactions, should be avoided if possible because it creates exclusive Locks on Row(s) in system Tables for the Duration of the transaction.

This Might not cause detrimental effects, but often it misleads Dbas to think there was a network Connectivity or SQL Server Resource Issue. If the code is not well Written, you may Encounter lock request timeouts when Performing Typical administrative tasks such as checking properties from SSMS. In actuality, you are just experiencing Typical Locks and Blocks on Row(s) in system Tables.

Translation

DDL instructions (including SELECT INTO) containing explicit transactions should be avoided, if possible, because it creates unique locks on (s) line(s) in system tables by duration of the transaction.

This may not cause harmful effects, but often deceives the Dbas thinking there was a network or connectivity problem SQL Server resource. If the code is not well written, you can find limit times of blocking request when executing typical administrative tasks, such as checking bank properties of SSMS data. In reality, you are experiencing blockages and typical blocks in lines in system tables.

Sorry to extend the question, though, would you like to know which one is correct? Really DDL s as SELECT ... INTO #temp cause tempdb blockages? Which of the links is more correct? Is there a place where I can research more on this subject? Is there any way to test the arguments of those involved in the above links?

From now on, I thank you.

P.S.: I am using SQL Server 12.0, better known as SQL Server 2014.

Answer 1

TEMPDB It is a system database and every time you use temporary tables (#Mytemptableexample) they are created in this database (not with this name but pq several sessions may be using the same name, if you look you will see that the real name is something very strange). Note that the tempdb is used all the time by the engine, especially for operations that process a lot of data where the tempdb is used as a form of auxiliary "memory".

Here I’ll just give a quick Overview about some problems related to your question.

1) Very common a blockage of physical resources, memory and especially IO. Note that by giving an input of millions of records you will be massively using disk access (not only by data but by indexes and other structures and tb by log). So there might be a hardware bottleneck there.

2) The second link you posted shows an article about a problem tested in SQL Server 2016 (the first link talks about a similar problem but that occurred in a very old version and that was fixed many years ago). In this link it is demonstrated that when creating and popular its temporary table some system tables are blocked, in particular the [sys].[sysrscols]. Personally I have not tested but it makes sense because when you create objects (tables, columns, etc.) these objects are catalogued in these tables. Note that it is interesting to do this test on your system to evaluate whether this occurs.

3) Further considerations on the tempdb especially when you use isolation levels snapshot using the tempdb p/ save versions of your data.

4) Finally the use of temporary tables can often be replaced by table variables. Note however that it depends on case by case. As variables (usually) stay in memory they tend to be more performative when they are small. By experience the temporary tables are better when vc is populating them with a lot of data (this is due to the amount of memory allocated for the table variables that when it breaks starts using the tempdb but with an overhead that greatly worsens the performance of those variables.).

Completion

Temporary tables are an important day-to-day tool but as all SQL-SERVER tools are subject to problems in some specific scenarios and should be used carefully.

So to determine whether your SQL INTO in these temporary tables is having a negative impact on performance you will have to check if the problem is really this, a good starting point is this second link that you posted.