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
