CODEWITHSUNDEEP
sql-serverstored-procedures
Hemal
Hemal

Reputation: 124

SQL Server stored procedure becomes slow after some time

I have created a stored procedure in SQL Server which returns data in one second. This stored procedure works fine for a couple of days, and it becomes slow later on. However if I re-execute it without making changes, it becomes fast.

I came to know that Recompile may fix this issue. Any suggestion on this?

Also will ReCompile cause any performance issue as this query is hit almost every second?

Upvotes: 1

Views: 1013

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28890

Also will ReCompile cause any performance issue as this query is hit at almost every second ?

Yes recompiling takes toll on CPU resources.Even though compiling a query plan is done in a matter of milliseconds,this will add extra CPU burden since query wont't use existing query plan

I came to know that Recomplie may fix this issue ?

SQL server will store the query plan with the parameter passed and optimize the plan for this input..

Suppose you have a table with each customer having approximately equal orders,then recompiling every time wont help you..

On the other hand if your customer's orders distribution is uneven,in range of 1-10000.In this case if SP is compiled using input of customer having one order,then when 10000 orders customer is passed through SQL will underestimate memory and may spill to tempdb ..

There are many ways in which you can overcome parameter sniffing,try to read the link i referred and see which option works for you

Upvotes: 2

Related Questions