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.

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Any additional feedback? (Optional)

Thank you for your feedback!

×