sqlskills-logo-2015-white.png

SQL Server 2012’s RetrievedFromCache Attribute

SQL Server 2012 includes a new RetrievedFromCache attribute in the query execution plan.

Let’s say I execute the following query immediately after executing DBCC FREEPROCCACHE:

1
2
3
4
5
6
7
SELECT     p.ProductLine,
           SUM(f.SalesAmount) AS TotalSalesAmount
FROM [dbo].[FactInternetSales] AS f
INNER JOIN [dbo].[DimProduct] AS p ON
     f.ProductKey = p.ProductKey
GROUP BY p.ProductLine
ORDER BY p.ProductLine;

What value for RetrievedFromCache would you expect to see?  In this example, I saw the following attribute value (with the attribute highlighted and StmtSimple abridged for clarity):

<StmtSimple StatementCompId=”2″ StatementEstRows=”5″ StatementId=”1″ StatementOptmLevel=”FULL” StatementSubTreeCost=”57.0909″  RetrievedFromCache=”true”>

This value is also “true” for scenarios where you use sp_recompile on a module – as it just means that object will be recompiled on the next run and retrieved from cache.

What if I add a RECOMPILE query hint?

1
2
3
4
5
6
7
8
SELECT     p.ProductLine,
           SUM(f.SalesAmount) AS TotalSalesAmount
FROM [dbo].[FactInternetSales] AS f
INNER JOIN [dbo].[DimProduct] AS p ON
     f.ProductKey = p.ProductKey
GROUP BY p.ProductLine
ORDER BY p.ProductLine
OPTION (RECOMPILE);

This time, I saw a “false” for RetreivedFromCache:

<StmtSimple StatementCompId=”1″ StatementEstRows=”5″ StatementId=”1″ StatementOptmLevel=”FULL” StatementSubTreeCost=”57.0909″  RetrievedFromCache=”false”>

And what about scenarios where you have “optimize for ad hoc workloads” enabled for the SQL Server instance?

1
2
3
4
5
6
7
EXEC sp_configure 'show advanced options',1;
 
RECONFIGURE;
 
EXEC sp_configure 'optimize for ad hoc workloads',1;
 
RECONFIGURE;

I executed DBCC FREEPROCCACHE and execute the following query (which should be “stubbed” given the server option):

1
2
3
4
5
6
7
8
SELECT     p.ProductLine,
SUM(f.SalesAmount) AS TotalSalesAmount
FROM [dbo].[FactInternetSales] AS f
INNER JOIN [dbo].[DimProduct] AS p ON
f.ProductKey = p.ProductKey
GROUP BY p.ProductLine
ORDER BY p.ProductLine;
GO

Sure enough – the RetrievedFromCache is False:

<StmtSimple StatementCompId=”1″ StatementEstRows=”5″ StatementId=”1″ StatementOptmLevel=”FULL” StatementSubTreeCost=”57.0909″  RetrievedFromCache=”false”>

And if I execute the SELECT query a second time without clearing the cache, it turns to “true”:

<StmtSimple StatementCompId=”1″ StatementEstRows=”5″ StatementId=”1″ StatementOptmLevel=”FULL” StatementSubTreeCost=”57.0909″  RetrievedFromCache=”true”>

Now if I disable “optimize for ad hoc workloads” – what changes?

1
2
3
4
5
EXEC sp_configure 'optimize for ad hoc workloads',0;
RECONFIGURE;
 
EXEC sp_configure 'show advanced options',0;
RECONFIGURE;

As expected – after executing DBCC FREEPROCCACHE and executing the SELECT query, I see a RetrievedFromCache value of “true” in contrast to “false” when optimize for ad hoc workloads is enabled.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course