Stored Proc: Fast in SSMS, Slow in ASP.Net

Question

Stored Proc: Fast in SSMS, Slow in ASP.Net

goksly

SSChasing Mays

Points: 638
More actions
August 26, 2010 at 2:58 am

#387662

I have a stored proc which runs in about 10 seconds on SSMS, but when I run it via ASP.Net (against the same database server) it takes more than 10 times as long to run. I've profiled both and the exact same SQL is sent to the box as per below.
ASP.Net
----------------------------------------------------------------------------------------
text data: exec dbo.usp_PalletCharge @month=8,@year=2010,@clientId=NULL
cpu: 62078
reads: 13785644
writes: 0
duration: 123776
SSMS
----------------------------------------------------------------------------------------
text data: exec dbo.usp_PalletCharge @month=8,@year=2010,@clientId=NULL
cpu: 20060
reads: 27570
writes: 28
duration: 10697
I've run them on the same account and I get the same results as above. The only difference is when I look at Activity Monitor when I'm running it via ASP.net the session id first has 'PAGEIOLATCH_SH' on the wait type and then this changes to 'IO_COMPLETION'. Also when I do 'Details' on the Session it has 'Harmony.dbo.usp_PalletCharge;1' (Harmony being the database name) instead of 'exec dbo.usp_PalletCharge @month=8,@year=2010,@clientId=NULL' even though profiler says the exact same text data is recieved.
Does anyone have a clue why this is taking longer to run? 🙁

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 1

On a guess, check the ANSI settings for the connections. I'll bet you one of them is different.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

goksly SSChasing Mays Points: 638 More actions · Answer 2

Don't suppose you could tell me how to do such a thing? Also, not sure if this is more of a SQL or ASP question, but is there any way to get back the execution plan for the one being run from the ASP.net (or any none SSMS application)? I'm pretty sure I remember being told if I get an XML execution plan I can load it into SSMS to get the visual report? Would be interesting to see exactly what the ASP.Net one is doing...

goksly SSChasing Mays Points: 638 More actions · Answer 3

I've tried running:

select *

from sys.dm_exec_sessions

And they seemed to be both have the same ANSI stuff.

goksly SSChasing Mays Points: 638 More actions · Answer 4

Actually just spotted one difference (except for the normal stuff you'd expect, login name etc) was:

arithabort

0

arithabort

1

It was set to '1' on SSMS. Could this cause the problem? If so, (or if I need to test) how do you change this?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Here is an example of how to do that

Dim conn As New SqlConnection(Config.GetConnectionString("SiteSqlServer"))

Dim command As New SqlCommand("set arithabort on;", conn)

command.Connection.Open()

command.ExecuteNonQuery()

Dim dc = New SiteDataContext(conn)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 6

Yeah, that setting could certainly result in different execution plans.

To see the plan, try querying the DMV sys.dm_exec_query_plans. You'll need to get a plan handle, probably from sys.dm_exec_query_stats and you can combine with sys.dm_exec_sql_text to see the query itself. That'll get you want you want.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

goksly SSChasing Mays Points: 638 More actions · Answer 7

Well I put that into the stored proc (as I assumed it would have the same affect) and the problem has gone away? But when I take it out (or even put ARITHABORT OFF in the stored proc) the query now takes 10 seconds all the time? Not sure if it's a good or bad thing.

Thanks to everyone for their input but unless I can reproduce the error I don't think I can take it any further at the moment. If it rears it's head I'd defo get the execution plan for that query 🙂

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

You're welcome

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events