Non Recompile Option while executing the Stored Proc.

Question

Non Recompile Option while executing the Stored Proc.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
July 17, 2012 at 10:09 am

#259083

Hi,
Can you please let me know the option available to restrict the SQL Stored Proc , not to create a new execution plan every time it is executed and compiled..?
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There's no such option because that is the default behaviour. When executing a proc it will reuse the cached plan if one is available. The only times a procedure will compile is if there is no cached plan, if a recompile was requested or if the cached plan is invalid.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

What is the query/table to check the execution plan of the queries until we restart the SQL Server?

Please suggest.

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

sys.dm_exec_cached_plans and related DMVs

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Daxesh Patel Hall of Fame Points: 3208 More actions · Answer 4

Hi,
Can you please let me know the option available to restrict the SQL Stored Proc , not to create a new execution plan every time it is executed and compiled..?

Do some research on parameterization (Simple and Forced). And be sure you understand this thoroughly before any implementation.