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:

 
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?

 
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?

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):

 
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?

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.