SQL SERVER – Impact of Changing Database Compatibility Level on Cache

In the world of SQL Server, the database compatibility level plays a vital role in determining how a database behaves when it comes to executing queries and optimizing execution plans. SQL Server constantly strives to improve performance and query optimization by adopting new algorithms and enhancements with each new release. As a result, changing the compatibility level becomes necessary to leverage the latest features and improvements. In this blog post, we will explore how changing the compatibility level affects the query cache and why it’s essential to be aware of its impact. I usually have this discussion during my Comprehensive Database Performance Health Check.

Understanding the Query Cache

Before diving into the effects of changing the compatibility level, let’s briefly discuss the query cache in SQL Server. The query cache, also known as the plan cache, stores the execution plans generated by the SQL Server query optimizer. When a query is executed, SQL Server first checks the cache to see if there is an existing execution plan for that specific query. If found, the cached plan is used, saving the overhead of generating a new plan. This significantly improves query performance and reduces query processing time.

Generating Sample Execution Plans: To illustrate the impact of changing the compatibility level, let’s generate some sample execution plans for queries in the AdventureWorks2019 database.

USE AdventureWorks2019;
-- Query 1
SELECT p.ProductID, p.Name AS ProductName, c.Name AS CategoryName
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS sc 
ON p.ProductSubcategoryID = sc.ProductSubcategoryID
JOIN Production.ProductCategory AS c ON sc.ProductCategoryID = c.ProductCategoryID;
GO 5
-- Query 2
SELECT SalesOrderID, SUM(LineTotal) AS TotalSalesAmount
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID;
GO 5
-- Query 3
SELECT p.ProductID, p.Name AS ProductName, 
       AVG(sod.OrderQty) AS AvgOrderQuantity,
       SUM(sod.LineTotal) AS TotalSalesAmount
FROM Production.Product AS p
JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
GROUP BY p.ProductID, p.Name;
GO 5

Checking Cache Status: Next, let’s examine the cache status using the following queries:

USE AdventureWorks2019;
-- Check the buffer cache usage
SELECT
    COUNT(*) AS CachedPagesCount,
    COUNT(*) * 8 / 1024 AS CachedSizeMB
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID();
GO
-- Check the plan cache usage
SELECT
    objtype AS CacheObjectType,
    COUNT(*) AS CachedPlansCount,
    SUM(size_in_bytes) / 1024 AS CachedSizeKB
FROM sys.dm_exec_cached_plans
GROUP BY objtype;
GO
-- Check the procedure cache usage
SELECT
    cacheobjtype AS CacheObjectType,
    COUNT(*) AS CachedObjectsCount
FROM sys.dm_exec_cached_plans
GROUP BY cacheobjtype;
GO

Here is the result you will see:

Impact of Changing Compatibility Level: Now, let’s proceed to change the compatibility level using the following queries:

-- For SQL Server 2019:
ALTER DATABASE [AdventureWorks2019] SET COMPATIBILITY_LEVEL = 150;
GO

Here 150 stands for SQL Server 2019. If you want to change the compatibility level to SQL Server 2022, you can use 160 instead of 150. Similarly, you can use 140 for SQL Server 2017.

Once you change the compatibility level and check the query cache again, you will see it is nearly empty.

Observations and Conclusion

In conclusion, the compatibility level of a SQL Server database plays a crucial role in determining the query optimization behavior and performance. Changing the compatibility level can influence the cached execution plans and, in turn, impact the overall query performance. As part of the Comprehensive Database Performance Health Check, I always consider the database compatibility level to ensure that clients leverage the full potential of their SQL Server databases.

You can always reach out to me via YouTube Channel.

Reference: Pinal Dave (http://blog.SQLAuthority.com)