Why does the Query Execution plans differes from prod to lower environment?

Hi there,
    We are facing some trouble in executing few queries which takes more time(1:30) in Production server but whereas in lower environment(staging DB) its takes just 7 seconds to bring 10 records of data. To test this issue , I have taken the copy of Production DB and restored it in the same lower environment where the staging DB is located. When i tested the query against the production copy, again it was taking more or less same time 01:25 sec to execute.

1)I observed that that the execution plans is different from both the databases . Not sure why it is ?

2)I have tried updating the stats and rebuild the indices. Again the same time to execute and no change in execution plan.

3) Can somebody shed some lights on this issue? Is this because of the job which i configured few months back to perform rebuild indexed and Statistics update?

Deepak KumarEngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You didn't provide both execution plans so we can analyze them for you.
My guess is there is difference in the indexes. Sometimes even with the same indexes, the execution plan can differ if there are an huge difference on the table sizes. And at last, you may have statistics out of date or index fragmentation, meaning that you should run update statistics or reindex.
Pawan KumarDatabase ExpertCommented:
As per me stats and SET options will differ the execution plans.
Look at this web: https://www.brentozar.com/product/mastering-server-tuning-wait-stats/

Beside the (possibly expensive) class you may download several free tools which will tell what's wrong with your queries. You should start with SP_BLITZ and SP_BLITZCACHE.

And remember: Sometimes you have to provide certain query hint to tell SQL Server optimizer what to do and how to do it...
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

Deepak KumarEngineerAuthor Commented:
Attached is the staging DB which performs the query execution in 7 seconds and Prod copy DB which takes 1Min 30 sec to run the same query
The problem is in your table function... which is then joined to itself. This requires expensive indexes creation more than one time etc. etc.  It is almost impossible to predict the number of rows in such case and the plan shows it clearly. Estimated number of rows/executions is VERY different from actual number of rows/executions. To optimize the query under such conditions is nearly impossible.

Replace the table function by native SELECT FROM and you'll see the difference.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
The vast majority of these complex execution plans is determined by the function hat is being executed. To the optimizer a function is a "black box". I might also add the it appears you are necessarily running that function twice despite putting the first execution into a cte, you then join that cte to an identical run of that same function. Looks weird to me.
      result_set AS (
                        ROW_NUMBER() OVER (ORDER BY GPID ASC) AS [_rownumber]
                      , [_key] =                              EmployeeDataIssueID
                  FROM dbo.fn_EmployeeDataIssues1('Ratings', 'bp1\sgkosf')
                  WHERE 1 = 1
    , GPID
    , EECode
    , Name
    , _info
    , EmployeeDataIssueID
    , WarningType
    , Message
    , Impact
    , Action
    , Ignore
    , Comment
    , LastModifiedBy
    , LastModifiedAt
    , EmployeeGroup
    , Status
    , LatestStartDate
    , HomeCountry
    , HostCountry
    , HomeSegment
    , HostSegment
    , SubSegment
    , SPU
    , BU
    , PrimaryFunction
    , SecondaryFunction
    , totalrowcount = (
            FROM result_set
FROM dbo.fn_EmployeeDataIssues1('Ratings', 'bp1\sgkosf')
JOIN result_set ON [_key] = EmployeeDataIssueID
WHERE [_rowNumber] BETWEEN @betweenFrom AND @betweenTo

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
Unfortunately you are using a table function name fn_EmployeeDataIssues1 and we can't see what that function is doing.
By the query execution we can see that there are some table scans (EmployeeDataIssues1, DataIssuesType1) and that's not good for the query performance.
As a resume, table scans can be avoid with the creation of the proper indexes. You can also improve your main query as for example, removing the ORDER BY clause but in my opinion the big issue must be in the table function that I've referred above but without knowing the code we can't do much here.
You may ask EE administrators to remove the attached plans from this question and then select the solution instead of the deletion.
I don't see any sensitive info in the plan. The plan contains a few column names from your database, then it contains number of rows. Maybe one text constant.... No server names, no passwords, no table data etc.

I would like to know how could this info be used to disclose some secrets?
We don't know the reason of Deepak's silence so the only option is to select all the three valid answers.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.