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 the hotfixes and all the security fixes that were included with the previous cumulative 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 that Microsoft uses to describe software updates.
