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.
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.
