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.

SQL SERVER - SQL_NO_CACHE and OPTION (RECOMPILE) option-800x252

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.

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

, , , ,
Previous Post
SQL SERVER – Msg 1842 – The File Size, Max Size Cannot be Greater Than 2147483647 in Units of a Page Size
Next Post
SQL SERVER – Fixing Freezing Activity Monitor

Related Posts

3 Comments. Leave new

  • I thouhjt OPTION (RECOMPILE) only forced the compilation of a new execution plan – i.e. would still use the data from the cache if already present?

    Reply
  • Roman Peralta
    May 10, 2020 1:46 am

    I had tought that RECOMPILE hint was meant only for execution plans not for data cache.

    Reply
  • Carsten Saastamoinen
    May 16, 2020 1:15 pm

    RECOMPILE is ONLY for execution plans not for data cache!!!!!!!!!

    Reply

Leave a Reply

Menu