Fervent Coder

Coding Towards Utopia...by Rob Reynolds
posts - 278 , comments - 431 , trackbacks - 0

My Links


Rob Reynolds

Subscribe to Fervent Coder RSS
Subscribe to Fervent Coder by Email

About Me

I manage several open source projects. Need...
   ...package management for Windows?
   ...automated builds?
   ...database change management (migrations)?
   ...your application to check email?
   ...a monitoring utility?

I also write for

Like what you are reading? Want to buy me a cup of coffee?
PayPal - The safer, easier way to pay online!


Post Categories


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?


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.


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
    DECLARE @LocalStartDate DateTime
    SET @LocalStartDate = @StartDate
    /* your code here */
    SELECT @LocalStartDate AS StartDate
    /* instead of 
        SELECT @StartDate AS StartDate

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 on Friday, November 6, 2009 12:02 PM | Filed Under [ Code ]



# re: Sproc Executing Slow? It Might be This

"not to use Sprocs"++
11/6/2009 4:45 PM | Dewayne Christensen

# re: Sproc Executing Slow? It Might be This

you can try "Optimize for unknown" in sql server 2008
11/7/2009 9:51 AM | anon

# re: Sproc Executing Slow? It Might be This

Do you really have to use that offensive term about your non-performant stored procedures?
11/8/2009 10:03 PM | David Kemp

# re: Sproc Executing Slow? It Might be This

I apologize for any stored procedure I offended by calling it a nancy boy. :D
11/9/2009 3:11 AM | Robz

# re: Sproc Executing Slow? It Might be This

I think you got that backwards... Nancy boys should be offended for being compared to sprocs.
11/9/2009 5:09 AM | anon

# re: Sproc Executing Slow? It Might be This

FYI; this is known as "parameter sniffing"
11/10/2009 3:39 AM | SanjayU

# re: Sproc Executing Slow? It Might be This

@Sanjay: Thanks! I couldn't remember what it was called when I wrote this...
11/10/2009 3:20 PM | Robz
Comments have been closed on this topic.

Powered by: