11

I have a query from a web site that takes 15-30 seconds while the same query runs in .5 seconds from SQL Server Management studio. I cannot see any locking issues using SQL Profiler, nor can I reproduce the delay manually from SSMS. A week ago, I detached and reattached the database which seemed to miraculously fix the problem. Today when the problem reared its ugly head again, I tried merely rebuilding the indexes. This also fixed the problem. However, I don't think it's necessarily an index problem since the indexes wouldn't be automatically rebuilt on a simple detach/attach, to my knowledge.

Any idea what could be causing the delay? My first thought was that perhaps some parameter sniffing on the stored procedure being called (said stored proc runs a CTE, if that matters) was causing a bad query plan, which would explain the intermittent nature of the problem. Since both detaching / reattaching and an index rebuild should theoretically invalidate the cached query plan, this makes sense, but I'm unsure how to verify this. Additionally, why wouldn't the same query (copied directly from SQL Profiler with the exact same parameters) exhibit the same delay when run manually through SSMS?

Any thoughts?

Chris
  • 27,596
  • 25
  • 124
  • 225
  • Is SSMS passing on some hints or a different connection string than the way ADO.net does it? – shahkalpesh Nov 17 '09 at 18:12
  • Yeah I'm connecting with different credentials. Unfortunately, for now, the problem has gone away, so I can't test with identical connection settings until the problem rears its head again. – Chris Nov 17 '09 at 18:56
  • @Chris: Could you post your own answer with your findings with what worked in your case? – shahkalpesh Nov 18 '09 at 05:57

5 Answers5

8

I know I am weighing in on this topic very late, but I wanted to post a solution that I found when having a similar issue. In brief, adding the SET ARITHABORT ON command at the outset of my procedures brought website query performance in line with performance seen from SQL Server tools. This option is typically being set on the connection when you run a query from QA or SSMS (you can change that option, but it is the default).

In my case, I had about 15 different stored procs doing mathematical aggregates (SUMs, COUNTs, AVGs, STDEVs) across a fairly sizeable set of data (10s to 100s of thousands of rows) - adding the SET ARITHABORT ON option moved them all from running in 3-5 seconds each to 20-30ms.

So, hopefully that helps someone else out there.

Jeremy A
  • 81
  • 1
  • 1
  • 2
    +1 because your answer led me to a workaround for my problem. I had a problem where a sproc was slow when called from ADO but fast in SSMS. Changing the sproc to include SET ARITHABORT ON cause it to speed up. (On the other hand, that could have just forced it to recompile and invalidate a bad cached execution plan.) I also found an interesting discussion on this: http://dba.stackexchange.com/questions/9840/why-would-set-arithabort-on-dramatically-speed-up-a-query I think the bottom line is SSMS was getting a different execution plan because it sets the option on by default and ADO doesn't. – Holistic Developer Mar 09 '12 at 20:50
6

If a bad plan is cached then the same bad plan should be used from SSMS too, if you run the very same query with identical arguments.

There cannot be better solution that finding the root cause. Trying to peek and poke various settings in the hope it fixes the problem will never give you the confidence it is actually fixed. Besides, next time the system may have a different problem and you'll believe this same problem re-surfaced and apply a bad solution.

The best thing to try is to capture the bad execution plan. Showplan XML Event Class Profiler event is your friend, you can get the plan of the ADO.Net call. This is a very heavy event, so you should attach profiler and capture it only when the problem manifests itself, in a short session.

Query IO statistics can also be of help. RPC:Completed and SQL: Batch Completed events both include Reads and Writes so you can compare the amount of logical IO performed by ADO.Net invocation vs. SSMS one. Large difference (for exactly the same query and params) indicate different plans.sys.dm_exec_query_stats is another avenue of investigation. You can find your query plan(s) in there and inspect the execution stats.

All these should help establish with certitude if the problem is a bad plan or something else, to start with.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • 1
    The number of reads for the "bad" query was significantly higher than the number of reads for the query from SSMS. I too thought that SSMS should use the same query plan if the exact same query was entered, but is there a scenario in which this may not be the case? – Chris Nov 17 '09 at 18:02
  • If any @parameter *types* are different or if the connection settings are different then the plan cannot be reused and SSMS will compile its own plan. Does your query has any @variables in it? – Remus Rusanu Nov 17 '09 at 18:32
  • I bet it was the connection settings. I was logging in to SSMS using windows auth while ASP.NET uses the sql user. That clears that up. Now, on to catching the bad execution plan in action.. :( – Chris Nov 17 '09 at 18:39
  • Nowdays SQL Server too has the ability to lock plans with the `USE PLAN` hint, see http://msdn.microsoft.com/en-us/library/ms186954.aspx – Remus Rusanu Nov 17 '09 at 19:00
2

I have been having the same problem. The only way i can fix this is setting ARITHABORT ON. but unfortunatley when it occurs again i Have to set ARITHABORT OFF.

I have no clue what ARITHABORT has to do with this but it works, I have been having this problem for over 2 years now with still no solution. The databses i am working with are over 300GB so maybe it is a size issue...

The closest i got to resolving this problem was from an earlier post Google Groups post

Let me know if you have managed to completely solve this problem as it is very frustrating!

Eli Perpinyal
  • 1,706
  • 3
  • 19
  • 35
0

Is it possible that your ADO.NET query is running after the system has been busy doing other things, so that the data it needs is no longer in RAM? And when you test on SSMS, it is?

You can check for that by running the following two commands from SSMS before you run the query:

CHECKPOINT
DBCC DROPCLEANBUFFERS

If that causes the SSMS query to run slowly, then there are some tricks you can play on the ADO.NET side to help it run faster.

RickNZ
  • 18,448
  • 3
  • 51
  • 66
0

Simon Sabin has a great session on "when a query plan goes wrong" ( http://sqlbits.com/Sessions/Event5/When_a_query_plan_goes_wrong ) that discusses how to address this issue within procs by using various "optimize for" hints and such to help a proc generate a consistent plan and not use the default parameter sniffing.

However I've got an issue with and ad-hoc query (not in a proc) where the SSMS plan and the ASP plan are exactly the same - clustered index / table scan - and yet the ASP query takes 3+ minutes instead of 1 second. (In this case table-scan happens to be a decent answer for fetching the results.)

Anyone care to explain that one?

Will
  • 419
  • 4
  • 13