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;

1_SQL_Server_Impact_of_literal_predicate_with_different_collation_and_query_plan

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

2_SQL_Server_Impact_of_literal_predicate_with_different_collation_and_query_plan

3_SQL_Server_Impact_of_literal_predicate_with_different_collation_and_query_plan

4_SQL_Server_Impact_of_literal_predicate_with_different_collation_and_query_plan

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

5_SQL_Server_Impact_of_literal_predicate_with_different_collation_and_query_plan

6_SQL_Server_Impact_of_literal_predicate_with_different_collation_and_query_plan

7_SQL_Server_Impact_of_literal_predicate_with_different_collation_and_query_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;

8_SQL_Server_Impact_of_literal_predicate_with_different_collation_and_query_plan

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

10_SQL_Server_Impact_of_literal_predicate_with_different_collation_and_query_plan

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

11_SQL_Server_Impact_of_literal_predicate_with_different_collation_and_query_plan

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 TwitterFollow me on FaceBook

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

One Comment on “SQL Server impact of different collation and query plan”

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

Leave a Reply

Your email address will not be published. Required fields are marked *