Temp Table Filtered Index Creation is Blocked When Stored Procedure is Run Multiple Times Concurrently 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;
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: