SQL Server – Locking Issues from SELECT INTO #TempTable

sql server

We had an issue on our live DB, where a query executed from the test instance, that referenced the live instance caused blocks due to it showing as 'suspended', which caused user requests to back up, all due to this one query.

Looking at the query, there is one request at the start that references the live DB:

SELECT      [Columns]
INTO        #TempTable
FROM        [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Users                      AS  sv
RIGHT JOIN  [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Company                    AS  co
INNER JOIN  [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Company_Marketing          AS  cm  ON co.co_ref        = cm.cmk_co_ref
INNER JOIN  [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Property                   AS  pr  ON co.co_pr_ref     = pr.pr_ref 
LEFT JOIN   [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Company_Types              AS  ct  ON co.co_cot_id     = ct.cot_id
LEFT JOIN   [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Users                      AS  cb  ON cm.cmk_called_by = cb.us_id 
LEFT JOIN   [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Marketing_Disciplines      AS  md  ON cm.cmk_mdi_id    = md.mdi_id 
LEFT JOIN   [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].REGIONS                    AS  rg  ON sv.us_reg_id     = rg.reg_id
LEFT JOIN   [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].REGIONS                    AS  rg1 ON CB.us_reg_id     = rg1.reg_id
WHERE       cm.cmk_appointment > '2017-01-01'
AND         md.mdi_mdg_id <> 2
ORDER BY    cm.cmk_appointment

This was being run against a test instance, but referencing live (there is a linked server). Changing it to run on the correct live instance means that it ran in a matter of seconds, and no blocking issues.

We are tidying up the query, as it is far from efficient anyway, but, what I am wanting to understand is, how does SELECT INTO behave in terms of blocks/locks? Would the query above mean that blocks are present on the tables in the query whilst #TempTable is populated?

Thanks for your assistance.

Best Answer

I would expect the same behavior with or without the INTO. Remote queries run with default READ COMMITTED isolation level behavior acquire and release shared locks during execution just like local queries. Those locks can block writers of the remote tables, especially with poorly written queries and if shared table-level locks are held for the duration of the query.

Separately, the local SELECT INTO query consists of two separate transactions when run in autocommit mode. This is done to avoid holding long-term locks on the system catalog objects which could otherwise block object creation by other sessions. Creation of the new table is done in the first transaction and then the table is loaded using the remote query source in the second transaction.

Related Solutions

SQL Server Execution Plan – SHOWPLAN vs ‘Include Execution Plan’ Warnings for the Same Query

This:

SET SHOWPLAN_XML ON;
GO
SELECT * FROM sys.objects;
GO

Is equivalent to pressing Display Estimated Execution Plan on the toolbar (or hitting Ctrl + L). You'll notice that no rows are returned from the query, like there is when you use Include Actual Execution Plan (Ctrl + M).

The spill warning is only a runtime warning. There is no way that SQL Server can know, when displaying the estimated plan, that a spill will happen at runtime. This is because a spill is caused by factors that might only be present during certain invocations of the query (for example, when there is memory pressure). The estimated plan knows roughly how much memory it's going to ask for, but it can't know until execution that it isn't going to get it.

As an aside, may I recommend* our free tool, SQL Sentry Plan Explorer? I think it provides much more obvious information than Management Studio. I recently wrote a lengthy blog post that can act as a tutorial, and Jonathan Kehayias has a great PluralSight course on it as well.

_{* Disclaimer: I work for SQL Sentry.}

SQL Server – Multiple Column Concatenation

I ran a few tests using a little over 6 mil rows. With an index on the ID column.

Here is what I came up with.

Your initial query:

SELECT * FROM (
    SELECT t.id,
            stuff([M].query('/name').value('/', 'varchar(max)'),1,1,'') AS [SomeField_Combined1],
            stuff([M].query('/car').value('/', 'varchar(max)'),1,1,'') AS [SomeField_Combined2]
    FROM dbo.test t
    OUTER APPLY(SELECT (
                    SELECT id, ','+name AS name
                    ,','+car AS car
                    FROM test WHERE test.id=t.id
                    FOR XML PATH('') ,type)
                 AS  M) 
            M ) S
GROUP BY id, SomeField_Combined1, SomeField_Combined2

This one ran for ~23 minutes.

I ran this version which is the version I first learned. In some ways it seems like it should take longer but it doesn't.

SELECT test.id,
    STUFF((SELECT ', ' + ThisTable.name
            FROM   test ThisTable
            WHERE  test.id = ThisTable.id
            AND    ThisTable.name <> ''
            FOR XML PATH ('')),1,2,'') AS ConcatenatedSomeField,
    STUFF((SELECT ', ' + car
            FROM   test ThisTable
            WHERE  test.id = ThisTable.id
            AND    ThisTable.car <> ''
            FOR XML PATH ('')),1,2,'') AS ConcatenatedSomeField2
FROM test 
GROUP BY id

This version ran in just over 2 minutes.

Best Answer

Related Solutions

SQL Server Execution Plan – SHOWPLAN vs ‘Include Execution Plan’ Warnings for the Same Query

SQL Server – Multiple Column Concatenation

Related Question