SQL SERVER – SQL_NO_CACHE and OPTION (RECOMPILE)

As a developer and DBA who often has preferred technology but if we are running a large organization, we have to be more accommodating to all the technologies. One of my large banking clients uses multiple databases for its doing various different transactions. They are also using lots of different caching solutions for their business as well as top of the line hardware. I had a very interesting conversation with my client on the topic of how to retrieve queries, not from the cache for MySQL and SQL Server, we discussed SQL_NO_CACHE and OPTION (RECOMPILE). We discussed during the Comprehensive Database Performance Health Check.

Let us see two different examples today for MySQL and SQL Server. Both of the relational databases takes advantages of the memory cache to return us the data. Let us see how we can write a query where we will not use the cached results but directly get our data from the SQL Server disk and not from the cache.

SQL Server Query – OPTION (RECOMPILE)

SELECT Columnname
FROM TableName
OPTION(RECOMPILE)

MySQL Query SQL_NO_CACHE

SELECT SQL_NO_CACHE Columnname
FROM TableName
OPTION(RECOMPILE)

When you use SQL_NO_CACHE and OPTION (RECOMPILE), the relational databases (MySQL and SQL Server respectively) are directly retrieving the data from the disk rather than what is stored in the cache.

Here are a few relevant blog posts on the same topic, which you may find interesting.

• 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

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