SQL SERVER – SQL_NO_CACHE and OPTION RECOMPILE Target Different Caches

OPTION (RECOMPILE) and SQL_NO_CACHE concern different cached objects. A banking customer using MySQL and SQL Server prompted my comparison.

SELECT name FROM sys.databases OPTION (RECOMPILE);
-- Historical MySQL 5.7 query-cache modifier, not SQL Server syntax:
-- SELECT SQL_NO_CACHE ColumnName FROM TableName;

OPTION (RECOMPILE) creates a temporary statement plan using the current compilation context. SQL Server discards that plan after execution. It does not flush data pages or force physical disk reads. Repeated compilation also costs resources.

Historical MySQL SQL_NO_CACHE avoids using and populating the query result cache for that statement. It does not bypass InnoDB or operating-system caches. MySQL removed the query cache in 8.0. Check the actual product and version, including MariaDB.

I incorrectly appended SQL Server OPTION syntax to the MySQL example. The corrected historical shape is now a comment. For benchmarks, identify compilation, reads and application-cache effects separately. These modifiers don’t create equivalent cache conditions.

Reference: MySQL 8.0 removal of the query cache.

Related reading

• Comprehensive Database Performance Health Check
• SQL SERVER – List Query Plan, Cache Size, Text and Execution Count
• SQL SERVER – Finding The Oldest Query Plan From Cache
• SQL SERVER – Plan Cache and Data Cache in Memory
• SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer
• SQL SERVER – Remove All Query Cached Plans Not Used In Certain Period
• SQL SERVER – Script to Get Compiled Plan with Parameters From Cache
• SQL SERVER – Plan Cache – Retrieve and Remove – A Simple Script
• SQL SERVER – 2017 – Script to Clear Procedure Cache at Database Level

A cache modifier is not a controlled cold-disk test, it is an instruction with a specific product and scope.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.