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 |
|
stephe40
Posting Yak Master
218 Posts |
 Posted - 2006-10-12 : 14:46:02
|
| I have a stored procedure that is taking a long time to run, ~3 minutes. When running the query directly outside of the stored procedure, it runs in ~3 seconds. I immediatly thought that this was a classic parameter sniffing problem. I tried running the stored procedure with "with recompile", No luck, it was still taking 3 minutes. I tried using sp_recompile, no luck. I ran a update statistics with fullscan, no luck. I created a copy of the stored procedure that used local varaibles in the query, rather than using the parameter variables directly. That worked. Again, this sounds like a classic parameter sniffing problem. I have dealt with these before. No matter what I did, I could not get the original sp to use the same execution plan as the updated one that used local variables. And since this stored procedure has no default values set for the parameters I would expect the recomple thing to work.Any ideas on where to go from here?- Eric |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2006-10-12 : 16:59:24
|
| Yes I have. The problem is that I get the same execution plan no matter what parameter gets passed in.- Eric |
 |
|
|
|
|
|
parameter sniffing problem... or is it?