September 29, 2011 at 10:45 am
On sql 2005/2008, if you auto stats on and you regularly rebuild indexes, is there a need to update statistics. I have seen different opinions on this
September 29, 2011 at 11:17 am
Rebuild redoes the stats. Reorganize doesn't. That's documented behavior.
So, if you rebuild, you already have a stat update, and doing another one (which a lot of people do) is pointless.
If you reorganize, it's often a good idea to update stats, because it doesn't do that for you.
- 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
September 29, 2011 at 11:20 am
Just bear in mind that rebuild only update's the statistics of the index that was rebuilt. It won't update the column statistics. So you may need to update those manually. No definitive answer that works for all cases, depends on table size, update frequency, query types, etc.
If in doubt, update the stats. It won't harm to update them more often, it may well harm to have queries run with stale stats.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2011 at 12:11 pm
Would everyone agree to rebuild all stats at least on a weekly basis?
September 29, 2011 at 12:20 pm
No. 😀
If you can afford the time, go ahead.
It may not be enough. I've seen cases where specific stats have to be updated daily. I've heard of cases where specific stats have to be updated every 10 minutes. I'm working with a database at the moment where weekly blanket updates are causing other problems and so I'm going to have to remove that weekly update and figure something else out.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2011 at 12:23 pm
timscronin (9/29/2011)
Would everyone agree to rebuild all stats at least on a weekly basis?
Nope. Should be hanlded based on what's actually needed, not on some blanket expectation that weekly will do.
Weekly can be far to infrequent, especially on databases that are subject to large data loads on a nightly basis or similar situations.
Weekly can be too frequent on databases that warehouse quarterly data or anything like that.
Major stat rebuilds can also be a major problem in 24X7 databases with large tables, because of locking/blocking/deadlocking.
- 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
September 29, 2011 at 12:32 pm
GSquared (9/29/2011)
... because of locking/blocking/deadlocking.
Unlikely to cause any of those, as stats updates run in READ UNCOMMITTED, but can cause extra IO load, extra CPU, memory churn, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2011 at 12:35 pm
I have a script that does rebuilds on only indexes that are needed, need one for stats that does the same
September 29, 2011 at 12:36 pm
GilaMonster (9/29/2011)
GSquared (9/29/2011)
... because of locking/blocking/deadlocking.Unlikely to cause any of those, as stats updates run in READ UNCOMMITTED, but can cause extra IO load, extra CPU, memory churn, etc.
Didn't realize that. I've only had to deal with manual stat updates once, and it was a mess, but was also a couple of years ago. I thought I remembered blocking issues from it, but I must be misremembering. (Not that that server didn't have enough blocking and deadlocking issues in other things to more than make up for them not occuring on this one point.)
Or maybe it was index rebuilds that caused blocking issues, and I'm crossing that up with the stats. Had to do that on those databases too, as part of the same maintenance processes. Is online Index rebuilding Enterprise-only, and does rebuilding an index lock the table outside of that? I seem to remember it does, but I could easily be delusional on that point.
- 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
September 29, 2011 at 1:09 pm
timscronin (9/29/2011)
I have a script that does rebuilds on only indexes that are needed, need one for stats that does the same
I'd love one of those too....
Problem with stats is there's no clear indicators that an update is necessary. You can wrangle something based on the number of rows changed since the last update, but that's what autoupdate does, so if you do you'll potentially be running into the same problems it has (some tables too often, some not often enough)
You probably want to do updates on the larger tables more often, especially any table where you've had statistics-based performance problems before. Other than that, there's no good rules or guidelines here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2011 at 1:11 pm
GSquared (9/29/2011)
Is online Index rebuilding Enterprise-only, and does rebuilding an index lock the table outside of that? I seem to remember it does, but I could easily be delusional on that point.
Yes, yes. Even Online rebuilds take locks, just short-lived ones.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2011 at 7:21 am
GilaMonster (9/29/2011)
GSquared (9/29/2011)
Is online Index rebuilding Enterprise-only, and does rebuilding an index lock the table outside of that? I seem to remember it does, but I could easily be delusional on that point.Yes, yes. Even Online rebuilds take locks, just short-lived ones.
Yeah. Soon as I asked the question, I went and researched it. Short schema lock at the end of the process for Online if I read it correctly.
Wasn't on Enterprise at the time, just Standard. That's what I'm remembering and crossing up.
- 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
September 30, 2011 at 7:37 am
GSquared (9/30/2011)
Yeah. Soon as I asked the question, I went and researched it. Short schema lock at the end of the process for Online if I read it correctly.
There's also a short-lived lock at the beginning (blocks writers, not readers iirc) to bump the table's minor version and hook in the 2-path modifications necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2011 at 7:42 am
GilaMonster (9/30/2011)
GSquared (9/30/2011)
Yeah. Soon as I asked the question, I went and researched it. Short schema lock at the end of the process for Online if I read it correctly.There's also a short-lived lock at the beginning (blocks writers, not readers iirc) to bump the table's minor version and hook in the 2-path modifications necessary.
Yep. But the schema lock is the one that's going to matter more in most cases. And even snapshot isolation has to honor schema locks because it doesn't version objects, just data.
- 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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply