Programmatic Analysis of SQL Server Execution Plan

Developers generally display the execution plan on SQL Server Management Studio visually. (Most known method: Select name of the procedure and press CTRL + L). SQL Server Database Engine serves execution plans in XML format. The images you see for plan in Management Studio is the interpretation of this XML string.

This XML formatted execution plan can be examined programmatically in order to check for certain operations.

Scenario: For a given stored procedure, I want to determine if the proc performs table scan or index scan operations on large tables.

In SQL Server connection, queries which are executed after "SET SHOWPLAN_XML ON" are not executed, but execution plan is created only.

Here is the class that retrieves Execution Plan XML of a stored proc.

Code:

static class ExecutionPlanRetriever
    {
        public static string Retrieve(string spName, string connectionString)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    using (SqlCommand cmd = GetCommandWithParams(spName, connectionString))
                    {
                        conn.Open();
                        cmd.Connection = conn;
                        SetShowPlanOn(conn);
                        SetCommandParamsByDefault(cmd);
                        cmd.CommandTimeout = 0;
                        string planStr = cmd.ExecuteScalar().ToString();
                        return planStr;
                    }
                }
                catch (Exception e)
                {
                }
            }
            return null;
        }

        static SqlCommand GetCommandWithParams(string spName, string connectionString)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(spName, conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                SqlCommandBuilder.DeriveParameters(cmd);
                return cmd;
            }
        }

        static void SetShowPlanOn(SqlConnection conn)
        {
            string cmdText = "SET SHOWPLAN_XML ON";
            using (SqlCommand cmd = new SqlCommand(cmdText, conn))
            {
                cmd.ExecuteNonQuery();
            }
        }

        static void SetCommandParamsByDefault(SqlCommand cmd)
        {
            foreach (SqlParameter prm in cmd.Parameters)
            {
                object val = GetDefaultSqlTypeValue(prm.SqlDbType);
                if (val is DataTable)
                {
                    prm.TypeName = ((DataTable)val).TableName;
                }
                prm.Value = val;
            }
        }

        static object GetDefaultSqlTypeValue(SqlDbType dbType)
        {
            object val = null;
            switch (dbType)
            {
                case System.Data.SqlDbType.BigInt: val = 0;
                    break;
                case System.Data.SqlDbType.Binary: val = val = new byte[1] { 0 };
                    break;
                case System.Data.SqlDbType.Bit: val = 0;
                    break;
                case System.Data.SqlDbType.Char: val = 'A';
                    break;
                case System.Data.SqlDbType.Date: val = new DateTime(2010, 01, 01);
                    break;
                case System.Data.SqlDbType.DateTime: val = new DateTime(2010, 01, 01);
                    break;
                case System.Data.SqlDbType.DateTime2: val = new DateTime(2010, 01, 01);
                    break;
                case System.Data.SqlDbType.DateTimeOffset: val = null;
                    break;
                case System.Data.SqlDbType.Decimal: val = 0;
                    break;
                case System.Data.SqlDbType.Float: val = 0;
                    break;
                case System.Data.SqlDbType.Image: val = null;
                    break;
                case System.Data.SqlDbType.Int: val = 0;
                    break;
                case System.Data.SqlDbType.Money: val = 0;
                    break;
                case System.Data.SqlDbType.NChar: val = string.Empty;
                    break;
                case System.Data.SqlDbType.NText: val = string.Empty;
                    break;
                case System.Data.SqlDbType.NVarChar: val = string.Empty;
                    break;
                case System.Data.SqlDbType.Real: val = 0;
                    break;
                case System.Data.SqlDbType.SmallDateTime: val = new DateTime(2010, 01, 01);
                    break;
                case System.Data.SqlDbType.SmallInt: val = 0;
                    break;
                case System.Data.SqlDbType.SmallMoney: val = 0;
                    break;
                case System.Data.SqlDbType.Text: val = string.Empty;
                    break;
                case System.Data.SqlDbType.Time: val = new DateTime(2010, 01, 01);
                    break;
                case System.Data.SqlDbType.Timestamp: val = new DateTime(2010, 01, 01);
                    break;
                case System.Data.SqlDbType.TinyInt: val = 0;
                    break;
                case System.Data.SqlDbType.UniqueIdentifier: val = string.Empty;
                    break;
                case System.Data.SqlDbType.VarBinary: val = new byte[1] { 0 };
                    break;
                case System.Data.SqlDbType.VarChar: val = string.Empty;
                    break;
                case System.Data.SqlDbType.Variant: val = null;
                    break;
                case System.Data.SqlDbType.Xml: val = null;
                    break;
                default:
                    break;
            }
            return val;
        }
    }

Now, once the XML string is ready, I can search for certain operations by using XPath.
Assuming we have the list of large tables, we can determine risky scan operations as:

Code:

    class DbObjectName
    {
        public string Schema { get; set; }
        public string Name { get; set; }
    }

    static class ExecutionPlanChecker
    {
        static DbObjectName[] TableIndexScanRiskTables = new DbObjectName[] {
            new DbObjectName { Schema = "SAMPLE_SCHEMA", Name = "SAMPLE_TABLE" }, 
        };

        // Resource for scan types:
        //http://technet.microsoft.com/en-us/library/ms186954(v=sql.105).aspx
        static readonly string[] riskyOperationNames = new string[] { "Table Scan", "Index Scan", "Clustered Index Scan" };

        public static bool PlanHasRiskyScanOperation(string planXml)
        {
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(planXml);
            doc = StripNamespace(doc);
            foreach (var opName in riskyOperationNames)
            {
                if (HasRiskyScanOperation(doc, opName))
                {
                    return true;
                }
            }
            return false;
        }

        static XmlDocument StripNamespace(XmlDocument doc)
        {
            if (doc.DocumentElement.NamespaceURI.Length > 0)
            {
                doc.DocumentElement.SetAttribute("xmlns", string.Empty);
                XmlDocument newDoc = new XmlDocument();
                newDoc.LoadXml(doc.OuterXml);
                return newDoc;
            }
            else
            {
                return doc;
            }
        }

        static bool HasRiskyScanOperation(XmlDocument doc, string scanOperationName)
        {
            string pattern = string.Format("//RelOp[@LogicalOp='{0}']", scanOperationName);
            XmlNodeList nodeList = doc.SelectNodes(pattern);
            foreach (XmlNode node in nodeList)
            {
                if (NodeTargetsRiskyTable(node))
                {
                    return true;
                }
            }
            return false;
        }
        
        static bool NodeTargetsRiskyTable(XmlNode node)
        {
            foreach (var item in TableIndexScanRiskTables)
            {
                string pattern = string.Format(@"OutputList/ColumnReference[@Schema='[{0}]' and @Table='[{1}]']", item.Schema, item.Name);
                if (node.SelectNodes(pattern).Count > 0)
                {
                    return true;
                }
            }
            return false;
        }
    }

Here, the client code calls "PlanHasRiskyScanOperation" function by passing the execution plan.