SQL Server – Dynamic SQL, Stored Procedure, and Plan Cache

Preface

Preparing to review our Query Plan Cache so that we can profile our SQL Instance performance.

HouseKeeping

Let us clear out our procedure cache:


declare @dbid int

set @dbid = db_id()

DBCC FLUSHPROCINDB(@dbid)
       with no_infomsgs;	

Code

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:

  1. Ad-Hoc Query
  2. Prepared Query
  3. Stored Procedure

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

 

Invoke

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


 

Plan Cache

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

Output:

QueryPlans_v3

 

Quick Explanations:

  1. We cleared our Plan Cache
  2. Issued our payload
  3. Issued a request to review our Plan Cache
  4. 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.

References

  1. MSDN Blogs > SQL Programmability & API Development Team Blog > 5.0 Retrieving Query Plans from Plan Cache DMV’s
    http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/12/5-0-retrieving-query-plans-from-plan-cache-dmv-s.aspx

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s