Quer runs well on its own but when put into stored procedure, it runs slowly

Hi,

Appreciate if any one can provide some pointers on this issue on a stored procedure that I have.

The required indexes (both clustered and non-clustered) have been created in the database tables.

When the T-SQL statements in the stored procedure are run in the SQL Query directly, it performs well and returns the results back within seconds.

However, the stored procedure is run in the SQL Query window directly, it takes minutes before it returns.

When the execution plan is examined for both, they are both different and it appears that when executing the stored procedure, it doesn't make use of the indexes, whereas in the first case of executing the same T-SQL statements directly, it does make use of the indexes.

We tried using the WITH RECOMPILE attribute to refresh the execution plan, but it doesn't seem to work for the stored procedure.

Wonder if anyone got any ideas or encounter anything similar?

Thanks,

Desmond

Can you post the SP and the DDL/Indexes?

|||

Hi Desmond T,

> When the T-SQL statements in the stored procedure are run in the SQL Query directly, it performs well and

> returns the results back within seconds.

> However, the stored procedure is run in the SQL Query window directly, it takes minutes before it returns.

Can you show us some light, please?

Look in BOL for auto-parameterization and also about the statistics used by the query optimizer. If you executed the "select" statement from QA or SSMS, using variables to simulate the parameters in the stored procedure, like in:

Code Snippet

use northwind

go

select orderid, customerid, orderdate

into dbo.t1

from dbo.orders

go

create index t1_orderdate_nu_nc_ix

on dbo.t1(orderdate)

go

dbcc show_statistics('dbo.t1', 't1_orderdate_nu_nc_ix') with STAT_HEADER, DENSITY_VECTOR, HISTOGRAM

go

create procedure dbo.usp_p1

@.orderdate datetime

as

set nocount on

select orderid, customerid, orderdate

from dbo.t1

where orderdate = @.orderdate

go

dbcc freeproccache

dbcc dropcleanbuffers

go

declare @.orderdate datetime

set @.orderdate = '19960704'

select orderid, customerid, orderdate

from dbo.t1

where orderdate = @.orderdate

go

dbcc freeproccache

dbcc dropcleanbuffers

go

exec dbo.usp_p1 '19960704'

go

drop table dbo.t1

go

drop procedure dbo.usp_p1

go

They seem to be the same, but the query optimizer uses different statistics to estimate cardinality and based on those statistics, it will choose an execution plan. In the sp case, the query procesor will use the values from the histogram, but for the query, it will use the "All density" value for that column and could be that those values yield different execution plan.

In my box, I got these values for "Estimated Number of Rows".

sp --> 1

query using variables --> 1.72917 (0.002083333 * 830) See "All density" value from the result of dbcc

The query optimizer chose to scan the table for the query using variables and an "index seek" operation followed by bookmark lookup for the sp.

One way of testing the query in QA / SSMS is using sp_executesql, parameterizing the statement.

Code Snippet

declare @.sql nvarchar(4000)

set @.sql = N'select orderid, customerid, orderdate from dbo.t1 where orderdate = @.orderdate'

exec sp_executesql @.sql, N'@.orderdate datetime', '19960704'

go

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx

AMB

|||hi,

i had a similar problem before, can you post your proc?, i resolved my problem by tweaking the proc a bit.

/rh4m1ll3