November 2, 2011 at 12:39 pm
drop table itemtest
create table itemtest
(itemid int)
go
create proc testDynamicSQLCaching
@itemid int = 111
as
declare @sql varchar(2000)
set @sql = 'select itemid from itemtest where itemid = '+ @itemid
exec sp_executesql @sql
--then run
exec testDynamicSQLCaching 1111
exec testDynamicSQLCaching 2222
--the second proc call would not use the exection plan previously cached by the first proc call
--because when using the dynamic sql the execution plan is trying to cache the strings
--1)select itemid from itemtest where itemid = 1111 and
--2)select itemid from itemtest where itemid = 2222
--and because their different it re-caches the execution plan for both. unlike non dynamic sql queries where the second proc call would use the first cached execution plan because both @itemids are ints
--Are my assumtions incorrect?
--is this correct? also is there a way for me to tell if the execution of either will use an execution plan who is currently cahed or will it recreate one?
November 2, 2011 at 12:43 pm
The procedure will use the cached plan. The dynamic SQL, because it runs as a separate batch and in a different scope has its own execution plan.
In this case, the dynamic SQL will likely reuse the cached plan because that query's simple enough to qualify for auto-parameterisation. A more complex query (say with a join) wouldn't, so you'd have the procedure's plan getting reused and then multiple plans for the dynamic SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 2, 2011 at 12:44 pm
As for monitoring...
http://sqlinthewild.co.za/index.php/2010/07/27/hit-and-miss/
http://sqlinthewild.co.za/index.php/2010/08/31/come-and-gone/
http://sqlinthewild.co.za/index.php/2011/08/16/compiles-and-recompiles/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 2, 2011 at 2:47 pm
that, my friend, is really cool. This is very useful for anyone optimizing or building search functionality. I plan on doing that at my current work space. My guess is going to be that in order to ensure the most cache hits for a website search it will be better to break the search procs out into much smaller more grandular procs who fit a specific set of parameters. Maybe have a master w/ all possable params that calls a sppecific proc based on the paramiters supplied. Interesting stuff.
thank you guys
November 2, 2011 at 3:46 pm
captcooldaddy (11/2/2011)
My guess is going to be that in order to ensure the most cache hits for a website search it will be better to break the search procs out into much smaller more grandular procs who fit a specific set of parameters. Maybe have a master w/ all possable params that calls a sppecific proc based on the paramiters supplied. Interesting stuff.
If you're doing dynamic search stuff, then here's some more reading 🙂
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply