Post reply

DB Comp. Level 110 and Cardinality Version 150 in [sys].[query_store_plan]

  • December 23, 2022 at 11:03 pm

    #4129591

    I am using Query Store in SQL Server 2019 to evaluate the performance impact of upgrading the compatibility level of a database from 110 (SQL 2012) to 150 (SQL 2019). As part of this exercise I frequently alternate between the two comp. levels, from 110 to 150 and vice versa, and looking at query performance regression as a result.

    I noticed this strange behavior in some entries of the Query-Store system view [sys].[query_store_plan], where [compatibility_level]=110 and the "CardinalityEstimationModelVersion" value in the query-plan XML is 150, instead of 70!

    The Cardinality-Estimator version in SQL versions of SQL 2012 and older is and should be 70.

    In this case I have a query plan created in a database with compatibility level 110 (SQL-2012) but using the newer version of the cardinality estimator. I thought that was not possible.

    Also seeing the reverse in some other entries of [sys].[query_store_plan]: compatibility_level = 150 and "CardinalityEstimationModelVersion" = 70, even though I am not using any query hint to force the old version of the cardinality estimator.

    Has anyone else noticed this, and what could be the cause?

     

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • December 24, 2022 at 11:10 pm

    #4129908

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • January 19, 2023 at 5:35 am

    This was removed by the editor as SPAM

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply