CODEWITHSUNDEEP
sqlsql-servert-sql
unruledboy
unruledboy

Reputation: 2342

Switch execution plan on/off in stored procedure of SQL Server

In my stored procedure, it will do a lot of insert/update queries, and call a few nested stored procedures.

I want to disable the execution plan, bypassing the above mentioned queries, then switch it on to start profiling those queries I am interested in.

for example:

...switch off execution plan

INSERT INTO FOO ...
UPDATE FOO...
EXEC usp_FOO

...switch on execution plan here then I can start getting the performance stat

SELECT * FROM FOO

In SQL Server Management Studio, we have "Include Actual Execution Plan" for performance trace/debug, but if there are more than 100 queries, the execution plan output will exceed and stop working. So I believe there should be some switch like 'SET EXECUTION PLAN OFF' something like that

Upvotes: 5

Views: 4853

Answers (2)

8kb
8kb

Reputation: 11406

I recommend reading Capture execution plans with SQL Server 2005 Profiler. Using profiler, you can generate execution plans for every single query that is run in your stored procedure without worrying about the output limitations of SQL Server Management Studio.

Upvotes: 1

Chains
Chains

Reputation: 13167

You can use query hints to force a 'RECOMPILE' (ignore the cached plan) for all CRUD and MERGE -- def & samples here: http://msdn.microsoft.com/en-us/library/ms181714.aspx

Upvotes: 0

Related Questions