This article is contributed. See the original author and article here.

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.


 



  1. 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 FilesMicrosoft SQL ServerMSSQL15.<InstanceName>MSSQLLogPolybasedump.


 


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 FilesMicrosoft SQL ServerMSSQL15.<InstanceName>MSSQLLog) 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


PowerShellif 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 FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLBinnPolybasempdwsvc.exe


15.0.2000.5    2019.0150.2000.05 ((SQLServer).190924-2033) C:Program FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLBinnPolybasempdwsvc.exe


 


PowerShellif PolyBase Services aren’t running, run the following command:


 


cd ‘C:Program FilesMicrosoft 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 FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLBinnPolybasempdwsvc.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



PolybaseCorePolybaseJava



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.



  1. 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.


 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

%d bloggers like this: