By SilverDeveloper


2011-12-14 12:53:29 8 Comments

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?

3 comments

@MatBailie 2011-12-14 14:17:57

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?

@SilverDeveloper 2011-12-14 15:21:52

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)?

@Dems 2011-12-14 15:34:07

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.

@jcolebrand 2011-12-15 17:41:26

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

@MatBailie 2011-12-21 23:01:58

@jcolebrand - It may cache the execution plan, it may cache the tables, it certainly won't cache the results.

@jcolebrand 2011-12-22 02:34:12

yeah, did I say that it would cache the results? how's the edit? still give the same impression?

@BartekR 2011-12-14 19:18:30

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?

@Rowland Shaw 2011-12-14 12:59:30

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.

@jcolebrand 2011-12-14 17:45:40

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.

@Rowland Shaw 2011-12-15 17:11:15

@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)

@jcolebrand 2011-12-15 17:40:15

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 2011-12-15 17:45:16

Also see this for further reference (from a name you may trust more than myself) brentozar.com/blitz/single-use-plans-procedure-cache

Related Questions

Sponsored Content

3 Answered Questions

[SOLVED] T-SQL Stored Procedure Returns Before Completing

1 Answered Questions

[SOLVED] Executing a Stored Procedure from a Trigger

1 Answered Questions

[SOLVED] SQL Server stored procedure causing 100% CPU usage

1 Answered Questions

[SOLVED] How long takes populating catalog in SQL Server 2008 R2 when update a row?

  • 2013-05-31 07:34:41
  • Snake Eyes
  • 860 View
  • 2 Score
  • 1 Answer
  • Tags:   sql-server

1 Answered Questions

1 Answered Questions

1 Answered Questions

[SOLVED] don't want to be able creating stored procedures with incorrect

Sponsored Content