Temp Table Filtered Index Creation is Blocked When Stored Procedure is Run Multiple Times Concurrently

Question

Are there any known blocking issues in SQL Server 2014 that are specific to creating one or more filtered indexes on a temp table after the initial table creation? (i.e. not an inline index creation)

I am not permitted to post the actual code; so, I have changed table/column names to protect the innocent. The representative but very rough logic below is included at the beginning of a stored procedure.

The blocking occurs when the stored procedure is run multiple times concurrently, I believe. According to my DMV query, the stored procedure is being blocked on the 1st nonclustered index creation statement.

CREATE TABLE #temp_table_name_goes_here (
    [the_first_col] BIGINT NULL
    ,[the_second_col] INT NULL
    ,[another_col] VARCHAR(20) NULL
    );

CREATE INDEX tmp_indx_temp_table_name_goes_here_1 ON #temp_table_name_goes_here (the_first_col)
WHERE the_first_col IS NOT NULL;

CREATE INDEX tmp_indx_temp_table_name_goes_here_2 ON #temp_table_name_goes_here (the_second_col)
WHERE the_second_col IS NOT NULL; 

Answer 1

Repro!

I was able to reproduce your issue using SQL Query Stress, both using your example, and changing things to create the indexes inline, as Paul suggested:

CREATE TABLE #temp_table_name_goes_here
     (
         the_first_col BIGINT NULL,
         the_second_col INT NULL,
         another_col VARCHAR(20) NULL,
         INDEX tmp_indx_temp_table_name_goes_here ( the_first_col ) 
               WHERE the_first_col IS NOT NULL,
         INDEX tmp_indx_outstanding_inventory ( the_second_col ) 
               WHERE the_second_col IS NOT NULL
     );

Of course, SQS was running 200 concurrent sessions for 20 iterations., so it took some work to get there.

I'm on SQL Server 2017, so the situation was not changed by enabling Trace Flag 3427.

If tempdb is not configured optimally on your system, I'd start there:

• One data file per core up to 8 cores
• Enable Trace Flags 1117 and 1118

If you still have issues, I'd open a support case with Microsoft. Scalability issues like this should be front and center.

Follow Up

After stumbling on this question, I decided to re-try the test using table variables. Under the same circumstances as above, the same contention was observed.

DECLARE @temp_table_name_goes_here TABLE
     (
         the_first_col BIGINT NULL,
         the_second_col INT NULL,
         another_col VARCHAR(20) NULL,
         INDEX tmp_indx_temp_table_name_goes_here ( the_first_col ) 
               WHERE the_first_col IS NOT NULL,
         INDEX tmp_indx_outstanding_inventory ( the_second_col ) 
               WHERE the_second_col IS NOT NULL
     );

I also re-ran the temp table test with an explicit DROP IF EXISTS (or the old style check for a NOT NULL object_id) at the beginning, per Jonathan Fite's comment:

I've hit this error before. My solution was to add explicit drop of the indexes and the temp table (DROP IF EXISTS) before creating it. It only happens when the same session runs the stored procedure multiple times.

This did help. Contention was either non-existent or minimized.

Adding an explicit DROP TABLE, or DROP IF EXISTS at the end had an odd effect: curiously, the contention switched to dropping the temp tables:

Answer 2

i.e. not an inline index creation

The major disadvantage would be that the temporary table would no longer qualify for caching, since DDL is being performed after creation.

This assumes the temporary table would qualify without the filtered index, and caching the structure would be beneficial to the workload. The question is a little light on detail, so mentioning this is a bit of a guess.

A second consideration, almost certainly unrelated to the question, it that separate index creation statements would cause a SNAPSHOT isolation level explicit transaction to fail, since DDL is not versioned.

Answer 3

Not a solution but for the sake of completeness: I had a similar problem on SQL 2016 while creating a filtered index on a computed column in a temporary table (must not be in a procedure, "works" stand alone too).

CREATE TABLE #temp_table_name_goes_here (
    [the_first_col] BIGINT NULL
    ,[the_second_col] INT NULL
    ,[another_col] VARCHAR(20) NULL
    , calc AS IIF(the_second_col = 0, 'hello', NULL)
    , INDEX #ix_tmp (calc) WHERE calc IS NOT NULL
    );

SELECT * FROM #temp_table_name_goes_here

The SELECT will throw several times error 207: Invalid column name 'calc'.

and a final error 4184:

Cannot retrieve table data for the query operation because the table "#temp_table_name_goes_here" schema is being altered too frequently. Because the table "#temp_table_name_goes_here" contains a filtered index or filtered statistics, changes to the table schema require a refresh of all table data. Retry the query operation, and if the problem persists, use SQL Server Profiler to identify what schema-altering operations are occurring.

So it seems that therer are some problems with filtered indexes on temp tables.

I worked around this bug by not filtering the index (even if it means, that it will be larger)