CODEWITHSUNDEEP
sqlsql-server
Hemal
Hemal

Reputation: 124

Declare stored procedure with recompile

I am using SQL Server. I am facing an issue in which my stored procedure becomes slow after every couple of days. It seems like a stored procedure caching issue. I came to know that I can declare my stored procedure as "with recompile". Will it cause any performance issue?

Upvotes: 0

Views: 749

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28900

I came to know that i can declare my stored procedure as ReCompile. Will it cause any performance issue ?

CPU usage time for compiling a plan will be in milliseconds,but if you have many type of these queries running,they will add up to CPU Cost and will cause high cpu issues

There are many other options to overcome parameter sniffing..you can force statement level recompilation(from SQLServer 2008) ,if you have narrowed down the issue to few statements..This will help in recompiling only few areas of query and not entire proc

Upvotes: 2

Related Questions