Which cardinality estimator (CE) was used in my query?

SQL Server 2008 is reaching end of support this year, so upgrading your SQL Server might be on your mind. One of the big changes when you upgrade your SQL Servers is upgrading the compatibility level, which by default will upgrade the cardinality estimator (CE).

This can change query performance, for better or for worse. This post won’t focus on whether it’s good or bad, but instead I want to show you how you can check to see what CE was used by your queries.

Let’s look at a database in 2008 compatibility level

Here’s our database properties.

This is similar to a database restored fresh from 2008 onto a 2017 server. Now, let’s run our query and see what CE will be used.

USE [StackOverflow2010]
GO

SELECT TOP 100 * 
FROM Users AS u
WHERE u.Reputation = 1001

Looking at the properties of the SELECT operator.

So the old cardinality estimator is version 70. Let’s upgrade the compatibility level to SQL Server 2017.

ALTER DATABASE [StackOverflow2010] SET COMPATIBILITY_LEVEL = 140
GO

Re-running our query:

So this query is using the 2017 CE. There’s two more versions available in SQL Server 2017: 120 and 130, which are accessible with compatibility level 120 and 130 respectively.

That brings us a total of four different versions: 70, 120, 130 and 140.

Compatibility level (and therefore, cardinality estimator level) can change your execution plans

I didn’t show any examples in this post because the execution plan stayed the same, but changing the database compatibility level can dramatically change execution plans. That’d be a good topic for a future post!

Stay tuned!