Query generates different exec plan!

Question

Query generates different exec plan!

prezader

SSC Enthusiast

Points: 159
More actions
December 1, 2003 at 5:07 pm

#160989

Hello.
I am purplexed with the SQL Server's behavior where it creates different execution plan (one really worse than the other) on different servers for the same query. Databases are same, row count is same, table fragmentaion is same.
Only major difference between the two servers in memory usage. One with bad plan has its memory pegged to max.
Note that this is a query and not a proc.
Is this a valid reason for SQL server to generate different exec plan?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ferguson SSCertifiable Points: 5837 More actions · Answer 1

Are you sure that the statistics on the two are the same? Also number of processors and the options set? Exactly same service packs and such?

I wasn't aware that current memory usage was considered in the plan, but I guess it might be.

prezader SSC Enthusiast Points: 159 More actions · Answer 2

OK, the number of processors are MORE (4) on the server where bad plan is being generated! And this results in really BAAAD response ime.

Version numbers are same.

And to add to that, I am being told that the response time was OK all these days.

EdVassie SSC Guru Points: 60472 More actions · Answer 3

Why is the plan bad? Is it using parallelism? Have you tried turning this off?

Also, if you have set SQL memory to the maximum on the box, you leave very little for Windows and other applications. It may be worthwhile to reduce memory to leave 100 - 200 MB free.

All information provided is a personal opinion that may not match reality.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Jeff W SSCertifiable Points: 5207 More actions · Answer 4

Agree, what is the bad plan? What is the difference between the execution plans?

If it really is the execution plan that changes, not just performance, then look at your statistics first..

"Keep Your Stick On the Ice" ..Red Green

Jonathan SSC-Insane Points: 20427 More actions · Answer 5

Sounds like the statistics differ between the two servers. If the "slow" server has stale (or missing) statistics, it could easily choose a sub-optimal plan methodology.

--Jonathan

Keith Henry Hall of Fame Points: 3759 More actions · Answer 6

Why is the plan bad? Could you execute the following on each:

set showplan_text on

go

/* Your query here */

go

set showplan_text off

go

... and paste the results here?

Alternatively view your plan normally in query analyser and any steps in red are missing statistics. Also any steps that are different (ie table scan vs index lookup) for the same field should have the appropriate indexes rebuilt.

Keith Henry

DBA/Developer/BI Manager

Keith Henry

According to everyone I know I "do something with computers?" for a living, so there you go.

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 7

It sounds like parallelism is slowing the query down. Try upping your cost threshold for parallelism setting with sp_configure and see if that doesn't cause the slow server to choose a different plan.

Let us know.