Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a simple code segment that runs in about 1 second when run alone, but more than 5 minutes when deployed as a stored procedure. I'm using sql server 2008.

Unfortunately, I can't post the code due to company policy. Is there a general explanation of this behavior?

share|improve this question

closed as unclear what you're asking by Mark Storey-Smith, Phil, ypercube, Max Vernon, Paul White Jun 20 at 0:13

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

3  
Hang on - I'll just deploy the MS SQL Server Crystal Ball module (TM) and your problem will disappear! Sorry about the sarcasm, but your question is a bit like saying "I'm going somewhere but I can't tell you where, can you help me to get there?". Change field names - sanitise data - only include key fields, but with no information, you can't expect to receive much assistance. –  Vérace Jun 18 at 14:39
3  
Can't really be certain without the code and execution plan (could you post the plan for both?). This may also be worth a read: sommarskog.se/query-plan-mysteries.html –  swasheck Jun 18 at 14:40
1  
It was a parameter sniffing issue. Thanks zlatan! –  Thomas Cleberg Jun 18 at 14:48
    
@Vérace I understand, that is why I specified a general solution, which zlatan was able to help with. Thanks for the lesson! –  Thomas Cleberg Jun 18 at 14:49
3  
It's probably what you already found out, but don't take it that parameter sniffing is bad, cause it isn't. Please read Kendra's article Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables), too. –  Marian Jun 19 at 7:31
show 1 more comment