Sproc Executing Slow? It Might be This

So you have stored procedure and it’s having issues. When you run it, it returns really fast. When .NET runs it, its like a dog.

The first thing you learn about SQL Server is that it has something called ARITHABORT and it is turned on in SQL Management Studio and OFF in ADO.NET. Yeah you heard that right. OFF. So when you find this out, your first thought is WTF, mate?! Why do we have to make this hard on ourselves?

Reference post: http://sqladvice.com/blogs/gstark/archive/2008/02/12/Arithabort-Option-Effects-Stored-Procedure-Performance.aspx

So you turn it off in SQL Server Management Studio under {Tools}->{Options} and all is good right?

image

Well, you might be coming across another problem and you want to optimize the sproc so you pull the text out and start running query analyzer. First things first, compare the text of the sproc versus executing the actual sproc. You might be surprised to find that the actual execution is not as fast as the text.

 image

WTF? Why is the sproc running slower than the actual text? This is due to a bad query plan being cached by SQL. If you use the parameters that are passed in by the sproc, SQL Server tries to guess what those are and it puts in fake values. Then it caches a bad query plan.

So if you’re still with me at this point, you’re probably saying, that’s all fine and dandy Rob, but I just want to fix the freaking thing and you are giving me too much context.

The fix is so easy, it’s crazy. All you have to do is set those parameters to local variables and use the local variables in the sproc.

Create PROC Tim 
    @StartDate DateTime
AS 
 
 BEGIN
 
    DECLARE @LocalStartDate DateTime
    SET @LocalStartDate = @StartDate
    
    /* your code here */
    
    SELECT @LocalStartDate AS StartDate
    /* instead of 
        SELECT @StartDate AS StartDate
     */
    
 
 
 END

It’s a hack. And only useful when the sproc is being a non-performant nancy boy.

And here’s another good reason not to use Sprocs. I’m just saying. YMMV.

 

 

kick it on DotNetKicks.com

 

Print | posted @ Friday, November 6, 2009 12:02 PM

Comments on this entry:

Gravatar # re: Sproc Executing Slow? It Might be This
by Dewayne Christensen at 11/6/2009 4:45 PM

"not to use Sprocs"++
Gravatar # re: Sproc Executing Slow? It Might be This
by anon at 11/7/2009 9:51 AM

hello,
you can try "Optimize for unknown" in sql server 2008
...
OPTION(OPTIMIZE FOR (@pid UNKNOWN))
Gravatar # re: Sproc Executing Slow? It Might be This
by David Kemp at 11/8/2009 10:03 PM

Do you really have to use that offensive term about your non-performant stored procedures?
Gravatar # re: Sproc Executing Slow? It Might be This
by Robz at 11/9/2009 3:11 AM

I apologize for any stored procedure I offended by calling it a nancy boy. :D
Gravatar # re: Sproc Executing Slow? It Might be This
by anon at 11/9/2009 5:09 AM

I think you got that backwards... Nancy boys should be offended for being compared to sprocs.
Gravatar # re: Sproc Executing Slow? It Might be This
by SanjayU at 11/10/2009 3:39 AM

FYI; this is known as "parameter sniffing"
Gravatar # re: Sproc Executing Slow? It Might be This
by Robz at 11/10/2009 3:20 PM

@Sanjay: Thanks! I couldn't remember what it was called when I wrote this...
Comments have been closed on this topic.