I enjoy learning new things every single day and I must express my gratitude to all of you for helping me learn new things. One of my client Comprehensive Database Performance Health Check recently asked me if I can help them understand how FORCE DEFAULT CARDINALITY ESTIMATION works as during the consulting engagement we applied this hint to few queries to improve its performance. Let us understand this with the help of a demonstration.
What is FORCE DEFAULT CARDINALITY ESTIMATION?
However, before we start the demonstration let us learn what this query hint means. This query hint forces the query optimizer to use the cardinality estimation model set for the current database and helps you to override database scoped configuration setting legacy cardinality estimation. If you are using SQL Server for a while and involved in SQL Server Performance tuning for a while, you may be familiar with the trace flag 9481 and this query hint is very similar to it.
This hint is helpful when you have your database set to newer cardinality and you have enabled the configuration of the legacy cardinality and want your query to run with the default cardinality of the database which you have set with the compatibility level. Well, if this is confusing, check out the image for example.
Now, look at the above scenario. Any query which is running for the database will be using newer compatibility of SQL Server 2017 but will be using the older cardinality estimation. This is usually a good setting when you have recently migrated the database. However, this may not be the best solution for the future but that is the conversation for the future and not the scope of this blog post.
Now while you are running with the above database settings, you may walk into the situation when you find a situation where your 100 queries are running fine but one query is running slow in the above settings and you want may find it running optimally when it is working with the latest cardinality and not the legacy cardinality. In this situation, you can, you may need to use the query hint like FORCE_DEFAULT_CARDINALITY_ESTIMATION.
Here is how you can use this hint to force the query to use the default cardinality specified in the compatibility level instead of the legacy cardinality.
SELECT *
FROM Schema.Table
WHERE Condition = 'Value'
OPTION (USE HINT ('FORCE_DEFAULT_CARDINALITY_ESTIMATION'));
GO I hope the above query will give you guidance on how you can use the query hint. Let me know if you have any questions related to SQL Server Performance Tuning, I will be happy to help.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)Â
