Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
1

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?

share|improve this question
1  
Try clearing the execution plan cache. – BNL Dec 14 '11 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. – SpectralGhost Dec 14 '11 at 13:10
This can be many things, the most probable ones are the execution plan and parameter sniffing. – Lamak Dec 14 '11 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 '11 at 17:41

migrated from stackoverflow.com Dec 14 '11 at 17:43

3 Answers

up vote 3 down vote

Take care when comparing StoredProcedure execution times to inline SQL execution times.

A StoredProcedure 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 StoreProcedures can and do generate different plans with different performance characteristics.


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

share|improve this answer
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 '11 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 '11 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 '11 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 '11 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 '11 at 2:34
up vote 0 down vote

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.

share|improve this answer
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 '11 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 '11 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 '11 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 '11 at 17:45
up vote 0 down vote

It was for SQL Server 2000, but maybe will apply in your case: http://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?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.