PolyBase error - 100001;Failed to generate query plan.

[NathanMSFT]

Problem

We've seen several cases come in lately where customers have been trying to use PolyBase feature and encountering "Failed to generate query plan" error. Depending on which command you run, the error will display differently.

1. CREATE EXTERNAL TABLE or CREATE EXTERNAL DATA SOURCE command fails with:

Msg 110813, Level 16, State 1, Line 21

100001;Failed to generate query plan.

2. SELECT from an existing external table fails with:

Msg 7320, Level 16, State 110, Line 1

Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". 100001;Failed to generate query plan.

In either of the above scenarios, if you open the <ServerName>_<InstanceName>_DWEngine_errors.log, you'll see an error like the following:

{datetime} [Thread:<ThreadID>] [ServerInterface:InformationEvent] (Info, Normal): Starting processor ExecuteMemoProcessor. [Session.SessionId:SID##][Session.IsTransactional:False][Query.QueryId:QID##]

{datetime} [Thread:<ThreadID>] [EngineInstrumentation:EngineQueryErrorEvent] (Error, High):

Microsoft.SqlServer.DataWarehouse.Common.ErrorHandling.UnexpectedStatementException: 100001;Failed to generate query plan. ---> Microsoft.SqlServer.DataWarehouse.Sql.Optimizer.MemoDeserializer.UnknownElementException: Unknown element DatabaseUser is found.

   at Microsoft.SqlServer.DataWarehouse.Sql.Optimizer.MemoDeserializer.MemoDeserializer.ShowMemoXMLState.HandleState(XmlReader reader, MemoDeserializer deserializer)

   at Microsoft.SqlServer.DataWarehouse.Sql.Optimizer.MemoDeserializer.MemoDeserializer.Deserialize(XmlReader reader)

   at Microsoft.SqlServer.DataWarehouse.Sql.Optimizer.MemoProvider.AbstractMemoGenerator.DeserializeMemoFromXML(SqlXml memoXml, ExecutionEnvironment executionEnvironment)

   --- End of inner exception stack trace ---

   at Microsoft.SqlServer.DataWarehouse.Sql.Optimizer.MemoProvider.AbstractMemoGenerator.DeserializeMemoFromXML(SqlXml memoXml, ExecutionEnvironment executionEnvironment)

   at Microsoft.SqlServer.DataWarehouse.Sql.Statements.OptimizedStatement.GenerateMemo(IMemoProvider memoProvider, IQPTelemetry queryProcessingTelemetry, Boolean isLocalShellSession)

   at Microsoft.SqlServer.DataWarehouse.Engine.Utils.EventUtils.PublishApplicationEventAndExecute(ApplicationEventTrigger beginTrigger, ApplicationEventTrigger endTrigger, ApplicationEventTrigger errorTrigger, ApplicationEventTrigger cancelTrigger, PublishedEventPayloadDelegate payload, Action callback)

   at Microsoft.SqlServer.DataWarehouse.Engine.Processors.ExecuteMemoProcessor.OnExecuteRequest()

   at Microsoft.SqlServer.DataWarehouse.Engine.Utils.EventUtils.PublishApplicationEventAndExecute(ApplicationEventTrigger beginTrigger, ApplicationEventTrigger endTrigger, ApplicationEventTrigger errorTrigger, ApplicationEventTrigger cancelTrigger, PublishedEventPayloadDelegate payload, Action callback)

   at Microsoft.SqlServer.DataWarehouse.Engine.Processors.AbstractProcessor.OnProcess()

   at Microsoft.SqlServer.DataWarehouse.Engine.Processors.AbstractProcessor.OnExecute() [Session.SessionId:SID##][Session.IsTransactional:False][Query.QueryId:QID##]

You may also observe a memory dump file (SQLDmpr*.dmp) created in SQLServerInstallDrive:\Program Files\Microsoft SQL Server\MSSQL15.<InstanceName>\MSSQL\Log\Polybase\dump.

The problem has only been observed in SQL Server 2019 on Windows.

Cause

The problem occurs when SQL Server Engine has been patched to at least Cumulative Update 8 (15.0.4073) and the PolyBase feature hasn't been updated to the same build.

The most common way of encountering this problem is to already have installed SQL Server 2019 and patched to CU8 and then subsequently add the PolyBase feature. When you add a feature to an existing SQL Server instance that has been patched, the feature added is still at the original RTM version. This isn't specific to PolyBase feature, but any feature added to an existing instance that has been patched. This would lead to problem with being unable to create the external table.

In order to get the error when selecting from the external table, you must have already successfully created the external table. We've seen this scenario when there's been some problem applying Cumulative Update 8 to the PolyBase feature, but installation of CU8 to the SQL Engine was successful. In scenarios like this, we've seen customers have uninstalled the PolyBase feature and reinstalled it, but then failed to subsequently apply CU8 to PolyBase feature.

How to Confirm

You must determine the SQL Server Engine version and PolyBase Engine version and compare.

Determine SQL Server Engine version.

This can be done a few different ways.

Check errorlog - at the top of the file errorlog (which you can find in SQLServerInstallDrive:\Program Files\Microsoft SQL Server\MSSQL15.<InstanceName>\MSSQL\Log) the first line in the file will show the version of SQL Server Engine. For example:

{datetime} Server      Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64)

Connect to SQL Server and run the query

SELECT @@VERSION as SQLEngineVersion

The output will look something like:

SQLEngineVersion

------------------------------------------------------------------------------------------------------------

Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64)

