Examining Query Plan from Plan Cache

Examining Query Plan from Plan Cache

(SQL Server 2005)

By : Kasim Wirama, MCDBA, MVP SQL Server

I would like to show you about examining plan cache and how you get insight after examining this plan. In SQL Server versions before SQL Server 2005, I could only see plan reuse by querying to syscacheobjects system table in master database. In SQL Server 2005, I can get information not only about plan reuse but also execution plan in plan cache, something that is interesting to get to know further. Moreover, with this execution plan get from plan cache, you will have less likely to execute the query to get the execution plan.

SQL Server 2005 save execution plan for queries, and will parameterized the “safe” query so it could be reuse for same query request but different only in parameter value. What is “safe” query? Safe query is a query that has only 1 definite execution plan for any value of query column, so I can say safe query is a consistent query. You will identify safe query when its execution plan is parameterized in default environment setting (simple parameterized).

To get query plan from plan cache in SQL Server 2005, just query to metadata dynamic management view below :

first clean up procedure cache with Dbcc freeproccache or dbcc flushprocindb(database id), then query to AdventureWorks with query below :

select * from sales.salesorderheader where salesorderid = 43659

query to the DMV will have these 2 entries :

  select * from sales.salesorderheader where salesorderid = 43659

(@1 int)SELECT * FROM [sales].[salesorderheader] WHERE [salesorderid]=@1

First one is Adhoc plan, second one is Prepared plan, if you issue other value of salesorderid, this Prepared plan will be reused with usecounts column value changes from 1 to 2. further more try to click one of Adhoc link in query_plan column.

You will have execution plan in XML format, compared to execution plan in Prepared plan, you will more complete execution plan with more elements, I can say first execution plan is in truncated format, what is the difference between truncated format, and complete format? I notice that truncated format doesn’t have QueryPlan as root element compared to execution plan of complete format.

Another sample query is below :

select * from humanresources.employee where title like 'Production Technician%'

Compare its execution plan, this time you will get complete execution plan instead of truncated one, I conclude that SQL Server consider the query is not safe query, so it will generate plan for that value, if you issue another value, SQL Server will generate other execution plan associated to that value.

Share this post: | | | |