Query Plan Difference

Question

Query Plan Difference

Jigar Damani

Grasshopper

Points: 14
More actions
March 24, 2010 at 2:43 pm

#386575

I have a database in PROD1 and PROD2 environments.
Both the databases are identical including indexes, statistics etc.
I have SQL query(query is attached) which generates different plan in PROD1 environment as opposed to PROD2 environment.
I ran Re-index and update statistics with Fullscan on both environments but still query plan is different.
Can anyboby help me as to where the difference lies. I want to replicate PROD2 query plan in PROD1. I have attached both query plans and the query.
Note : PROD1 Env is 16 GB RAM with 4 processors
PROD2 Env is 64 GB RAM with 16 processors. (Does hardware configuration makes query optimizer select different plan ?)
Thanks in advance !!!

Viewing 5 posts - 1 through 4 (of 4 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Looks like from the plans that it is related to the increased memory and cpu available in Prod2. Prod2 plan is more efficient. I also see that you are missing an index for this query in both environments - that could make a bit of a difference.

<MissingIndex Database="[siebp]" Schema="[dbo]" Table="[S_OPTY]">

<ColumnGroup Usage="EQUALITY">

<Column Name="[ASGN_USR_EXCLD_FLG]" ColumnId="24" />

<Column Name="[X_OWNER]" ColumnId="103" />

</ColumnGroup>

<ColumnGroup Usage="INEQUALITY">

<Column Name="[STATUS_CD]" ColumnId="86" />

</ColumnGroup>

<ColumnGroup Usage="INCLUDE">

<Column Name="[ROW_ID]" ColumnId="1" />

<Column Name="[CREATED]" ColumnId="2" />

<Column Name="[CREATED_BY]" ColumnId="3" />

<Column Name="[LAST_UPD]" ColumnId="4" />

<Column Name="[LAST_UPD_BY]" ColumnId="5" />

<Column Name="[MODIFICATION_NUM]" ColumnId="7" />

<Column Name="[CONFLICT_ID]" ColumnId="8" />

<Column Name="[BU_ID]" ColumnId="9" />

<Column Name="[NAME]" ColumnId="11" />

<Column Name="[PR_BU_DNRM_FLG]" ColumnId="12" />

<Column Name="[PR_BU_MANL_FLG]" ColumnId="13" />

<Column Name="[PR_BU_SYS_FLG]" ColumnId="14" />

<Column Name="[SUM_COMMIT_FLG]" ColumnId="16" />

<Column Name="[CURCY_CD]" ColumnId="17" />

<Column Name="[PR_REP_DNRM_FLG]" ColumnId="19" />

<Column Name="[PR_REP_MANL_FLG]" ColumnId="20" />

<Column Name="[PR_REP_SYS_FLG]" ColumnId="21" />

<Column Name="[STG_START_DT]" ColumnId="35" />

<Column Name="[SUM_COST_AMT]" ColumnId="36" />

<Column Name="[SUM_DOWNSIDE_AMT]" ColumnId="37" />

<Column Name="[SUM_EFFECTIVE_DT]" ColumnId="38" />

<Column Name="[SUM_MARGIN_AMT]" ColumnId="39" />

<Column Name="[SUM_REVN_AMT]" ColumnId="40" />

<Column Name="[SUM_UPSIDE_AMT]" ColumnId="41" />

<Column Name="[SUM_WIN_PROB]" ColumnId="42" />

<Column Name="[CHANNEL_TYPE_CD]" ColumnId="47" />

<Column Name="[CURR_STG_ID]" ColumnId="50" />

<Column Name="[DESC_TEXT]" ColumnId="52" />

<Column Name="[LEAD_QUALITY_CD]" ColumnId="55" />

<Column Name="[PAR_OPTY_ID]" ColumnId="61" />

<Column Name="[PROG_NAME]" ColumnId="63" />

<Column Name="[PR_CMPT_OU_ID]" ColumnId="64" />

<Column Name="[PR_CON_ID]" ColumnId="65" />

<Column Name="[PR_DEPT_OU_ID]" ColumnId="67" />

<Column Name="[PR_OU_ADDR_ID]" ColumnId="71" />

<Column Name="[PR_OU_INDUST_ID]" ColumnId="72" />

<Column Name="[PR_POSTN_ID]" ColumnId="74" />

<Column Name="[PR_PRTNR_ID]" ColumnId="77" />

<Column Name="[PR_SRC_ID]" ColumnId="80" />

<Column Name="[PR_TERR_ID]" ColumnId="81" />

<Column Name="[REASON_WON_LOST_CD]" ColumnId="83" />

<Column Name="[SALES_METHOD_ID]" ColumnId="85" />

<Column Name="[SUM_CLASS_CD]" ColumnId="88" />

<Column Name="[SUM_REVN_ITEM_ID]" ColumnId="89" />

<Column Name="[SUM_TYPE_CD]" ColumnId="90" />

<Column Name="[X_SCORE]" ColumnId="93" />

<Column Name="[X_CATEGORY]" ColumnId="94" />

<Column Name="[X_LOCK_REASON]" ColumnId="95" />

<Column Name="[X_NUM_SEATS]" ColumnId="96" />

<Column Name="[X_SDR_PROFILED]" ColumnId="98" />

<Column Name="[X_CAD_PACKAGE]" ColumnId="99" />

<Column Name="[X_HEARABOUTUS]" ColumnId="102" />

<Column Name="[X_ROLE]" ColumnId="104" />

<Column Name="[X_SOLIDWORKS]" ColumnId="105" />

<Column Name="[X_WEB_OFFER_ID]" ColumnId="107" />

<Column Name="[X_NO_OF_TOUCHES]" ColumnId="109" />

<Column Name="[X_CONNECTED_BY]" ColumnId="110" />

</ColumnGroup>

</MissingIndex>

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Post this information also

SET STATISTICS IO ON

Your Query

SET STATISTICS IO OFF

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jigar Damani Grasshopper Points: 14 More actions · Answer 3

Hi Bhuvnesh,

I have attched the output of STTISTICS IO for both envs.

kladibeeto SSCommitted Points: 1582 More actions · Answer 4

Hi,

Yes it is possible that hardware be the reason for different execution plan (parralelism is good example)

My opinion is that optimizer decide on different plan because he need to spill data into tempdb because lack of memory on PROD1 but that is only my first impression.

I suppose that options like parametrization,paralelism and etc. are same on the both server and database.