Returning Values From Query Plans Using C#

Hello and welcome to yet another “Ronseal” title for a post…. and whilst this may not be something you’ll have to do regularly, searching for values in a query plan may be useful when running unit tests for SQL: you may be using it to confirm that a certain operator is used in the query plan, or whether a seek or scan is used… the possibilities are really endless. To return the query plan you can turn on the showplan_xml statement to return detailed information on how a particular batch would be executed without actually executing it. It would be better to turn on SHOWPLAN_XML and execute on a per batch basis to make the process of navigating through the query plan easier. This is the code to return the query plan:


using System;
using System.Data.SqlClient;

namespace UnitTestFrameWork
{
public class GetQueryPlan
{
public string GetQueryPlanForQuery(string conn, string batch)
{
SqlConnection _conn = new SqlConnection(conn);
_conn.Open();
SqlCommand cmd = _conn.CreateCommand();

cmd.CommandText = "SET SHOWPLAN_XML ON";
cmd.ExecuteNonQuery();
cmd.CommandText = batch;
try
{
String QueryPlan = String.Empty;
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read()) QueryPlan += sdr.GetSqlString(0).ToString();
sdr.Close();
cmd.CommandText = "SET SHOWPLAN_XML OFF";
cmd.ExecuteNonQuery();
return QueryPlan;
}

catch (SqlException)
{
string e = "Query plan could not be returned.";
return e;
}
}
}
}

Now that we have the query plan, we want to be able to navigate it to retrieve a single value, or a sum of values. As a query plan is an xml file, we can use xpath to navigate the nodes and return the value(s) we want.


using System;
using System.Xml;
using System.Xml.XPath;
using System.IO;

namespace UnitTestFrameWork
{
public class GetQueryPlanValues
{

public XPathNodeIterator GetXpathIterator(string QueryPlan, string SqlNamespace, string xpath)
{
//read query plan to tring, then xml reader
StringReader strReader = new StringReader(QueryPlan);
XmlTextReader xreader = new XmlTextReader(strReader);

//xpath document from the xreader, preserving whitespace,
//then a nvaigator so that we can search using xpath
XPathDocument doc = new XPathDocument(xreader, XmlSpace.Preserve);
XPathNavigator navigator = doc.CreateNavigator();

//define namespace
XmlNamespaceManager nsmgr = new XmlNamespaceManager(navigator.NameTable);
nsmgr.AddNamespace("sql", SqlNamespace);

//create xpath expression to search
XPathExpression xpression;
xpression = navigator.Compile(xpath);
//set namespace in the expression
xpression.SetContext(nsmgr);
//iterate over the nodes
XPathNodeIterator iterator =  navigator.Select(xpression);
return iterator;
}


public string GetSumOfValues(string QueryPlan, string SqlNamespace, string xpath)
{
try
{

string _QueryPlan = QueryPlan;
string _SqlNameSpace = SqlNamespace;
string _xpath = xpath;

XPathNodeIterator i = GetXpathIterator(_QueryPlan, _SqlNameSpace, _xpath);

//get total value of each of the expressions we are looking for in the node
Single TotalValue = 0;
while (i.MoveNext()) TotalValue += Single.Parse(i.Current.Value);
string ret = String.Empty;
ret = Convert.ToString(TotalValue);
return ret;
}
catch (Exception e)
{
string error = e.Message;
return "-1";
}
}

public string GetSingleValue(string QueryPlan, string SqlNamespace, string xpath)
{
try
{
string _QueryPlan = QueryPlan;
string _SqlNameSpace = SqlNamespace;
string _xpath = xpath;

XPathNodeIterator i = GetXpathIterator(_QueryPlan, _SqlNameSpace, _xpath);

string ret = String.Empty;
while (i.MoveNext()) ret = i.Current.Value;
return ret;
}
catch (Exception e)
{
string error = e.Message;
return "-1";
}
}
}
}

The “SumOfValues” could be used to return a single value if the xpath is specific enough, however it is only useful for single data types in this respect, so it is best to use the “GetSingleValue” example when using wanting a single value.

To demonstrate, I’m going to create a library called “QueryPlanFrameWork” in Visual Studio using the code samples above and reference this in a MSTest Unit Test project. I’m also going to add a TestScript with some SQL batches and add that to the project. The TestScript.sql contains the following batches:


USE NORTHWND
GO

SELECT * FROM dbo.Categories c
GO

SELECT c.CategoryName, s.CompanyName, s.ContactName, s.Phone, p.ProductName FROM dbo.[Order Details] od
INNER JOIN dbo.Products p ON p.ProductID = od.ProductID
INNER JOIN dbo.Suppliers s    ON s.SupplierID = p.SupplierID
INNER JOIN    dbo.Categories c    ON    c.CategoryID = p.CategoryID
WHERE    c.CategoryName    = 'Condiments'
AND p.Discontinued    = 0
GROUP BY c.CategoryName, s.CompanyName, s.ContactName, p.ProductName,s.Phone

