Same Query but diffirent execiution plan

Question

Same Query but diffirent execiution plan

Minaz Amin

SSChampion

Points: 11052
More actions
May 15, 2015 at 8:52 pm

#298159

Hi All,
I have very weird issue. I have same query but when executed from different server use different plan. when it runs on QA box it is faster and when it runs on PRD it is slow.
Is it possible to force SQL Server to use QA plan by giving a hint?
"More Green More Oxygen !! Plant a tree today"

Viewing 3 posts - 1 through 2 (of 2 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Minaz Amin (5/15/2015)
Hi All,
I have very weird issue. I have same query but when executed from different server use different plan. when it runs on QA box it is faster and when it runs on PRD it is slow.
Is it possible to force SQL Server to use QA plan by giving a hint?

The execution plan is the result of many factors. Forcing the use of an execution plan that works well on one machine will very likely cause the query to run even slower on a different machine. It would be far better to not rely on such trickery and troubleshoot the query for performance on the prod box.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

Better to understand why you're getting different plans. Usually this is because of differences in statistics between the machines. But, it could be other stuff. For example, differences in the configuration of the machines themselves, more or less memory, more or less CPU. It could be machine settings like the default connection settings or the cost threshold for parallelism. So, the trick is, to understand why you're seeing differences in performance and different execution plans. That understanding will help lead you to a solution.

"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