execution plan differences

Question

Post reply

execution plan differences

danschl

SSCertifiable

Points: 5770
More actions
December 1, 2004 at 10:31 am

#109945

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

Try update statictics using "UPDATE STATISTICS" with fullscan option.

Do both tables have same number of records?

danschl SSCertifiable Points: 5770 More actions · Answer 2

actually all three are currently backups of production

so yes they are identical

danschl SSCertifiable Points: 5770 More actions · Answer 3

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)

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 4

Allen Cui-55137

SSC Guru

Points: 51650

December 1, 2004 at 12:33 pm

#532093

Have you tried to rebuild the index?

danschl SSCertifiable Points: 5770 More actions · Answer 5

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 6

Allen Cui-55137

SSC Guru

Points: 51650

December 1, 2004 at 1:13 pm

#532105

Table hint is the only one.

danschl SSCertifiable Points: 5770 More actions · Answer 7

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.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 8

Try to educate them even though it is not that easy.

danschl SSCertifiable Points: 5770 More actions · Answer 9

it would be an easier education if it were random servers than just the one