September 22, 2009 at 7:38 pm
I have a poorly performing query in my production environment and restored the nightly backup copy to another server to troubleshoot as the data in my QA and Prod environments is greatly different. The table in question is 8 million plus records and in my execution plan on the restored copy the first step shows 140000 rows being returned. However the execution plan on my production version shows 5600000 records being returned. My question is when I restored the backup did it somehow rebuild all of my indexes to make the newly restored copy perform better? Tonight I have scheduled to rebuild all of my indexes (which I do weekly anyway) to see if the problem goes away but I wanted to post this in the event that my index rebuild does not fix the problem.
Thanks in advance from the newby.
Jason
p.s. I did restore the full backup so all indexes and queries are the same in both environments.
September 23, 2009 at 4:36 am
Have you tried updating statistics on the table in question, you may be getting a poor execution plan if your statistics are out of date??
Maybe try UPDATE STATISTICS
September 23, 2009 at 2:38 pm
I did update statistics along with the index rebuild over night and things are much better now.
September 24, 2009 at 3:38 am
If you access your table via a stored procedure, the access plan will be created the first time the SP is run after SQL startup.
It is possible that on your production server a large number of rows had been added since SQL was last started and you had not done anything that would allow the plan to be refreshed. Operations such as refreshing stats or rebuilding indexes will invalidate the old plan and allow a new plan to be created when the SP is next run.
When you restored your DB to another server and ran the SP, a new plan was generated that took into account the number of rows and other stats that existed when it was run. This may be why a different plan was created.
Another factor that can influence access paths is the SP parameters that were used when the access plan was created. If certain parameter values would cause a table scan plan to be generated but other values cause an index seek plan to be generated, you can end up with a plan that does not suit all your queries.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply