SQL Server impact of different collation and query plan

Hi Friends,

Recently I completed my blog series on execution plans which I hope helped you to understand on the basics. Couple of days back, I got a message in one of the social networking site asking;

 ‘What is the impact of a literal predicate which has different accent sensitivity/collation when compared to a column in turn triggers any implicit conversion and any performance impact because of this?’

Interesting question which I thought of checking at my end and encouraged me to write a blog post. In short this is to find out, impact of different collation and their impact on execution plan. Collation settings defined at server level could be database or a column level as well and by default if we do not specify any collation explicitly while defining a column it inherits database collation level.

I’ll be using tempdb for todays’ demonstration. Let us first check collation for tempdb database;

Let me create a table for demonstration and insert few records;

We will now define an index on ‘Name’ column and then run a SELECT statement then check execution plan;

Table ‘CollateTest’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

As can been it reported logical reads 4. We will now change literal collation in SELECT statement then have a look into the execution plan;

Table ‘CollateTest’. Scan count 1, logical reads 128, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Logical read is now 128 as collation precedence took place. To be specific, if the literal has an explicit collation mentioned, we would observe a plan with SCAN with CONVERT_IMPLICIT. To dive into deep, we could use QueryOptimizer trace flag and have a look what happened internally.

*** Input Tree: ***

LogOp_Project QCOL: [tempdb].[dbo].[CollateTest].Name

LogOp_Select

LogOp_Get TBL: dbo.CollateTest dbo.CollateTest TableID=21575115 TableReferenceID=0 IsRow: COL: IsBaseRow1002

ScaOp_Comp x_cmpEq

ScaOp_Convert varchar collate 61449,Null,Var,Trim,ML=900

                        ScaOp_Identifier QCOL: [tempdb].[dbo].[CollateTest].Name

ScaOp_Const TI(varchar collate 61449,Var,Trim,ML=11) XVAR(varchar,Owned,Value=Len,Data = (11,TestCollate))

AncOp_PrjList

Yes, it is not possible to know collation name from the tree and you can very well find the same using following SELECT statement; which is the same as the one used in query.

I hope this explains what happens internally when we specify a different collation in literal.

Regards

Kanchan Bhattacharyya

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook