December 1, 2004 at 10:31 am
select order_origination,count(1) from orders --with (index(dt_rcvd))
where dt_rcvd between '2004-10-01' and '2004-10-31'
group by order_origination
I have issues with this query running diffently on different servers
on the development and production servers the above query
run and shows in the execution plan that it is using the index on date
on stage which is a copy of production, it will always do a table scan
instead of the index. The performance is much better with the index.
I have updated statistics sp_updatestats and
DBCC SHOW_STATISTICS (orders, dt_rcvd)
and they are the same in all environments
is there an option on sql server that could be set wrong to cause this
December 1, 2004 at 12:04 pm
Try update statictics using "UPDATE STATISTICS" with fullscan option.
Do both tables have same number of records?
December 1, 2004 at 12:18 pm
actually all three are currently backups of production
so yes they are identical
December 1, 2004 at 12:24 pm
bad
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
SET STATISTICS PROFILE ON 25 1 0 1 SETSTATON 0
(1 row(s) affected)
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
select order_origination,count(1) from orders --with (index(dt_rcvd))
where dt_rcvd between '2004-10-01' and '2004-10-31'
group by order_origination 26 1 0 1 8.0996399 1.7118025 SELECT 0
|--Compute Scalar(DEFINE[Expr1002]=Convert([Expr1005]))) 26 2 1 Compute Scalar Compute Scalar DEFINE
[Expr1002]=Convert([Expr1005])) [Expr1002]=Convert([Expr1005]) 8.0996399 0.0 1.5549121E-3 15 1.7118025 [orders].[order_origination], [Expr1002] PLAN_ROW 0 1.0
|--Stream Aggregate(GROUP BY[orders].[order_origination]) DEFINE
[Expr1005]=Count(*))) 26 3 2 Stream Aggregate Aggregate GROUP BY
[orders].[order_origination]) [Expr1005]=Count(*) 8.0996399 0.0 1.5549121E-3 15 1.7118025 [orders].[order_origination], [Expr1005] PLAN_ROW 0 1.0
|--Sort(ORDER BY[orders].[order_origination] ASC)) 26 4 3 Sort Sort ORDER BY
[orders].[order_origination] ASC) 541.87457 1.1261261E-2 7.7770902E-3 11 1.7102475 [orders].[order_origination] PLAN_ROW 0 1.0
|--Table Scan(OBJECT[TITLE_OK].[dbo].[orders]), WHERE
[orders].[dt_rcvd]>='Oct 1 2004 12:00AM' AND [orders].[dt_rcvd]<='Oct 31 2004 12:00AM')) 26 5 4 Table Scan Table Scan OBJECT
[TITLE_OK].[dbo].[orders]), WHERE
[orders].[dt_rcvd]>='Oct 1 2004 12:00AM' AND [orders].[dt_rcvd]<='Oct 31 2004 12:00AM') [orders].[dt_rcvd], [orders].[order_origination] 541.87457 1.6546155 0.0203647 1199 1.6749803 [orders].[dt_rcvd], [orders].[order_origination] PLAN_ROW 0 1.0
(5 row(s) affected)
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
SET STATISTICS PROFILE OFF 27 1 0 1 SETSTATON 0
good
StmtText
select order_origination,count(1) from orders --with (index(dt_rcvd))
where dt_rcvd between '2004-10-01' and '2004-10-31'
group by order_origination
(1 row(s) affected)
StmtText
|--Compute Scalar(DEFINE[Expr1002]=Convert([Expr1005])))
|--Stream Aggregate(GROUP BY[orders].[order_origination]) DEFINE
[Expr1005]=Count(*)))
|--Sort(ORDER BY[orders].[order_origination] ASC))
|--Bookmark Lookup(BOOKMARK[Bmk1000]), OBJECT
[title_ok].[dbo].[orders]))
|--Index Seek(OBJECT[title_ok].[dbo].[orders].[Dt_rcvd]), SEEK
[orders].[dt_rcvd] >= 'Oct 1 2004 12:00AM' AND [orders].[dt_rcvd] <= 'Oct 31 2004 12:00AM') ORDERED FORWARD)
(5 row(s) affected)
December 1, 2004 at 12:33 pm
Have you tried to rebuild the index?
December 1, 2004 at 1:07 pm
i have drop and recreated the indexes from production
i have dbcc dbreindexed
there are some documents on
sql not using the index if it isnt an effiecent
index The simple question is there an option or setting in sql anywhere that would keep the indexes from being used all the time
December 1, 2004 at 1:13 pm
Table hint is the only one.
December 2, 2004 at 7:23 am
as you can see i have that on it,
this is a management issue, in that management thinks that the server is setup wrong
So i was looking for a setting or a reason a different execution plan would be run.
I have 30 similar databases on nine servers and only the ones on stageing server are doing this. I have even rebuilt the server from scratch.
December 2, 2004 at 8:29 am
Try to educate them even though it is not that easy.
December 3, 2004 at 11:43 am
it would be an easier education if it were random servers than just the one
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply