4

We use SQL Server 2008. When I am watching in SQL Server Profiler I found some queries that take for example 20 seconds. The same query (in a stored procedure) when executed in SQL Server Management Studio takes 2 seconds. SQL Server is under load when I catch those queries with memory and CPU at the 20 percent.

What I can do?

Improve this question
4
  • 1
    Try clearing the execution plan cache.
    – BNL
    Dec 14, 2011 at 12:55
  • Every time I've seen a slow sproc, but the same SQL runs fast, it's always resolved by recompiling the sproc.
    – UnhandledExcepSean
    Dec 14, 2011 at 13:10
  • This can be many things, the most probable ones are the execution plan and parameter sniffing.
    – Lamak
    Dec 14, 2011 at 13:25
  • @Dems I would agree with SpectralGhost on account of the problem is with plans, and you can do parameter sniffing to prevent the recompile.
    – jcolebrand ♦
    Dec 14, 2011 at 17:41

3 Answers 3

Reset to default
4

Take care when comparing stored procedure execution times to inline SQL execution times.

A stored procedure gets compiled into ONE execution plan to fit all possible parameters. Depending on your query this may not be a good plan for Any parameters, but instead the least-worst for all parameters.

When executing Inline, the optimiser can see both the query AND the parameters. It can then compile a plan specifically for that case. It can take conditions such as OR @param IS NULL and turn it into OR TRUE and then be able to pick indexes and approaches that fit that specifically.

In short, Inline and stored procedures can and do generate different plans with different performance characteristics.

For your particular case, can you show us the definition of the stored procedure?

Improve this answer
5
  • All stored procedure has acceptable execution time. In some point, all stored procedure takes 10 X to execute. Is posible that some locks on table slow down queries (stored procedures)?
    – SilverDeveloper
    Dec 14, 2011 at 15:21
  • Yes, that is a very real possibility. If most SP's read from one table, but you sometimes have a long INSERT or UPDATE on the table, the reads will have to wait until the writes finish. One way to infer this is to see if the CPU Time, Reads and Writes stay about the same, but the actual time is 10x normal.
    – Dems
    Dec 14, 2011 at 15:34
  • all queries given to the engine are treated this way, including ad-hoc queries. It's just that ad-hoc queries aren't likely to get run repeatedly, so they don't generally have as much occasion to see the caching benefits as stored procedures. But I've worked with enough ad-hoc queries to know that it is caching them just the same. for instance, see brentozar.com/blitz/single-use-plans-procedure-cache
    – jcolebrand ♦
    Dec 15, 2011 at 17:41
  • @jcolebrand - It may cache the execution plan, it may cache the tables, it certainly won't cache the results.
    – MatBailie
    Dec 21, 2011 at 23:01
  • yeah, did I say that it would cache the results? how's the edit? still give the same impression?
    – jcolebrand ♦
    Dec 22, 2011 at 2:34
0

I've seen this in the past where the query plan is "wildly wrong" - it's worth remembering that you will use different query plans when executing this via SSMS to via an application (such as one written in .Net).

You can demonstrate this if you SQL server has a linked server; connect to your "real" server, and see it run fast; reconnect to the linked server, and execute again, and you'll see the performance difference.

Improve this answer
4
  • But it's also worth pointing out that ad-hoc queries can be cached just the same as stored procedures, because often if an application sends in a query once, it's probably going to send it again and again. All you can do is let them age away, refreshing the "aging" as they're run again. So if they never age away, then it was worthwhile to cache the execution plan.
    – jcolebrand ♦
    Dec 14, 2011 at 17:45
  • @jcolebrand little harder to prove it was the query plan, and not the client application's fault when it's not a stored procedure, mind)
    – Rowland Shaw
    Dec 15, 2011 at 17:11
  • How's that? The difference between a stored procedure and a dynamic bit of SQL sent over the wire is that the stored procedure is just that, stored. Other than that, according to every dba I've ever heard talk about it, the engine considers the two the same, nowadays on SQL Server. I've never heard anybody say that they are treated differently. Additionally, query hints are given "for the optimizer" which is not the thing that stores procedures, but is the thing that we are discussing. I can put query hints in an ad-hoc query too.
    – jcolebrand ♦
    Dec 15, 2011 at 17:40
  • Also see this for further reference (from a name you may trust more than myself) brentozar.com/blitz/single-use-plans-procedure-cache
    – jcolebrand ♦
    Dec 15, 2011 at 17:45
0

It was for SQL Server 2000, but maybe will apply in your case: https://stackoverflow.com/questions/440944/sql-server-query-fast-but-slow-from-procedure

It was an opposite situation, but maybe also there is a problem with ANSI NULLS?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.