April 9, 2010 at 9:45 am
Hi,
I'm wondering if anyone might be able to share some insight into how to resolve this problem:
I have a very slow-running query on our Production environment that takes 40+ min. When I run an execution plan on it, I see it is doing a full table scan.
I restored a 3-hour old copy of this production database to a development environment. I run the same query and it returns in a few seconds. The execution plan in this dev environment shows the query using the index and rowid lookup as expected. The table is indexed identically in both environments.
The data didn't change significantly in the 3 hours between the backup and restore. (This situation was also occuring yesterday so I'm not focused on *today's* data specifically.)
Also, I don't see isn't any blocking occurring in the Production environment.
Obviously, the development environment does not have the user-load on it that we have in production.
Can anyone offer suggestions on what else may be a factor here or what may contribute to the explain plan being different between the two? I realize there may still be some things I'm missing and would appreciate anyone's input on what else to consider here.
Thanks,
Matt Smith
April 9, 2010 at 11:30 am
It's possible that you have some skewed data that when used to generate the execution plan, it results in a bad plan, but when different data is used, you get a good plan. To check for this, be sure that you are passing identical parameters on both servers. Also be very sure that neither server is updating statistics or rebuilding indexes on a different schedule than the other.
It's also possible that the statistics have changed, but not updated and the execution plan on production is older than the new plan you created when you ran the query on a new server. Try running a recompile on the production procedure and see if the plan changes to match what's on your test machine.
Those are the things that come immediately to mind.
"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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply