SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

The post Generating Estimated Plan and the Plan Cache appeared first on Home Of The Scary DBA.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).

Comments

Leave a comment on the original post [www.scarydba.com, opens in a new window]

Loading comments...