Reasons for difference in query plans

Question

Post reply

Reasons for difference in query plans

Praveen Goud Kotha

Ten Centuries

Points: 1141
More actions
August 5, 2010 at 3:09 am

#224374

Hi..
I have observed the difference in execution plans when i m trying to execute the same stored procedure on different computers..
can any one help to find out why it is..??
[font="Comic Sans MS"]Praveen Goud[/font]

Viewing 14 posts - 1 through 13 (of 13 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

steveb.

SSC-Forever

Points: 46733

August 5, 2010 at 3:25 am

#1202587

The statistics may be out of date one server.

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 2

In addition to stats it could be

- indexes

- CPU/Mem

But I would lean toward stats...

Just check that the connection details are the same, I have has issues when thing like "SET

ARITHABORT ON" is different on each connection.. If you are running it through SSMS then check the "Query Option - Adv"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Different volume of data

Different indexes

Different hardware

Different database load

Different statistics

Different set options

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Praveen Goud Kotha Ten Centuries Points: 1141 More actions · Answer 4

Apart from all the above,

is there any chances because of change in the service packs releases of sql server..

[font="Comic Sans MS"]Praveen Goud[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Quite likely.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398880 More actions · Answer 6

In addition to Gail's list:

Different Parameters

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Praveen Goud Kotha Ten Centuries Points: 1141 More actions · Answer 7

Grant Fritchey (8/5/2010)
In addition to Gail's list:
Different Parameters

Hi Fritchey..

happy to see your post for my topic..

Nice to meet you in this way..

And you said that Different Parameters also leads to difference in execution plans..

just confirm me that whether the difference is in no. of parameters or different input data for the procedure..

[font="Comic Sans MS"]Praveen Goud[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Praveen Goud Kotha (8/5/2010)
just confirm me that whether the difference is in no. of parameters or different input data for the procedure..

Yes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398880 More actions · Answer 9

GilaMonster (8/5/2010)
Praveen Goud Kotha (8/5/2010)
just confirm me that whether the difference is in no. of parameters or different input data for the procedure..
Yes.

What she said.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Praveen Goud Kotha Ten Centuries Points: 1141 More actions · Answer 10

GilaMonster (8/5/2010)
Praveen Goud Kotha (8/5/2010)
just confirm me that whether the difference is in no. of parameters or different input data for the procedure..
Yes.

??

[font="Comic Sans MS"]Praveen Goud[/font]

Grant Fritchey SSC Guru Points: 398880 More actions · Answer 11

Praveen Goud Kotha (8/5/2010)
GilaMonster (8/5/2010)
Praveen Goud Kotha (8/5/2010)
just confirm me that whether the difference is in no. of parameters or different input data for the procedure..
Yes.
??

She means that yes, the number of parameters can make a difference in the plans created, and, yes, different input data for the procedure can cause different plans to be created.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Praveen Goud Kotha Ten Centuries Points: 1141 More actions · Answer 12

Thank you all the members for their replies/suggestions who participated in my topic..

especially for Fritchey..

If any other reasons for "Reasons for difference in execution plans"..

Please dont forget to post here..

what i mean to say is: i have solved my issue(it is because of difference in service packs(2 and 3) )..

but more answers are welcomed to help others too...

[font="Comic Sans MS"]Praveen Goud[/font]

Grant Fritchey SSC Guru Points: 398880 More actions · Answer 13

Praveen Goud Kotha (8/5/2010)
Thank you all the members for their replies/suggestions who participated in my topic..
especially for Fritchey..
If any other reasons for "Reasons for difference in execution plans"..
Please dont forget to post here..
what i mean to say is: i have solved my issue(it is because of difference in service packs(2 and 3) )..
but more answers are welcomed to help others too...

Not especially me, especially Gail. She had the definitive list.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning