4

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; 
4

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
     );

NUTS

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.

  • 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. – Jonathan Fite Sep 4 at 12:54
4

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.

0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.