Type Conversion may affect cardinality estimate in query plan

27 November,2017 by Jack Vamvas

Type Conversion may affect cardinality estimate in query plan is a warning found on Execution Plans. This may negatively impact the correct index usage. Cardinality is a critical part of how indexes are used.

Data type matching has many benefits. It’s a practise often neglected in database design but which can have a number of long term consequences for  query access and database performance

One long term cost of neglecting data type matching is it can lead to more memory required. When data types are matched , this leads to less bytes requires , leading to less data pages in the SQL Server buffer cache . leading to faster query performance

Another way to look at it is to say if datatype usage is inefficient there will be more data pages in memory which will constrict the performance of other queries – as they will not have enough space

One of the potential impacts of relying on an implicit conversion  is the use of an index scan versus the index seek. This occurs because the column values must first be converted before they can be compared.  An index seek is a more efficient mehod to access the data in comparison to an index scan.You can check for index scans , by either reading the execution plan or checking for the SQL Server profiler Scan Event

When performance tuning how can you discover queries exhibiting these warning messages. One quick way is to scan the plan cache for plans with these warnings

Read More on Type Conversion may affect cardinality estimate in query plan 

Implicit conversion of data types in the query engine (SQL Server DBA)

Why does Query Optimizer choose a Clustered Index Scan?

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Type Conversion may affect cardinality estimate in query plan


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer