or
bryan rebok imported from SE
sql-server sql-server-2014
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; 
Top Answer
Erik Darling
Repro!
--
I was able to reproduce your issue using [SQL Query Stress][1], 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
         );

[![NUTS][2]][2]

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][3].

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][4], 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][5] 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:

[![NUTS][6]][6]


  [1]: https://github.com/ErikEJ/SqlQueryStress
  [2]: https://i.stack.imgur.com/X4eyv.jpg
  [3]: https://blogs.msdn.microsoft.com/sql_server_team/tempdb-files-and-trace-flags-and-updates-oh-my/
  [4]: https://dba.stackexchange.com/q/13392/32281
  [5]: https://dba.stackexchange.com/users/46773/jonathan-fite
  [6]: https://i.stack.imgur.com/iOHc8.jpg
Temp Table Filtered Index Creation is Blocked When Stored Procedure is Run Multiple Times Concurrently

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.