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_Get TBL: dbo.CollateTest dbo.CollateTest TableID=21575115 TableReferenceID=0 IsRow: COL: IsBaseRow1002
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))
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.
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
One Comment on “SQL Server impact of different collation and query plan”
I enjoy, result in I found just what I used to be looking for.
You have ended my four day long hunt! God Bless you man. Have a
great day. Bye