Generating Estimated Plan and the Plan Cache

Does generating an Estimated Plan cause that plan to be loaded into the plan cache?

No.

 

What? Still here? You want more? Proof? Fine. Let’s first run this bit of code (but please, not on your production server):

DBCC FREEPROCCACHE();

That will remove all plans from cache. Now, let’s take this query and generate an Estimated Plan (CTL-L from your keyboard or by clicking on the “Display Estimated Execution Plan” button on the toolbar):

SELECT * FROM Production.ProductModel AS pm;

This will generate a trivial plan showing a scan against the Production.ProductModel table. Now, let’s run another query:

SELECT  deqs.plan_handle
FROM    sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE   dest.text = 'SELECT * FROM Production.ProductModel AS pm;';

That’s just an easy way to see if a plan_handle exists. If a plan was stored in cache for this query, I should see a result. I don’t. Now, you might say that this is because it’s a trivial plan. So, let’s complicate the query a little, add some JOINs and a WHERE clause:

SELECT  *
FROM    Production.Product AS p
JOIN    Production.ProductModel AS pm
        ON pm.ProductModelID = p.ProductModelID
JOIN    Production.ProductInventory AS pi
        ON pi.ProductID = p.ProductID
JOIN    Production.Location AS l
        ON l.LocationID = pi.LocationID
WHERE   p.ProductID = 750;

Not the most complicated query around, but, it will go through full optimization because it is not a query that will generate a trivial plan. Let’s generate an Estimated Plan again. Now we’ll modify our query against the cache just a little:

SELECT  deqs.plan_handle
FROM    sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE   dest.text = 'SELECT  *
FROM    Production.Product AS p
JOIN    Production.ProductModel AS pm
        ON pm.ProductModelID = p.ProductModelID
JOIN    Production.ProductInventory AS pi
        ON pi.ProductID = p.ProductID
JOIN    Production.Location AS l
        ON l.LocationID = pi.LocationID
WHERE   p.ProductID = 750;'

You won’t see any rows returned. Now, if I execute the query and then re-run the query against the cache, then I see a plan. How about stored procedures? I have a really simple one I use to teach parameter sniffing:

EXEC dbo.spAddressByCity
    @City = N'Mentor';

I’ll generate an Estimated Plan and then query the cache using a slightly different approach:

SELECT  deps.plan_handle
FROM    sys.dm_exec_procedure_stats AS deps
WHERE   deps.object_id = OBJECT_ID('dbo.spAddressByCity');

Again, assuming I’ve cleared the cache and I haven’t executed the procedure, this returns zero rows. That’s because generating an Estimated Plan does not then load that plan into the cache. The plan gets loaded into cache when it gets executed.


I love talking about execution plans and query tuning. If you do too, we two opportunities to get together. The first is in Las Vegas at the Connections conference. Click here to register. If you bring your execution plans to Vegas, I’ll try to make some time during the session to use your plans to demonstrate to the crowd. The second opportunity will be down in San Diego, the day before their SQL Saturday event.

3 thoughts on “Generating Estimated Plan and the Plan Cache

  • Mike Dimmick

    On SQL Server 2005 and 2008 R2, if you query sys.dm_exec_cached_plans rather than sys.dm_exec_query_stats, you should get a row:

    SELECT decp.plan_handle
    FROM sys.dm_exec_cached_plans AS decp
    CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest
    WHERE dest.text = ‘SELECT * FROM Production.ProductModel AS pm;’;

    Querying sys.dm_exec_query_plan, passing that handle, produces query plan XML that appears correct for the query.

    Testing on SQL Server 2012, it doesn’t show up in sys.dm_exec_cached_plans. Looks like this was an internal change. I haven’t tried it out on SQL Server 2014 or 2016 as I don’t currently have either installed.

    I’m guessing it doesn’t show up in sys.dm_exec_query_stats because it’s never been executed. Actually executing the query causes it to show up there – with the same plan_handle as before, on SQL Server 2008 R2.

    For diagnostics, possibly the more interesting question is, if I copy a query from SQL Profiler (for example), paste into SSMS and use Display Estimated Execution Plan, will I get the same plan that the server is currently getting from the cache? The answer is usually ‘it depends on your SET options’. This has led to head-scratching in the past where the estimated plan in SSMS looked right, but the actual executing queries were slow because the cached plan had been compiled with atypical parameters. The defaults in SSMS can often be different from the .Net SqlClient or ODBC driver’s defaults.

  • I don’t have a 2008R2 or earlier version to check on. Maybe that did change with newer releases. But I double checked, dumping the cache with FREEPROCCACHE and then just looking at the plans in cache:

    SELECT deqp.* FROM sys.dm_exec_cached_plans AS decp
    CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) AS deqp

    You were right that getting rid of sys.dm_exec_query_stats was the right thing to do to validate stuff. Thanks.

    But, I’m still not getting a plan in cache from the generation of the estimated plan.

    And you’re right, both SET options and statement recompiles could lead to a different plan, making everyone crazy.

  • This was indeed a change for SQL Server 2012. We documented it in Plan Caching and Recompilation in SQL Server 2012 (https://msdn.microsoft.com/en-us/library/dn148262.aspx) in the section, “Mode Changing SET Options (SHOWPLAN-Related and Others)”. Extract:

    Some SET operations change the mode of operation for SQL Server. In SQL Server 2012, a plan is compiled but not cached when using certain SET options that cause a batch to be evaluated but no query to be executed. This is a change in behavior from earlier versions of SQL Server.

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.