This blog has moved, permanently, to http://software.safish.com.

Tuesday, May 19, 2009

SQL Server Query Plans

The caching of query plans in SQL Server is extremely important when it comes to application performance. The way this works is not very well understood - I still don't get all the intricacies of it but as a general rule of thumb, it's a good move to either:
  1. Use stored procedures: these get pre-compiled and allow the re-use of execution plans. They allow for parameters, allowing for a "shared" execution plan
  2. he use of stored procs is not possible or not part of your design, use sp_executesql - do NOT use EXEC when running dynamic sql. sp_executesql, unlike EXEC, can be parameterised and therefore also allows for "shared" execution plans.
You can analyse the cached execution plans on SQL Server with the following statement:
 
with CachedPlans as (
select top 100
    objtype,
    p.size_in_bytes,
    left([sql].[text], 100) as [text],
    usecounts
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
)
select * from CachedPlans
where text like '%Select * from MyTable%'
order by usecounts desc

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.