Preparing to review our Query Plan Cache so that we can profile our SQL Instance performance.
Let us clear out our procedure cache:
declare @dbid int set @dbid = db_id() DBCC FLUSHPROCINDB(@dbid) with no_infomsgs;
We will present a couple of options for retrieving data from our table.
Btw, for the sake of familiarity we are using the AdventureWorksDW database.
The options we will cover in successive order are:
- Ad-Hoc Query
- Prepared Query
- Stored Procedure
The Stored Procedure stands alone in that we need it in place, before we can employ it.
We will name the SP dbo.usp_DimCustomer_Get and here is the rudimentary code.
if OBJECT_ID('dbo.usp_DimCustomer_Get') is null begin exec('create procedure dbo.usp_DimCustomer_Get as select 1/0 as [shell]') end go alter procedure dbo.usp_DimCustomer_Get ( @lastname varchar(30) , @firstname varchar(30) ) as begin select /* Stored Procedure */ CustomerKey, FirstName, LastName from [dbo].[DimCustomer] tblC where tblC.[Lastname] = @lastname and tblC.[Firstname] = @firstname end go
Here is a code that uses ad-hoc, prepared query, and Stored Procedure.
/* Declare variables */ DECLARE @lastnameLocal nvarchar(500); DECLARE @firstnameLocal nvarchar(500); set @lastnameLocal = 'Green' set @firstnameLocal = 'Stephanie' if ( (@lastnameLocal is null) ) begin select @lastnameLocal = lastname , @firstnameLocal = firstname from [dbo].[DimCustomer] tblC order by NEWID() end /* Adhoc */ select /* Adhoc */ CustomerKey, FirstName, LastName from [dbo].[DimCustomer] tblC where tblC.[Lastname] = @lastnameLocal and tblC.[Firstname] = @firstnameLocal /* Prepared Query */ DECLARE @SQLString nvarchar(500); DECLARE @ParmDefinition nvarchar(500); SET @SQLString = N' select /* Prepared */ CustomerKey, FirstName, LastName from [dbo].[DimCustomer] tblC where tblC.[Lastname] = @lastname and tblC.[Firstname] = @firstname '; SET @ParmDefinition = N'@lastname varchar(30), @firstname varchar(30)'; EXECUTE sp_executesql @SQLString , @ParmDefinition , @lastname = @lastnameLocal , @firstname = @firstnameLocal ; /* Invoke SP */ exec dbo.usp_DimCustomer_Get @lastname = @lastnameLocal , @firstname = @firstnameLocal
Let us query our Plan Cache and see how each access method is noted.
select st.text , cp.objtype , qp.dbid , dbName = db_name(qp.dbid) , cp.cacheobjtype , cp.objtype , [objectName] = object_name ( qp.objectid , qp.[dbid] ) , qp.objectid , cp.plan_handle , qp.query_plan from sys.dm_exec_cached_plans cp cross apply sys.dm_exec_sql_text(cp.plan_handle) st cross apply sys.dm_exec_query_plan(cp.plan_handle) qp where ( --filter out references to system functions */ (st.text not like '%sys.%') ) go
- We cleared our Plan Cache
- Issued our payload
- Issued a request to review our Plan Cache
- And, here is where we ended up
- The “Stored Procedure” is first on the list
- Next comes a “SET STATISTICS”
- Thinking is that this a no-op for us
- And, to round up our “Prepared Statement“
Moral of the Story
Wish I could say I knew and forgot, but let us just say that has we inspect and use XPATH to aggregated our Plan Cache, it is likely that we will not be able to get a complete picture of Ad-hoc queries.
- MSDN Blogs > SQL Programmability & API Development Team Blog > 5.0 Retrieving Query Plans from Plan Cache DMV’s