1

I have a stored procedure that is called by a website to display data. Today the web page has started timing out so I got profiler going and saw the query that was taking too long. I then ran the same query in management studio, under the same user login, and it takes less than a second to return.

Is there anything obvious that could be causing this? I can't think of a reason why when ASP calls the stored proc it takes 30 secs but when I call it it's fine.

Thanks

Blootac
  • 525
  • 4
  • 17
  • Is there a lot of concurrency going on? And what about parameters? – Jonathan van de Veen May 11 '10 at 08:18
  • Is SMSS connected to a local instance or the same remote DB? Just wondering if it's the amount of data the query returns that's causing the problem (which you wouldn't see locally with a shared memory type of connection for example). – Paolo May 11 '10 at 08:21
  • Just tried from a different db server on a different geographical site and it stil runs in under a second. It's only returning a few k of data, nothing major. If I'm going to investigate the parameter sniffing route, is there a way I can recreate the problem? I'm only having the issue from the website, so short of playing with the live site (which i'm a bit hesitant about) what can I do? – Blootac May 11 '10 at 08:34
  • There are quite a few inserts, a few hundred a second, but very few reads from this database. Parameters are smalldatetime, datetime, char 6, char 7 and char 1. I'd have just expected it to recreate the issue no matter where I run the query. – Blootac May 11 '10 at 08:38

5 Answers5

1

I guess, there might be two reasons:

  1. Network problem
  2. Parameter sniffing
Cagdas
  • 819
  • 6
  • 6
1

This is usually because some of the SET-tings differ between the Management Studio connection and the ASP connection, such as SET ARITHABORT. This wouldn't explain why it's only started being problematic today from the website call, but there's a fair chance it's related.

Rob
  • 45,296
  • 24
  • 122
  • 150
  • There's also some good answers in http://stackoverflow.com/questions/2736638/sql-query-slow-in-net-application-but-instantaneous-in-sql-server-management-stu/ that may well be related – Rob May 11 '10 at 08:42
  • I'm not sure that it is the Settings per se that would explain the speed difference? More usually if you change one of these settings you will get a new or different execution plan so it looks like changing the setting fixed it but actually the culprit was parameter sniffing. – Martin Smith May 11 '10 at 09:08
  • I experienced this, too, with ARITHABORT. The issue was to query an index on a computed column, which performs better when ARITHABORT is set. – devio May 11 '10 at 20:20
0

It seemed to be parameter sniffing... I've stopped the sniffing by assigning the passed in parameters to local variables and it seems to be fine at the moment (i.e. it's running under a second from the website again). It'll be interesting to see if it stays like this or will degrade again.

I had assumed running with the option RECOMPILE would have temporarily 'fixed' the parameter sniffing problem for the query in question but it didn't.

Ah well. Thank you everyone for answering. I'll see what happens

Blootac
  • 525
  • 4
  • 17
  • If this ends up being the answer, please accept it once you're sure. I mention it since you're new here, and welcome! – SqlRyan May 11 '10 at 08:57
0

We had a similar issue with our IVR - when I ran a query through SSMS, it returned instantly, but when it was run through a webservice accessed by our IVR, it would time out about 20% of the time - really odd.

I ended up running SQL Profiler to see the queries being submitted and then added some additional indexes per the recommendations of the Index Tuning wizard, which sped up the IVR query to under a second every time. I suspect the problem was also something to do with parameters, and while I didn't compare the execution plan between the two different venues, I suspect they were quite different. SQL Profiler will help you sort this out, though, since you can see the query actually submitted to the engine, as well as the execution plan it uses to fetch the data.

SqlRyan
  • 33,116
  • 33
  • 114
  • 199
-1

Sounds like a dead lock.

Chris Bednarski
  • 3,364
  • 25
  • 33
  • No it doesn't! That would cause one of the participating transactions to be rolled back and you'd get an error message telling you that a deadlock had occurred. – Martin Smith May 11 '10 at 09:05