Comments on SQL Garbage Collector: Parameter Sniffing & Stored Procedures Execution P...

This article and recommendation/solution saved us ...

2011-10-11T14:44:01.014-04:00

This article and recommendation/solution saved us a ton of time! Thank you for writing and posting it.

great information, my sp improved from 79s to 1.5s...

2011-09-13T17:52:35.973-04:00

great information, my sp improved from 79s to 1.5s, you saved me tons of time and painful diagnosing.

Just to answer some questions about differences in...

2011-08-31T20:15:20.977-04:00

Just to answer some questions about differences in performance from different clients, each connection that is determined to be different (even a slightly different connection string from the same application) keeps its own execution plans. This is why the same stored procedure may run fast in query analyzer while being slow in an application.

good article. my sproc went from 34 seconds down ...

2011-07-21T23:47:17.858-04:00

good article. my sproc went from 34 seconds down to 1.5. thanks, james

Very good article. Nicely explained.

2011-05-17T14:26:16.256-04:00

Very good article. Nicely explained.

Ok I have seen loads of posts of using option (rec...

2011-05-05T19:43:36.731-04:00

Ok I have seen loads of posts of using option (recompile) and using local variables in a stored procedure. What about if you are not in control of the generation of the query, as when using Dynamics CRM. It's queries are passed into the sp_executesql stored proc with a literal string of the query & parameters. This gives the same parameters sniffing problem. If i capture the SQL query being produced using profiler & then re-run in management studio, i see the use of a old plan & the query taking minutes. If i amend the query string and add a option (recompile) or actually just adding a space in the string - the query plan is re-evaluated and the results are returned within a second. Trouble is the CRM platform is generating the query so I cant intercept. Is there a way to fix this at a server level???

Great article I have a similar problem where a sim...

2011-02-17T06:26:24.400-05:00

Great article
I have a similar problem where a simple stored procedure times out (takes more than 30 seconds when executed from a Delphi client), but running the procedure from management studio takes 1 second.
As soon as I alter the procedure, it starts working fast again.
My question is, why the procedure (with the same parameters) runs fast in management studio? If it was parameter sniffing, wouldnt be affecting every client?

thanks again for the great article.

Many thanks for the clear explanation

2011-01-31T12:36:28.391-05:00

Many thanks for the clear explanation

Hmm - is it something I didn't properly get in...

2011-01-31T07:22:34.885-05:00

Hmm - is it something I didn't properly get in this ? - I seem to have same cause of problem, but cannot fix it by declaring variables and using them inside my stored procedure.

My SP has approx 10 joined views (select.... ) as xxx inner join ....)
It runs in 5 seconds from sql server management studio, it takes 5 minutes or so when I run the same thing from Reporting services.

I even tried with Optimise for... and recompile.
Any other reason it could be that the same procedure takes forever when running thru reporting services ?
Jostein

Hmm. This seems to give a lot of meaning, but is i...

2011-01-31T06:19:17.131-05:00

Hmm.
This seems to give a lot of meaning, but is it something I didnt get here.
I have this SP that runs 15 seconds in SQLserver management studio, and when I run the same thru Reporting services it takes approx 5 minutes.
I've change the sp, so that it uses locally defined variables in the where statements.

Jostein
Is it still a reason why Reporting services should take 100 times longer than the actual run of the SP ? (And yes: I did try with defining the sp with recompile, seems to make no difference.

nice One !

2010-09-09T21:55:07.302-04:00

nice One !

Thanx dude. Our team sat for hours trying to figur...

2010-09-07T03:18:46.431-04:00

Thanx dude. Our team sat for hours trying to figure out why a SP takes 5min to run while the inner script took 8sec on it's own.
Applied the local vars solution and bam, 8sec.

Great explanation! Thanks! That really helped me w...

2010-08-02T08:12:23.056-04:00

Great explanation! Thanks! That really helped me with a recent problem I had

Fantastic explanation. Its a shame I spent 4 hour...

2010-07-07T11:46:02.261-04:00

Fantastic explanation. Its a shame I spent 4 hours tearing my hair out before I found this article. ;)

Thanks for the very clear explanation - it helped ...

2010-06-15T12:49:21.801-04:00

Thanks for the very clear explanation - it helped me get the execution time down from 20 minutes to 12 seconds!

SQL Server 2008 SP1 Cumulative Update 7 and SQL Se...

2010-06-07T08:18:44.525-04:00

SQL Server 2008 SP1 Cumulative Update 7 and SQL Server 2005 SP3 Cumulative Update 9 introduce trace flag 4136 that can be used to disable the "parameter sniffing" process, see http://support.microsoft.com/kb/980653/

Great Job and thanks very much! I notice this pro...

2010-05-11T08:20:06.794-04:00

Great Job and thanks very much! I notice this problem some months ago but it's your wonderful solution which help me solving this problem completely! (I was using WITH RECOMPILE procedure hint to solve this problem but it did not work well on large SQL procedures...)
Really great and wonderful!

Thanks alot dude... keep up the work!!

2010-03-17T09:40:23.790-04:00

Thanks alot dude... keep up the work!!

Great Article. Thanks

2010-03-09T06:31:12.278-05:00

Great Article.
Thanks

Thanks for this article. It's a really intere...

2010-03-08T16:51:48.252-05:00

Thanks for this article. It's a really interesting read and saved the day when one of my procs slowed down!

I have been asked Question during Microsoft interv...

2010-02-20T18:27:56.658-05:00

I have been asked Question during Microsoft interview like this "Stored Proc runs perfactly fine and few days later same Proc takes 15-20 Minutes to execute.What will be the potential problem here?

Thank you in advance

Thanks a lot, it solved mysterious too long execut...

2010-02-10T18:19:19.220-05:00

Thanks a lot, it solved mysterious too long execution of sp

Great post. Covers complete detail.

2009-12-18T08:15:14.615-05:00

Great post. Covers complete detail.

Thank you so much. Truly un-expected behavior.

2009-12-16T14:41:31.237-05:00

Thank you so much. Truly un-expected behavior.

Brilliant article. Thanks. And it's quite well...

2009-10-05T05:20:24.896-04:00

Brilliant article. Thanks. And it's quite well written so that the problem and the solution is clearly understandable.

It is very rare that I learn that have a widespread effect on how I write SQL code, but I just did today.