Execution plan differences in QA and Prod

Question

Execution plan differences in QA and Prod

SQLBuzz

SSC-Addicted

Points: 497
More actions
July 15, 2016 at 11:34 am

#328213

I am run into an situation with a change in behavior to one query, and I'm looking for advice on how to interpret this.

Viewing 12 posts - 1 through 11 (of 11 total)

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

SQLBuzz SSC-Addicted Points: 497 More actions · Answer 1

I know if you looking into the Prod , it's trying to retrieve the data from ## million rows but at the one which has ##% cost. I will that only reason costing us low performance. I think it might missing index but i am unable to pit point the which one has the missing index.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Before I look at the query plans I have several questions:

1) are the sql server versions (and database compatibility levels) EXACTLY the same?

2) are the state of the involved tables (columns, indexes, data and statistics) EXACTLY the same?

3) are the settings on the two SQL Servers EXACTLY the same?

4) are all physical characteristics between the two servers EXACTLY the same? RAM and CPU are the main thing here.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SQLBuzz SSC-Addicted Points: 497 More actions · Answer 3

Here is my response

1) are the sql server versions (and database compatibility levels) EXACTLY the same?

AK - there are the exactly same setting

2) are the state of the involved tables (columns, indexes, data and statistics) EXACTLY the same?

AK - All the setting involved in both the tables are Exactly same

3) are the settings on the two SQL Servers EXACTLY the same?

AK - Yes Same

4) are all physical characteristics between the two servers EXACTLY the same? RAM and CPU are the main thing here.

AK - Prod is more powerful than QA environment

Robert Davis One Orange Chip Points: 28027 More actions · Answer 4

I notice that the QA plan parallelizes out much farther than the first one. How many logical CPUs does each have and what are the max DOP settings?

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

SQLBuzz SSC-Addicted Points: 497 More actions · Answer 5

Strange ...I was just looking into the setting and i had found these

LogicalCPUs on Prod is 32

LogicalCPUs on Dev is 40

MAX DOP Setting on Prod is 8

MAX DOP setting on Dev is 16

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

You also need to make sure that statistics are up to date on the tables involved and you also need to consider differences in table volume and range(s) of data involved.

Most people forget or just don't update stats on Dev or Staging/Test boxes.

--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)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

SQLBuzz (7/15/2016)
Strange ...I was just looking into the setting and i had found these
LogicalCPUs on Prod is 32
LogicalCPUs on Dev is 40
MAX DOP Setting on Prod is 8
MAX DOP setting on Dev is 16

And yet you had just told me the settings were identical on both boxes. :blink:

Also note that simply having a more powerful machine is cause for query plan differences even with every, and I mean EVERY OTHER SINGLE THING identical between the boxes/sql server/databases/etc.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SQLBuzz SSC-Addicted Points: 497 More actions · Answer 8

SQLBuzz

SSC-Addicted

Points: 497

July 15, 2016 at 1:23 pm

#1890421

mmm

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

SQLBuzz (7/15/2016)
Yeah my bad, I was told the system i identical on both the environment.
But this issues was raised recently and it was working perfectly fine last 6 months with an issues.

Most likely scenarios given new information:

A) somebody changed some setting/configuration/machine and you get a different plan

B) data/statistical differences let to a plan change in one but not the other database

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 10

I just recently published a blog post [/url]on this very topic. I'd suggest doing the checks that I did.

"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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

TheSQLGuru (7/15/2016)
SQLBuzz (7/15/2016)
Strange ...I was just looking into the setting and i had found these
LogicalCPUs on Prod is 32
LogicalCPUs on Dev is 40
MAX DOP Setting on Prod is 8
MAX DOP setting on Dev is 16
And yet you had just told me the settings were identical on both boxes. :blink:
Also note that simply having a more powerful machine is cause for query plan differences even with every, and I mean EVERY OTHER SINGLE THING identical between the boxes/sql server/databases/etc.

And now we know the reason I don't answer these types of questions. 😛 The answer is always "something is different... go figure it out". 😉

--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)