3

This is bizarre, but I am hoping someone can help me out.

I have a stored procedure call which takes about 42 seconds to run – when called from an application connected by an ODBC connection. However, if I run that same call in SSMS (Sql Server Management Studio), it takes only 10 or 15 seconds to execute….as recorded by a trace.

This does not appear to be a network issue. I am only passing about 1200 records back to the client – and in any case, the times I gave you were coming straight from a trace duration field….so it is taking SQL Server 3 or 4 times longer to process the same call – when done via an ODBC call. I can reproduce this over and over again. What is more interesting, is that the reads and writes (taken from the trace) are a little higher for the ODBC call, but the CPU usage is 3 or 4 times what the SSMS call is.

There are other stored procs called as part of the same process, and they do not appear to be affected in the same way...or at least not to the same extent.

We are using SQL Server 2005

Any ideas about what is going on here?

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
Clinemi
  • 906
  • 6
  • 20
  • 33
  • Possibly paramter sniffing. Check both execution plans for differences. SSMS will likely have some of the `set` options different which means it won't share the plan from the other connection instead it will get a new plan generated that might be more suitable for that set of parameters. – Martin Smith Feb 14 '11 at 22:05

2 Answers2

1

Could be that you are pulling data from a "warmed" cache in SSMS. Try running it with these lines before your stored proc call in SSMS and see if it runs as quickly:

CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO

-- Your SQL begins here

As @Martin says it could also be the result of Parameter sniffing. Here is a good SO post that goes over that.

Community
  • 1
  • 1
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • Wow... that made a huge difference! It appears that cleaned out all the caches. My SSMS query took more than a minute (you might expect that), but the ODBC connection is now much faster! What gives there? – Clinemi Feb 14 '11 at 22:43
  • 2
    @Clinemi - If clearing the plan cache resolved things then I would definitely suspect parameter sniffing. – Martin Smith Feb 14 '11 at 22:47
  • Yeah, clearing the cash should have made things longer for both. Are you passing the exact same params to the stored proc everytime you test? – Abe Miessler Feb 14 '11 at 22:50
  • Also, if you cleared the cache, ran the proc through SSMS and then reran it through ODBC, then the ODBC run would have been against a warmed cache. – Abe Miessler Feb 14 '11 at 22:51
  • I believe it definitely is parameter sniffing. After executing the above code I ran the SSMS call first - that rebuilt the query plan and took a long time. However, after that point both the ODBC and SSMS calls were fast. The SSMS is still about 10-15 seconds but the ODBC is about 4 seconds! I used the same parameters on these calls and changing the parameters might change things, but the symptoms look like parameter sniffing. Thanks! – Clinemi Feb 14 '11 at 23:04
0

Sounds like the first connection time. Can you reproduce the the issue with two calls and only time the second one? Might also be worth timing the call with another library to see if it's different.

Preet Sangha
  • 64,563
  • 18
  • 145
  • 216