• Adam Thurgar

Cardinality estimator


Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run under compatibility level of 120 (SQL Server 2014), the query took over 13 minutes.

The query had 6 left outer joins, not ideal but not excessive.

We made sure that we had the correct hotfixes applied.

https://support.microsoft.com/en-in/help/3044519/fix-query-performance-issues-when-new-cardinality-estimator-is-enabled

https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server?view=sql-server-2014&redirectedfrom=MSDN

We also did some testing with trace flag 4199, but it may no difference.

https://support.microsoft.com/en-au/help/974006/sql-server-query-optimizer-hotfix-trace-flag-4199-servicing-model

Whilst testing the query in compatibility level of 110 and getting the actual execution plan to compare with the query running in compatibility level 120. I noticed a missing index recommendation. Even though the query was running under a second I added the missing index and there was no significant improvement.

I then dropped the missing index and ran the query in compatibility level 120 to get the execution plan to see what was the big difference between the two compatibility levels. There was no missing index recommendation.

I then created the missing index from the 110 execution plan and ran the query again in compatibility level 120 and this time it returned in less than 1.5 seconds, down from over 13 minutes.

Moral of the story is to make sure you test before changing compatibility levels, but even then a simple index might be all that is required.


4 views

Recent Posts

See All

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t

Statistics sampling

DBA's and developers know the importance of having up to date statistics to ensure that query performance is optimal. Most of the time the statistics sampling percentage is left to the default. To und