Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

stored procedures execution plan

How can I find out if my stored procedures execution plan is being recompiled each time it runs.

When I run the same query when it is not encapsulated inside a procedure it is much faster.
0
Mr_Shaw
Asked:
Mr_Shaw
  • 2
2 Solutions
 
GiantSlayerCommented:
If your parameter values in your sp vary a lot, SQL doesn’t benefit from the cached execution plan.

You could use WITH RECOMPILE to force recompilation each time the sp is run, or if the sp is large, try and split it up into multiple smaller sp’s.

A bit about recompiling sp's:
http://msdn.microsoft.com/en-us/library/ms190439.aspx
0
 
Mr_ShawAuthor Commented:
I don'y want to recompile... i want to see if the sql engine is recompliling the sproc automatically.
0
 
Mr_ShawAuthor Commented:
thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now