You read that correctly, three kinds of execution plans. You may have thought that all you had to deal with are estimated and actual, but there is one more. The estimated plan is the plan that comes out of the optimizer. It’s based on statistics and indexes and known objects within the system. The actual plan is the plan that was used to execute the query and will show all the actual number of rows processed, etc. It might be different than the estimated plan because the stastics were off or for any number of other reasons. Those were the ones you knew about. There is also the plan that gets stored in the plan cache, the compiled plan.
I lied. The compiled plan and the estimated plan are the same thing, but I wasn’t really that aware of it until I started working with the sys.dm_exec_query_plan DMV. One day I went to look at the statistics of a plan after I executed it, prompted by a discussion at SQL Server Central. Instead I found an estimated plan. I flipped and began running all sorts of tests until I realized that, of course, the plan stored is an estimated plan. The number of rows changes over and over again as different data comes into the system or different parameters change the data accessed by any given execution plan. The system isn’t going to update the plan in place over & over. It just makes sense. But I had to see it in action.
First, I created a dummy table in AdventureWorks, just to play with. Some of these scripts are modified from Dissecting SQL Server Execution Plans (I’m lazy):
-- query to create the test table
IF EXISTS ( SELECT *
FROM sys.objects AS o
WHERE o.[object_id] = OBJECT_ID(N'dbo.[NewOrders]')
AND o.[type] IN (N'U') )
DROP TABLE dbo.[NewOrders]
GO
SELECT *
INTO dbo.NewOrders
FROM Sales.SalesOrderDetail AS sod
GO
CREATE INDEX IX_NewOrders_ProductID ON dbo.NewOrders (ProductID)
GO
Then comes the meat. First I’m clearing out the cache so that I can see exactly what gets stored there. I run a simple select statement and capture the estimated execution plan. I’m using SET SHOWPLAN_XML ON so that I can capture execution plans while running multiple scripts. Then I check the cache to see if anything is there. After that, because I want to see differences in my plans, I update my test data and the statistics on the data so that the exact same query produces a different execution plan. Here’s the code:
DBCC FREEPROCCACHE()
GO
--estimated plan
SET SHOWPLAN_XML ON
GO
SELECT nwo.OrderQty
,nwo.CarrierTrackingNumber
FROM dbo.NewOrders AS nwo
WHERE nwo.ProductID = 897
GO
SET SHOWPLAN_XML OFF
GO
–what’s in cache now?’
SELECT deqp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
GO
BEGIN TRAN
UPDATE dbo.NewOrders
SET ProductID = 897
WHERE ProductID BETWEEN 1 AND 10000
GO
UPDATE STATISTICS dbo.NewOrders
GO
–actual plan
SET STATISTICS XML ON
GO
SELECT nwo.OrderQty
,nwo.CarrierTrackingNumber
FROM dbo.NewOrders AS nwo
WHERE nwo.ProductID = 897
ROLLBACK TRAN
GO
SET STATISTICS XML OFF
GO
–and the cache again
SELECT deqp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
The estimated plan looks like this:
The actual plan looks like this:
And the plan out of the cache looks like this:
You can see the differences as the plans are generated from different points in the script, but the final storage is an estimated plan, even though, in this case, it doesn’t look like the original estimated plan.
Gail Shaw over at SQL In Wild has a much better description of what’s going on than I’m going to attempt here. I wanted to show it to you in action.