Freigeben über

Clicking on the XML link containing a query plan doesn’t display the graphical plan but opens the XML editor instead

  • Artikel

Recent Note: SQL Server 2008 R2 Service Pack 1 Cumulative Update 4 (10.50.2796.0) is the first build servicing showplanxml.xsd with the updated version. So, install that CU to get rid of this problem.

Last week, while working with a customer in the optimization of some of his queries, he raised to my attention that his SQL Server Management Studio wasn’t experiencing some weird behavior when clicking on a grid’s cell which contained the XML representation of a query plan.

He said, sometimes it worked as expected, by displaying the graphical representation of the query plan, while some others it opened the raw XML in the XML editor. He had not been able to figure out under which circumstances it decided to show one or the other.

I launched an instance of SSMS (the one installed by SQL Server 2008 R2) that I had updated to Service Pack 1, and ran the following three batches after having configured SSMS to show me the results as a grid (Ctrl+D):

set showplan_xml on
go
select * from sys.sysaltfiles
go
set showplan_xml off

ShowplanCell

 

I clicked on the resulting hyperlink and, guess what?, it opened the raw XML in the XML editor as well:

ShowplanXMLEditor

In order to determine what was going on, I attached a debugger to the instance of SSMS, and found that when one clicks on the XML cell, SSMS uses the System.Xml.XmlValidatingReader class to validate the instance of the XML document against the schema locally stored under %programfiles(x86)%\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd (%programfiles% instead of %programfiles(x86)%, if SSMS is installed in a 32-bit OS). If the document is valid, it is considered to be a query plan which can be rendered by the graphical plan editor, so it is saved to a temporary file which is then passed to the graphical editor for display. If it is invalid though, it is passed to the XML Editor to show its raw XML contents.

The question that we had to answer then was: Why would such plan be invalid?

I noticed that during the parsing, a System.Xml.Schema.XmlSchemaException exception was being thrown at this particular point:

System.Xml.Schema.SchemaInfo.GetAttributeXsd(...)
System.Xml.Schema.XsdValidator.ValidateStartElement()
System.Xml.Schema.XsdValidator.ProcessElement(...)
System.Xml.Schema.XsdValidator.ValidateElement()
System.Xml.Schema.XsdValidator.Validate()
System.Xml.XmlValidatingReaderImpl.ProcessCoreReaderEvent()
System.Xml.XmlValidatingReaderImpl.Read()
System.Xml.XmlValidatingReader.Read()
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.GridResultsTabPage.IsShowPlanXml(...)
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.GridResultsTabPage.HandleXMLCellClick(...)
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.GridResultsTabPage.OnSpecialGridEvent(...)
Microsoft.SqlServer.Management.UI.Grid.GridControl.OnGridSpecialEvent(...)
Microsoft.SqlServer.Management.UI.Grid.GridControl.HandleHyperlinkLBtnUp(...)
Microsoft.SqlServer.Management.UI.Grid.GridControl.ProcessLeftButtonUp(...)
Microsoft.SqlServer.Management.UI.Grid.GridControl.OnMouseUp(...)
System.Windows.Forms.Control.WmMouseUp(...)
System.Windows.Forms.Control.WndProc(...)

And its message contained the following revealing text: The 'ForceScan' attribute is not declared.

What was going on? Was SQL Server (its Relational Engine to be more precise) producing an instance of a showplan where one entity included an attribute named ForceScan that was not declared in the schema defined in the showplanxml.xsd file on disk? And the answer is: Yes, indeed!

It happens that Service Pack 1 for SQL Server 2008 R2 includes, among other check-ins one that ports back code to include some new information exposed via new attributes for some existing iterators of a query plan. One of them being ForceScan, which is a boolean, optional attribute that can be produced by table scans (TableScanType in the XSD) and indexes scans (IndexScanType in the XSD). The second new attribute that could show up in a query plan is named ForceSeekColumnCount, is also boolean, and optional, and is part of an index scan (IndexScanType in the XSD).

The glitch is that either SQL_ENGINE_CORE_SHARED.MSP or SQL_SSMS.MSP transforms, included in Service Pack 1, should have serviced the XSD file with the new version that contains these new attributes declared but it didn’t.

And it didn’t, not because my Service Pack 1 installation failed or was incomplete, it just didn’t because of a human mistake in the process of publishing those changes to the location from where the build consumes that file to include it in the Service Pack or to publish it publicly with all other public schemas. Therefore, the one version published under http://schemas.microsoft.com/sqlserver/2004/07/showplan/showplanxml.xsd is also incomplete because it also misses those attributes.

I have reported this problem already and will hopefully be fixed in the next Public Cumulative Update.

Recent Note: SQL Server 2008 R2 Service Pack 1 Cumulative Update 4 (10.50.2796.0) is the first build servicing showplanxml.xsd with the updated version. So, install that CU to get rid of this problem.

Comments

  • Anonymous
    November 07, 2012
    Nice debug job :-). Had a headache with this one as well. One annotation from my side: If you can't install SP2 rightaway, locate the "sqlplanxml.xsd" file somewhere near .. (instdrive) <program files x(86)Microsoft SQLServer100ToolsBinnschemassqlserver2004�7showplan Make a backup, copy the file from (e.g.) an SQL2008R2SP2 Server, or replace from the contents in the link above, where the schema definiton is maintained by now. Should work after that withount any restart.