SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL

November 2011 - Posts

Execution plan warnings–The final chapter

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

image

But if we parameterize the query

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

The plan is very different

image

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

image

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

Blogging from 37,000ft

Im currently on my way to Sql Rally nordic and looking forward to a few days of full on SQL geekery and “Unleashing my inner Viking”.  I shall be speaking on Wednesday afternoon on one of my favourite subjects “Cursors are Evil”.  Ok,  so lets put it into perspective, “Evil” is a bit dramatic , but “Often use inappropriately and can cause serious performance bottlenecks” didn't have quite the same ring Smile

If you are not going to be at SQL Rally,  im going to be repeating it at the Leeds and Manchester user groups on the 23rd and 24th of November respectively.  Presenting with me on these nights will be James Boother, so make it along to those if you can.  I look forward to seeing you at one of these events.

Execution plan warnings–All that glitters is not gold

In a previous post, I showed you the new execution plan warnings related to implicit and explicit warnings.  Pretty much as soon as i hit ’post’,  I noticed something rather odd happening.

This statement :

select top(10) 
SalesOrderHeader.SalesOrderID,
SalesOrderNumber
from Sales.SalesOrderHeader
join Sales.SalesOrderDetail
on SalesOrderHeader.SalesOrderID
= SalesOrderDetail.SalesOrderID
 
Throws the “Type conversion may affect cardinality estimation” warning.
 
image
 
Ive done no such conversion in my statement why would that be ?  Well, SalesOrderNumber is a computed column , “(isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID],0),N'*** ERROR ***'))”,  so thats where the conversion is.
 
Wait!!! Am i saying that every type conversion will throw the warning ?  Thankfully, no.  It only appears for columns that are used in predicates ,even if the predicate / join condition is fine ,  and the column is indexed ( and/or , presumably has statistics). 
 
Hopefully , this wont lead to to many wild goose chases, but is definitely something to bear in mind.  If you want to see this fixed then upvote my connect item here.