Understanding complications, plan reuse and recompilations of stored procedures – Itzik Ben-Gan
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.
PLAN RESUSE
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
PARAMETER SNIFFING
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
DYNAMIC FILTERS
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.
