FIX: Assertion occurs when a parallel query deletes from a Filestream table in SQL Server 2014, 2016 and 2017

Dotyczy: SQL Server 2014 DeveloperSQL Server 2014 EnterpriseSQL Server 2014 Enterprise Core

Symptoms


Consider the following scenario:

  • You have a very large Filestream table.
  • You run a delete query of rows in the Filestream table that creates an expensive query plan, such as one that is filtered by an inner join to another large table in the system.
  • The query optimizer chooses a parallel execution plan.

In this scenario, an assertion will occur, and you may receive error messages that resemble the following in the SQL Server error log:

DateTime spid SpidNumber     Error: 5553, Severity: 20, State: 6.

DateTime spid SpidNumber     SQL Server internal error. FILESTREAM manager cannot continue with current command.

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. 

Resolution


The issue is fixed in the following cumulative updates for SQL Server:
About cumulative updates for SQL Server:

Each new cumulative update for SQL Server contains all thehotfixes and all the security fixes that were included with the previouscumulative update. Check out the latest cumulative updates for SQL Server:

Workaround


To work around this issue, you can use the following method:
Move the inner join query to a separate statement and save the results to a temporary table. Then run the delete query that's filtered by the entries in the temporary table. Additionally, you can increase the cost threshold for parallelism to force the optimizer to serialize the query plan.
For example:

SELECT RefTable.Alias INTO #T FROM DB.dbo.OuterTable INNER JOIN DB.dbo.RefTable ON RefTable.Alias = OuterTable.Alias

             WHERE OuterTable.Alias > 30006 AND OuterTable.Alias < 30010 

DELETE FROM DB.dbo.[Table] WHERE Alias IN (SELECT * FROM #T)

References


Learn about the terminology thatMicrosoft uses to describe software updates.