7

Today, I had an issue with a stored procedure timing out (took longer than 30 seconds) when it was run from an ASP.NET web page, but executed quickly when run from SSMS (took 5 seconds).

After suspecting parameter sniffing as the culprit, I masked the input parameters, and the query executed faster.

My question is: why did this happen?

This system has been in production for more than 5 years, and this is the first time we've seen anything like this on our stored procedures. Is this "Database wear and tear"?

We've resolved the issue, so it isn't a big deal, but I'm just curious as to why this was happening.

| improve this question | |
  • Also see these on SO please: stackoverflow.com/… – gbn Jun 12 '12 at 7:12
  • I've seen this happen quite a bit. At the end of most of my DROP/CREATE PROC .sql files, I typically include an EXEC for the sproc, with realistic parameters. Thus, any time the sproc is recreated, it gets run once with good params and (should) get a good execution plan. – Jon of All Trades Jun 12 '12 at 17:01
8

Basically what happens is, when SQL Server sees a query that it needs to compile, it is going to use the first-time-called parameters to generate the execution plan. This may or may not be a good thing, but it is what happens.

For instance, say you have a table of fruit (100 rows). There are 98 rows that are Apple, and only 2 rows that contain the fruit Orange. If you query that table for Apple, then the plan will most likely compile with a Scan. This is a good thing, as it is optimized for that Apple query. But then when you want to query for the Orange, that Scan is inefficient. But it is the stored plan that is being used.

The fact of the matter is that it happens. It happens all the time. It's typically not an outstanding issue, but in some cases it can be a pretty big problem. A solution to an ongoing bad parameter sniffing problem is that you can utilize the OPTIMIZE FOR query hint to force SQL Server to use certain parameter values when generating the execution plan upon initial compilation.

| improve this answer | |
  • 3
    +1 Or use OPTIMIZE FOR UNKNOWN or OPTION RECOMPILE if the plan shape varies widely and there isn't a single value you can knowingly optimize for. – Aaron Bertrand♦ Jun 11 '12 at 16:32
  • @AaronBertrand Good points on those. I've personally never had to use OPTION RECOMPILE, as I'd rather deal with a less-than-optimal cached plan than recompiling for every call. – Thomas Stringer Jun 11 '12 at 16:41
  • In some cases OPTION RECOMPILE is better - compilation overhead can be small compared to a really bad plan. – Aaron Bertrand♦ Jun 11 '12 at 16:42
  • @AaronBertrand I believe it. Have you had to use that a bit in your past?? – Thomas Stringer Jun 11 '12 at 16:49
  • Yes, very hairy dynamic SQL for search with lots of optional parameters. OPTION RECOMPILE and later optimize for ad hoc workloads really helped. – Aaron Bertrand♦ Jun 11 '12 at 16:51
6

...but executed quickly when run from SSMS (took 5 seconds)

Rather annoyingly, the SSMS default is for SET ARITHABORT ON whereas the majority of client libraries (ADO .Net, ODBC, OLE DB) specify SET ARITHABORT OFF. Likely you had a plan "go bad" but when you attempted to replicate via SSMS, the difference in ARITHABORT resulted in a different plan being used, which was "good".

Slow in the Application, Fast in SSMS? is a great reference for this.

Why did this happen?

Most likely a recompilation was triggered and unfortunately a non-typical set of inputs were used for the compilation.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.