February 7, 2011 at 10:16 am
I have a query that runs in SQL 2005 production system that takes about 1 minute to return the resultset (11 rows)
If i refresh the data from prodution to test system using either TDP backup/restore OR attach the test database to a copy/paste of the production mdf and ldf files, the query runs in a second in test.
I tried rebuilding the indexes in prod as i saw some fragmentation but didnt help. The database size is 150 mb. Performance is hardware dependent but I do not think it is the case here. Auto update stats in turned ON.
My goal is to replicate the issue in test. Any thoughts on what I might be missing?
Appreciate your help in advance.
Thanks
Sudhakar
February 7, 2011 at 10:32 am
February 7, 2011 at 10:35 am
Check for lock concurrency issues at production, might be running into other processes.
When you run the task on QA, does the first run also take 10-11 seconds, and the later ones take 1 second? If so, that's page cache'ing. Your prod system might be under memory pressure and is dumping the data from memory too quickly, so it's got to return to disk to get it.
Otherwise, check the wait-state on the spid in the production and see what it's waiting for while it's running.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 7, 2011 at 4:01 pm
Thanks Gianluca and Craig for the suggestions.
The explain plans are different in prod and test although test is a clone of prod.
The wait time and type are both zero when the query is running in production. Caching is not a issue since the query has been consistently running the same (long) time in production within a few seconds apart of running each time.
I will continue to research but please let me know if you have any further thoughts.
Thanks
Sudhakar
February 8, 2011 at 7:40 am
smushti (2/7/2011)
Thanks Gianluca and Craig for the suggestions.The explain plans are different in prod and test although test is a clone of prod.
The wait time and type are both zero when the query is running in production. Caching is not a issue since the query has been consistently running the same (long) time in production within a few seconds apart of running each time.
I will continue to research but please let me know if you have any further thoughts.
Thanks
Sudhakar
I'll bet if you forced a recompile in production the plan would change. Sounds like the statistics have aged and the plan created in test was different. Or, you used different parameters in the test environment.
"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
February 8, 2011 at 7:50 am
Grant Fritchey (2/8/2011)
I'll bet if you forced a recompile in production the plan would change. Sounds like the statistics have aged and the plan created in test was different. Or, you used different parameters in the test environment.
+1 here,
If you look at the execution plans on the live (slow) server you should be able to retrieve the compiled parameter values from the plan and reproduce on test.
February 9, 2011 at 4:05 am
This issue might be similar to something I'm experiencing on a database of mine.
I'm adding the link to that topic, in case something interesting comes up:
http://www.sqlservercentral.com/Forums/Topic1060705-146-1.aspx
-- Gianluca Sartori
February 9, 2011 at 1:15 pm
Some more additional information from the developers....the query is hardcoded within the application and executed on SQL database. It is NOT being executed within a stored procedure.
Also, when the query runs with a date value say 12/01/2010, the esitmated vs actual rows are close in the "Acutal execution plan". If the exact same query runs with another date value say 12/04/2010, the estimated and actuals in the plan are way different (estimated is 100 vs actual is 20,000)
I am looking for options on clearing the cache so the execution plan gets re-generated for the specific SQL with performance issue.
I bounced the database (not the instance) but didnt see any performance change in the SQL.
We are on on SQL 2005. DBCC FREEPROCCACHE i believe would clear systemwide...not a specific plan in the database.
Regathering the stats might help but it will affect the plan for other queries.
I am running out of options. Any suggestions please?
Thanks
Sudhakar
February 9, 2011 at 4:08 pm
Problem solved !!
I just ran the stats in production. The query is running under a second.
Auto update stats is turned ON and I dont think stats was the real issue.
REASON: when i copied the database from prod to test, the same stats carried from prod to test. The query ran like a flash in test. If stats were bad, the query should have been slow in test as well !
So what could have solved the issue? --> Running stats cleared the SQL plan from the cache and recreated the plan. This is what I think solved the problem.
Thanks to everyone who responded and provided valuable suggestions/clues in this thread.
Sudhakar
February 9, 2011 at 5:00 pm
I think your assessment of the issue is accurate. I'm glad things are back on track. I'd suggest you might want to set up some statistics maintenance. The auto update of stats is good, but not enough for most people.
"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 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy