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?
thanks
Deepak
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?
thanks
Deepak
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...
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...
ASKER
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
Staging_DB.sqlplan
Prod_Copy_Test.sqlplan
Staging_DB.sqlplan
Prod_Copy_Test.sqlplan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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.
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.