Sep 23 2020 16:03:08

Copyright (C) 2019 Microsoft Corporation

Developer Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)

Determine PolyBase Engine version

PowerShell - if PolyBase Services are running, run the following command:

Get-Process mpdwsvc -FileVersionInfo | Format-Table -AutoSize

The output will look something like:

ProductVersion FileVersion                                 FileName                                                                                

-------------- -----------                                 --------                                                                                 

15.0.2000.5    2019.0150.2000.05 ((SQLServer).190924-2033) C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\Polybase\mpdwsvc.exe

15.0.2000.5    2019.0150.2000.05 ((SQLServer).190924-2033) C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\Polybase\mpdwsvc.exe

PowerShell - if PolyBase Services aren't running, run the following command:

cd 'C:\Program Files\Microsoft SQL Server'

ls mpdwsvc.exe -r -ea silentlycontinue | % versioninfo | Format-Table -AutoSize

The output will look something like:

ProductVersion FileVersion                                 FileName                                                                                 

-------------- -----------                                 --------                                                                                

15.0.2000.5    2019.0150.2000.05 ((SQLServer).190924-2033) C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\Polybase\mpdwsvc.exe

If for some reason the two above examples don't work, you can use the original setup media and run SQL Discovery

1. Start SQL Server setup (setup.exe)
2. Click on Tools in left pane
3. Click Installed SQL Server features discovery report. It will generate a Setup Discovery Report that will look something like this:

Microsoft SQL Server 2019 Setup Discovery Report

Product	Instance	Instance ID	Feature	Language	Edition	Version	Clustered	Configured
Microsoft SQL Server 2019	MSSQLSERVER	MSSQL15.MSSQLSERVER	Database Engine Services	1033	Developer Edition	15.0.4073.23	No	Yes
Microsoft SQL Server 2019	MSSQLSERVER	MSSQL15.MSSQLSERVER	SQL Server Replication	1033	Developer Edition	15.0.4073.23	No	Yes
Microsoft SQL Server 2019	MSSQLSERVER	MSSQL15.MSSQLSERVER	PolyBase Query Service for External Data	1033	Developer Edition	15.0.2000.5	No	Yes
Microsoft SQL Server 2019	MSSQLSERVER	MSSQL15.MSSQLSERVER	PolybaseCore\PolybaseJava	1033	Developer Edition	15.0.2000.5	No	Yes
Microsoft SQL Server 2019	MSSQLSERVER	MSSQL15.MSSQLSERVER	Azul-Java-Runtime	1033	Developer Edition	15.0.2000.5	No	Yes

You can check KB4518398 - SQL Server 2019 build versions (microsoft.com) to see which ProductVersion value corresponds to which Cumulative Update.

Compare Versions

If the versions don't match and PolyBase Engine version is less than SQL Server Engine, and SQL Server Engine is at least 15.0.4073, then you have confirmed the problem is due to not having applied the same Cumulative Update to PolyBase feature.

Resolution

To resolve this issue, you need to apply the same Cumulative Update to PolyBase features that SQL Engine is already on.

Additional Information

1. In Cumulative Update 8 there was a change made to the XML memo that is sent from SQL Server Engine to PolyBase Engine. If the PolyBase Engine is on a build prior to CU8, it will be unable to "deserialize" the memo and throw this error because it cannot generate a query plan.

2. In general, any time any feature is added to an existing SQL Server instance that has been patched, you need to reapply the same patch to bring the feature to same build.