Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shaggy
Posting Yak Master
248 Posts |
 Posted - 2014-04-16 : 08:52:36
|
| In our env there is 2 cached plan for a SP. I want to know how 2 plans are createdfor a SP there is IF else statement.So how the SQL server will choose what plan to use is it advisable to have multiple plan for a SP whether if it chooses wrong plan it will degarde performance of SP.The SP taked 20 sec to execute when I drop the plan it is executed in 3 secs.And aslso I have heard that in our other env there were more than 200 plan for a SPwhat is the differnce between statement recompilation vs creating more than one plan for a SP.Can anyone let me know how to overcome this |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-04-16 : 09:03:04
|
| There may be one or two query plans; if there are, one is for parallel executions.Your query may be slow because of parameter sniffing. Google for parameter sniffing and ways to avoid them e.g. http://www.sommarskog.se/query-plan-mysteries.htmlStatement level recompilation allows you to tell SQL Server that the statement (and not the whole stored proc) should be recompiled each time the query is run. |
 |
|
|
|
|
|
query plan