Lately I've been studying a lot about database and everything. Today, performing a routine check on the processes of the instance of SQL Server verified that a statement that was running in one database was blocking execution in another database. By checking the queries that were running at the time, I saw that there was no direct link between queries, their structures look like 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*/
Looking at this, I noticed that the only part in which the queries are similar is in the creation of the temporary table next to select in SELECT ... INTO #tmp I decided to search on this, I found some articles, however, all me left me doubting the verity of my conclusion.
SQL Server lock issues when using DDL (including SELECT INTO) clause in long running transactions
In these two articles that I took as an example, you can see totally conflicting comments and comments like:
Originals
Does this mean that you have to select a file in a file? 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
Does select ... into #temp block anything by the length of the stored procedure that contains the select? No way. Just reading this statement and I started laughing.
Does select ... into #temp block anything by the duration of select? Not really.
Fortunately, it was just a problem for SQL 6.5. It was fixed in 7.0 and later.
** Quotes have been taken from multiple users.
While on the second link, your conclusions say 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 database properties from SSMS. In actuality, you are just experiencing typical locks and blocks on row (s) in system tables.
Translation
DDL statements (including SELECT INTO) containing explicit transactions should be avoided, if possible, because it creates unique locks on the line (s) in the system tables by the duration of the transaction.
This may not cause harmful effects, but often misleads DBAs to think that there was a network connectivity problem or SQL Server resource. If the code is not well written, you you may encounter lock request timeouts when you run Typical administrative tasks, such as checking bank properties of SSMS data. In reality, you are experiencing blockages and typical blocks in rows in the system tables.
Sorry for extending the question, but would like to know which one is correct? Really DDL's as SELECT ... INTO #temp cause locks on tempdb? Which of the links is more correct? Is there any place where I can research more about it? Is there any way to test the arguments of those involved in the above links?
Thanks in advance.
Q: I'm using SQL Server 12.0, better known as SQL Server 2014.