Many SQL Server developers and admins found, after upgrading to SQL Server 2014, that some queries started taking much longer time than before. The reason is the new cardinality estimation formula which was introduced in SQL Server 2014. Cardinality Estimation is done all the time by the SQL Server optimizer. To produce a Query plan, the optimizer makes some assumptions about how many rows exist for each condition in the table. In most cases, the new cardinality estimation formula in SQL Server 2014 and onwards gives slightly better estimates and the optimizer therefore produces slightly better plans. In some cases however, mostly when there are predicates on more than one column in a WHERE clause or JOIN clause, the 2014 cardinality estimation is a lot worse than in previous versions of SQL Server.
I’m doing an upgrade of SQL Server, from 2012 to 2016. We had a stored procedure which went from three minutes to 15 minutes some days ago. Analyzing Query plans for the procedure showed an operator estimating 10.000 rows but with an actual rowcount of nearly three million rows. In SQL Server 2012, the estimation for the same combination of predicates was two million rows. The difference between two and three million rows is neglectable. The difference between 10.000 and three million rows is far from neglectable and in our case it caused the optimizer to choose to do key lookups instead of scanning small and medium sized tables.
There was a new trace flag introduced in SQL Server 2014, 9481, which can be used to instruct SQL Server to use the old cardinality estimation formula. But a trace flag is global and we probably wouldn’t want to change the behaviour for a whole instance when we have a problem with one specific Query.
In SQL Server 2016, database scoped configuration was introduced. Database scoped configuration moves som of the configuration options previously only available on instance level down to the database level. One database scoped configuration option is LEGACY_CARDINALITY_ESTIMATION, which will give the same behaviour as trace flag 9481, but only for a single database. I’m pleased with database scoped configuration, but I still don’t want to force a certain behaviour upon ALL queries in a database when I only have a problem with ONE single Query.
Both in SQL Server 2014 and SQL Server 2016, one can use the query hint QUERYTRACEON, to use the behaviour of certain trace flags for a single query. That’s almost good enough. Almost.
use AdventureWorks2014; --Create a user with low privileges CREATE USER u WITHOUT LOGIN; GO GRANT SELECT TO u; --Impersonate user u GO EXECUTE AS user='u'; GO --Query using QUERYTRACEON to enforce legacy cardinality estimation SELECT * FROM Sales.SalesOrderHeader OPTION(QUERYTRACEON 9481) GO --Stop impersonating user u REVERT; GO --Cleanup DROP USER u; GO
And the result is this
Msg 2571, Level 14, State 3, Line 11 User 'u' does not have permission to run DBCC TRACEON.
QUERYTRACEON requires sysadmin privileges, and I certainly don’t want all my users or application services to run with sysadmin privileges.
Which brings me to the real topic of this post. There has been a LOT of fuzz about SP1 to Sql Server 2016 and most of it has been about all the cool Enterprise features that makes it into Standard edition. Less talked about is the new query hint USE HINT. It provides a method to add behaviour to a single query, and behaviour which was previously only available as trace flags or database scoped configuration options. One option available through USE HINT is FORCE_LEGACY_CARDINALITY_ESTIMATION.
It does the same as querytraceon 9481 and the syntax is quite simple:
SELECT col1, col2, col3...
FROM SomeTable
WHERE col1='somevalue' AND col2='someothervalue' AND col3='somethirdvalue'
OPTION(USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
And if we run our test using the low privelege user “u” again, but with USE HINT instead of QUERYTRACEON, it just works.
use AdventureWorks2014;
--Create a user with low privileges
CREATE USER u WITHOUT LOGIN;
GO
GRANT SELECT TO u;
--Impersonate user u
GO
EXECUTE AS user='u';
GO
--Query using USE HINT to enforce legacy cardinality estimation
SELECT * FROM Sales.SalesOrderHeader
OPTION(USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))
GO
--Stop impersonating user u
REVERT;
GO
--Cleanup
DROP USER u;
GO
To me, this is fantastic, because it lets us fix the very, very few queries where the cardinality estimator goes bananas after an upgrade, without having to change the behaviour of the other several thousand queries.
And of course, there’s a sibling hint to FORCE_LEGACY_CARDINALITY_ESTIMATION, and that is FORCE_DEFAULT_CARDINALITY_ESTIMATION, which overrides both trace flag 9481 and the database scoped setting LEGACY_CARDINALITY_ESTIMATION=ON.
Another really useful hint available through USE HINT is ENABLE_QUERY_OPTIMIER_HOTFIXES. When a hotfix is released to fix some odd behaviour in the optimizer, Microsoft has decided that is’s not going to be enabled by default. That is because a hotfix may fix an odd behaviour for certain queries, but it might introduce other problems with other queries. Instead of globally enabling the trace flag 4199 or use database scoped configuration option QUERY_OPTIMIZER_HOTFIXES=ON, we can now enable Query optimizer on a per query base. Really cool if you ask me!
All the rest of the hints available using USE HINT (there are nine of them) are available in Books OnLine, here: https://msdn.microsoft.com/en-us/library/ms181714.aspx