The query processor ran out of stack space during query optimization" error is encountered when creating a backup of a database with many filegroups. (4377499)

The query processor ran out of stack space during query optimization" error is encountered when creating a backup of a database with many filegroups.

Replication steps - 

• Create a large number of file groups (the issue starts manifesting at above 2000-2200 on current dev machines, but it varies with available memory)
• Perform a backup of this database
• The backup “The query processor ran out of stack space during query optimization. Please simplify the query.” error is encountered

The error is visible in LiteSpeed logs.

The problematic query is also logged and it contains a large switch-case block (when x then y) with >2000 lines (one line for each existing filegroup).

Because the block is so large, the query optimizer runs out of resources when processing the query.

Product Defect - LS-2510

The issue has been fixed the latest hotfix release for LiteSpeed for SQL Server Hotfix 8.9.8.65.

You can download the installer from here.