Developing Matt

My Technical Journal

Understanding complications, plan reuse and recompilations of stored procedures – Itzik Ben-Gan

leave a comment »

Understanding complications, plan reuse and recompilations of stored procedures – Itzik Ben-Gan


Can I just say WOW!  The things I learned in this 1 hour class were phenomenal.  Thanks Itzik for giving me a leg up on optimizing queries.  My notes are here (02_understanding-compliationsstoredproc_itzikbengan)  and his sql scripts are here.



When is a plan reused and what is the possible problem?

The problem is that the cost of reusing an inadequate plan is higher than the cost of recompilation

Any query over 1% of an index is not as efficient as a full table scan

How to query the existing plan: select * from sys.syscacheobjects where sql like ‘%something unique about stored proc. He used newid() to get an ide and added it as a commented line in the stored proc%’

·         Usecounts will show how many times the plan has been used

An example would be to create a stored procedure and then run it for the first time with a highly selective criteria.  The plan that is saved might be ineffective if that’s not a typical criteria. 

Possible solutions:

Create an index that covers the ctriteria(n))

Note: when it doesn’t matter the selectivity it is called a trivial plan and the same plan is always usedc (the optimizer has a flag for this type of plan)

Add recompile to procedure

Specify certain queries not to optimize (add option (recompile) after where clause

The cost of recompile every time is less than the cost of using a wrong plan



The problem is centered around the fact that a declaration inside a stored procedure cannot be ‘sniffed’ (read ahead), therefore it cannot be optimized.  Great discussion on this read notes for solution



This was another great example that showed a common <bad> use of dynamic sql. (example….where name like ‘%@parameter@%’ or  city like ‘%@city%’ or state like ‘%@state%’)  He talked about a way to optimize a query in a clever way.  He also spoke of how to prevent sql injection (use sp_executesql with parameters).  Excellent information I can use today.


Written by matt

May 7, 2008 at 8:53 am

Leave a Reply

Fill in your details below or click an icon to log in: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: