使用 SQL Server 2005 中的 SQLCLR 处理 XML Showplan

核心提示： 图 3. 第二个解决方案的示意处理步骤图 3 概述该解决方案的处理步骤，在此方法中应强调两个重点：•在进程内执行对 XML showplan 的提取，使用 SQL Server 2005 中的 SQLCLR 处理 XML Showplan(8)，与解决方案 1 相同，注 使用 X

图 3. 第二个解决方案的示意处理步骤

图 3 概述该解决方案的处理步骤。在此方法中应强调两个重点：

•

在进程内执行对 XML showplan 的提取，与解决方案 1 相同。

注 使用 XQuery 表达式的查询成本提取并没有使用进程内数据访问，这是由于已将 showplan 发送到客户端进程，并且该客户端已重新发送 showplan，并且从 showplan 中提取查询成本的 XQuery 表达式。

•

对于进程外数据访问，SQL Server 还支持 XQuery 查询，而不仅仅是 XPath 查询。因此，可以使用更多的表述性查询来处理 XML showplan。此方法不如解决方案 1 中所使用的方法有效，因为 showplan 通过连接发送了两次。

小结

通过 SQL Server 2005 SQLCLR 功能，可使用 XPath 或 XQuery 语言处理 XML 格式的 Showplan。由于 XPath 和 XQuery 引擎内置于 SQL Server 2005，因此可以在它们和 Transact-SQL 之间形成无缝集成。实现 CLR 用户定义的过程以作为 Transact-SQL 和 XPath 或 XQuery 之间链接的 Visual C# 代码相对简单。SQLCLR 极大地扩展了 Transact-SQL 的功能，并且可以使用诸如 Visual C# 和 Visual Basic .NET 之类的过程性语言有效地实现 CPU 密集的计算。

附录 A：“ShowplanXPath.cs”的代码清单（解决方案 1）

using System;
using System.IO;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.XPath;
public class xmlshowplanaccess　
{
　public static void GetXMLShowplan(string tsqlStmt, ref string tsqlStmtCost)　
　{
　　　// tsqlStmt contains the query whose cost needs to be calculated
　　　// tsqlStmtCost will contain the tsqlStmt's cost
　　　// Open a connection and create a command
　　　SqlConnection conn = new SqlConnection("context connection = true");
　　　conn.Open();
　　　SqlCommand cmd = conn.CreateCommand();
　　　cmd.CommandText = "set showplan_xml on";
　　　cmd.ExecuteNonQuery();　// turns showplan_xml mode on
　　　cmd.CommandText = tsqlStmt;
　　　try {
　　　　　// thePlan will contain the showplan in XMLformat
　　　　　String thePlan = String.Empty;
　　　　　SqlDataReader sdr = cmd.ExecuteReader();
　　　　　// In case the result set is chunked, we concatenate
　　　　　while (sdr.Read()) thePlan += sdr.GetSqlString(0).ToString();
　　　　　sdr.Close();
　　　　　cmd.CommandText = "set showplan_xml off";
　　　　　cmd.ExecuteNonQuery();　// turns showplan_xml mode off
　　　　　// Now the showplan in XMLformat is contained in thePlan.
　　　　　// We shall now evaluate an XPathexpression against the showplan.
　　　　　StringReader strReader = new StringReader(thePlan);
　　　　　System.Xml.XmlTextReader xreader =　
　　　　　　　new System.Xml.XmlTextReader(strReader);
　　　　　XPathDocument doc = new XPathDocument(xreader, XmlSpace.Preserve);
　　　　　System.Xml.XPath.XPathNavigator navigator = doc.CreateNavigator();
　　　　　XmlNamespaceManager nsmgr = new XmlNamespaceManager(navigator.NameTable);
　　　　　nsmgr.AddNamespace("sql", "http://schemas.microsoft.com/sqlserver/2004/07/showplan");
　　　　　// The exact namespace will depend on the showplan's version.
　　　　　// Please modify the year and month appropriately.
　　　　　XPathExpression xpression;
　　　　　// The XPaththat points to the estimated execution cost of the query
　　　　　xpression =　
　　　　　　　navigator.Compile("//sql:Batch/sql:Statements/sql:StmtSimple/"　
　　　　　　　+ "sql:QueryPlan[1]/sql:RelOp[1]/@EstimatedTotalSubtreeCost");
　　　　　xpression.SetContext(nsmgr);
　　　　　XPathNodeIterator iterator = navigator.Select(xpression);
　　　　　String val = String.Empty;
　　　　　System.Single totalCost = 0;
　　　　　// sum costs of all query plans in this batch
　　　　　while(iterator.MoveNext()) totalCost += Single.Parse(iterator.Current.Value);
　　　　　tsqlStmtCost = totalCost.ToString();　// set the return value
　　　} catch (SqlException) { // return -1 if there are any errors in SQL code
　　　　　tsqlStmtCost = "-1";
　　　}
　}　// GetXMLShowplan ends
}　// xmlshowplanaccess class ends

附录 B：“ReturnShowplanXML.cs”的代码清单（解决方案 2）