Execution plan warnings–The final chapter

Posted by Dave Ballantyne on SQL Blogcasts See other posts from SQL Blogcasts or by Dave Ballantyne
Published on Tue, 29 Nov 2011 10:42:03 GMT Indexed on 2011/11/29 17:55 UTC
Read the original article Hit count: 307

Filed under:

In my previous posts (here and here), I showed examples of some of the execution plan warnings that have been added to SQL Server 2012. There is one other warning that is of interest to me : “Unmatched Indexes”.

Firstly, how do I know this is the final one ? The plan is an XML document, right ? So that means that it can have an accompanying XSD. As an XSD is a schema definition, we can poke around inside it to find interesting things that *could* be in the final XML file.

The showplan schema is stored in the folder Microsoft SQL Server\110\Tools\Binn\schemas\sqlserver\2004\07\showplan and by comparing schemas over releases you can get a really good idea of any new functionality that has been added.

Here is the section of the Sql Server 2012 showplan schema that has been interesting me so far :

<xsd:complexType name="AffectingConvertWarningType">
    <xsd:annotation>
        <xsd:documentation>Warning information for plan-affecting type conversion</xsd:documentation>
    </xsd:annotation>
    <xsd:sequence>
        <!-- Additional information may go here when available -->
    </xsd:sequence>
    <xsd:attribute name="ConvertIssue" use="required">
        <xsd:simpleType>
            <xsd:restriction base="xsd:string">
                <xsd:enumeration value="Cardinality Estimate" />
                <xsd:enumeration value="Seek Plan" />
                <!-- to be extended here -->
            </xsd:restriction>
        </xsd:simpleType>
    </xsd:attribute>
    <xsd:attribute name="Expression" type ="xsd:string" use="required" />
</xsd:complexType>
<xsd:complexType name="WarningsType">
    <xsd:annotation>
        <xsd:documentation>List of all possible iterator or query specific warnings (e.g. hash spilling, no join predicate)</xsd:documentation>
    </xsd:annotation>
    <xsd:choice minOccurs="1" maxOccurs="unbounded">
        <xsd:element name="ColumnsWithNoStatistics" type="shp:ColumnReferenceListType" minOccurs="0" maxOccurs="1" />
        <xsd:element name="SpillToTempDb" type="shp:SpillToTempDbType" minOccurs="0" maxOccurs="unbounded" />
        <xsd:element name="Wait" type="shp:WaitWarningType" minOccurs="0" maxOccurs="unbounded" />
        <xsd:element name="PlanAffectingConvert" type="shp:AffectingConvertWarningType" minOccurs="0" maxOccurs="unbounded" />
    </xsd:choice>
    <xsd:attribute name="NoJoinPredicate" type="xsd:boolean" use="optional" />
    <xsd:attribute name="SpatialGuess" type="xsd:boolean" use="optional" />
    <xsd:attribute name="UnmatchedIndexes" type="xsd:boolean" use="optional" />
    <xsd:attribute name="FullUpdateForOnlineIndexBuild" type="xsd:boolean" use="optional" />
</xsd:complexType>

I especially like the “to be extended here” comment, high hopes that we will see more of these in the future.

So “Unmatched Indexes” was a warning that I couldn’t get and many thanks must go to Fabiano Amorim (b|t) for showing me the way.

Filtered indexes were introduced in Sql Server 2008 and are really useful if you only need to index only a portion of the data within a table. However, if your SQL code uses a variable as a predicate on the filtered data that matches the filtered condition, then the filtered index cannot be used as, naturally, the value in the variable may ( and probably will ) change and therefore will need to read data outside the index. As an aside, you could use option(recompile) here , in which case the optimizer will build a plan specific to the variable values and use the filtered index, but that can bring about other problems.

To demonstrate this warning, we need to generate some test data :

DROP TABLE #TestTab1
GO
CREATE TABLE #TestTab1 (Col1     Int not null,
                       Col2     Char(7500) not null,
                       Quantity Int not null)
GO

INSERT INTO #TestTab1 VALUES (1,1,1),(1,2,5),(1,2,10),(1,3,20),
                            (2,1,101),(2,2,105),(2,2,110),(2,3,120)
GO

and then add a filtered index

CREATE INDEX ixFilter ON #TestTab1 (Col1)
WHERE Quantity = 122

Now if we execute

SELECT COUNT(*) FROM #TestTab1 WHERE Quantity = 122

We will see the filtered index being scanned

But if we parameterize the query

DECLARE @i INT = 122
SELECT COUNT(*) FROM #TestTab1 WHERE Quantity = @i

The plan is very different

a table scan, as the value of the variable used in the predicate can change at run time, and also we see the familiar warning triangle.

If we now look at the properties pane, we will see two pieces of information “Warnings” and “UnmatchedIndexes”.

So, handily, we are being told which filtered index is not being used due to parameterization.

Developer IT

Execution plan warnings–The final chapter - Developer IT

Execution plan warnings–The final chapter

Categories cloud