The Northwind database is available on the CodePlex site. For those of you following at home, the Solution Explorer for the Unit Test project would now look something like this:

reference

Then we’ll use the “QueryPlanFrameWork” library to extract the query plan and ensure that the expected values are the actual values. For example, I may want to check that the two non clustered indexes we access are seeks and not scans (we could want to do this to verify that indexes that are frequently updated are only used as seeks to prevent locking). We also check the total sub-tree cost of the two operations and verify that they are below 0.01, an arbitrary number just for the purposes of demoing the “SumOf” method. If I was running this as a proper test I’d first force a scan by making the query non-sargable and getting SumOf the scans and ensure that this value is greater than the SumOf seeks.


using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Data;
using System.IO;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using QueryPlanFrameWork;

namespace DemoUnitTest
{
    [TestClass]
    public class RunTest
    {
        [TestMethod]
        public void RunTestScriptTestMethod()
        {
            GetQueryPlan qp = new GetQueryPlan();
            GetQueryPlanValues qv = new GetQueryPlanValues();

            string constring_1 = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=NORTHWND;Data Source=.";
            string fileContent = File.ReadAllText(@"..\\..\\TestScript\\TestScript.sql");
            string[] batches = Regex.Split(fileContent, "GO");
            string SqlNamespace = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
            string XPath = "//sql:Batch/sql:Statements/sql:StmtSimple/sql:QueryPlan/sql:RelOp/@PhysicalOp";
            string XPath_2 = "//sql:Batch/sql:Statements/sql:StmtSimple/sql:QueryPlan/sql:RelOp//sql:StreamAggregate/sql:RelOp/sql:NestedLoops/sql:RelOp[1]/sql:Sort/sql:RelOp/sql:NestedLoops/sql:RelOp/sql:NestedLoops/sql:RelOp/sql:NestedLoops/sql:RelOp[1]/@PhysicalOp";
            string XPath_3 = "//sql:Batch/sql:Statements/sql:StmtSimple/sql:QueryPlan/sql:RelOp//sql:StreamAggregate/sql:RelOp/sql:NestedLoops/sql:RelOp[1]/sql:Sort/sql:RelOp/sql:NestedLoops/sql:RelOp/sql:NestedLoops/sql:RelOp/sql:NestedLoops/sql:RelOp[2]/@PhysicalOp";
            string XPath_4 = "//sql:Batch/sql:Statements/sql:StmtSimple/sql:QueryPlan/sql:RelOp//sql:StreamAggregate/sql:RelOp/sql:NestedLoops/sql:RelOp[1]/sql:Sort/sql:RelOp/sql:NestedLoops/sql:RelOp/sql:NestedLoops/sql:RelOp/sql:NestedLoops/sql:RelOp/@EstimatedTotalSubtreeCost";

            for (int i = 0; i < batches.Length; i++)
            {
                string _conn = constring_1;
                string batch = batches[i];
                SqlDataAdapter da = new SqlDataAdapter(batch, _conn);
                DataSet ds = new DataSet();

                try
                {
                    da.Fill(ds);
                }
                catch (Exception e)
                {
                    string Error = e.Message;
                }
                da.Dispose();

                DataTable dt = ds.Tables["Table"];

                if (i == 2)
                {
                    string QueryPlan = qp.GetQueryPlanForQuery(constring_1, batch);
                    string _operator = qv.GetSingleValue(QueryPlan, SqlNamespace, XPath);
                    Assert.AreEqual("Stream Aggregate", _operator);
                    string _operator_2 = qv.GetSingleValue(QueryPlan, SqlNamespace, XPath_2);
                    Assert.AreEqual("Index Seek", _operator_2);
                    string _operator_3 = qv.GetSingleValue(QueryPlan, SqlNamespace, XPath_3);
                    Assert.AreEqual("Index Seek", _operator_3);
                    string _operator_4 = qv.GetSumOfValues(QueryPlan, SqlNamespace, XPath_4);
                    Assert.IsTrue(0.01 > Convert.ToSingle(_operator_4));
                }
            }
        }
    }
}

XPath is the bane of my life… it takes a while to find the correct value particularly as there are so many node names that are re-used yet embedded into them. So I added a simple example and a couple with more depth. Nevertheless, running the test should produce a green result. This could be used for more than just testing; DBA’s may find it useful in SQLCLR scenarios.

I’ve uploaded the code to GitHub. The repository is called XQueryPlanPath.

If you have any questions/ideas/improvements then leave a comment below, or DM me on Twitter, or take a clone of the repo, make the changes and let me know!

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

4 thoughts on “Returning Values From Query Plans Using C#”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s