June 10, 2008 at 8:36 am
Wondering if anyone has seen a significant degradation in query performance after updating stats in SQL 2005?
I recently did a reorg of indexes and update to stats in a new environment that had never had any type of maint. plan ran on it before and immediately saw a decrease in performance on certain querys.
June 10, 2008 at 11:58 am
hmm it's a bit of a vague question. technically this might happen - try updating stats with a full scan and see if that helps.
could be you need some better indexes?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 10, 2008 at 12:12 pm
Usually just the opposite. Not updating stats for ages on end leads to slower & slower running procs and then the stats get updated and everything speeds up.
It could be you got bad execution plans after a recompile? Just a guess. More detail would be needed.
"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
June 10, 2008 at 12:25 pm
I would expect the first run of a proc after a reindex and re-stat to be slow, since it's redoing its execution plan. After the first run, however, I would expect it to be faster. Thus far, that's been my experience with it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 10, 2008 at 12:55 pm
I agree it is not the typical situtation, that is why I am a little stumped. The update to stats was with a full scan. And it didn't just run slower the first time as you might think, it stayed at a steady slow crawl.
I ended up doing normal procedure "tweeking" to get the query to at an acceptable rate. Typical performance tuning that would have increased execution speeds prior to the maintainence I'm sure.
Just thinking that it must have had something to do with the update to stats....maybe negativly affecting the query plan in some way? Maybe an update to stats and the way they are system generated in 2005 might have had something to do with it?
Thanks for the responses....
June 10, 2008 at 1:03 pm
Updating the stats will certainly lead to changes in the query plan, depending on what happened in the stats. It's possible that you had a plan that was working well with your data set, despite being out of date and when the plan was updated it revealed issues with joins or indexes that had been masked by the earlier plan. I've seen that type of thing happen in 2000 and 2005.
"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
June 10, 2008 at 1:42 pm
especialy after the first time you refresh statistics, always perform a free proccache so your plans are recompiled using the new statistics.
also take a snapshot of "missing indexes" (performance dashboard) pre update stats.
Or better stop/start:crazy: so these start from scratch.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 11, 2008 at 10:50 am
If there are many trivial queries and those are the dominant in the server there are cases in which 2000 is faster than 2005. There have been many patches released after SP2 to address many of those cases but I am not sure if you want to go that route 😉
* Noel
June 11, 2008 at 2:47 pm
RockStar (6/10/2008)
Wondering if anyone has seen a significant degradation in query performance after updating stats in SQL 2005?I recently did a reorg of indexes and update to stats in a new environment that had never had any type of maint. plan ran on it before and immediately saw a decrease in performance on certain querys.
I actually had that happen to me before in SQL 2000. I looked at the stat histogram and at the comparative plans, and in one particular query, it changed the plan it chose to a suboptimal (slooooow!) plan. It is certainly possible, because RDBMS's use heuristics, not algorithms to find the best plan. I don't have any advice, just a "been there, done that" comment. Good luck!
Eric
June 11, 2008 at 2:54 pm
Interesting! And that happened to you after a update to stats?
So in 2005 are old query plans retained? Do you know of a way for me to view what the query plan was and compare it to what it is now to see how it changed? And if so, then I would assume I could use maybe a query hint to force it to use the old plan??
Thanks again for the response. Nice to know there are others who have felt my pain. 😉
June 11, 2008 at 2:58 pm
Have you checked out parameter sniffing? I'm kind of inline with what Grant was getting at, where it made some bad choices based on the new stats. There was also something Jeff ran into with Top(@n) which turned a highly efficient merge join into a slower loop join. Forcing that particular statement to recompile or giving it a join hint fixed that issue.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply