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