July 16, 2009 at 9:59 am
Hi Folks
I am trying to determine Best Practice on creating a nightly full-backup maint plan
I have read so many documents, that I am D-ZZZZZZZZZZZZZZ
Anyway, so far I have deterimined this approach:
Start
DBCC Check
Full-Backup
Rebuild Indexes
Finish
I am reading conflicting articles on whether or not to update statistics
Some say not to
Some say that even though update stats is set to "Auto" that you still need to update them
Anyone have any thought on this one ?
Thanks
Jim
July 16, 2009 at 10:03 am
Maybe....
Now I've got that out of the way, some details. Auto-update ensures that the stats will get updated once more than 20% of the table has changed. That's a large number and, in some cases, it's too high and results in sub-optimal query plans.
If you do update stats in the maint plan, do it before the index rebuild. Update stats, as far as I know, does a sampled update. Rebuilding an index updates that index's statistics with fullscan. You don't want to do a sampled update right after doing an update will fullscan.
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
July 16, 2009 at 10:14 am
Hi Gail
so your thoughts are:
dbcc ckeck
full backup
update stats
index rebuild
do stats and index rebuild have to be done every night in your opinion ?
Thanks
Jim
July 16, 2009 at 11:16 am
Personally I wouldn't use the maint plan rebuild unless it's a small database. Get one of the index rebuild scripts that only rebuilds if the fragmentation's excessive. There's one on Michelle's site - http://sqlfool.com
As for the stats, depends. If you're not seeing any stats-related performance problems, maybe you can rely on auto-update.
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
July 16, 2009 at 11:42 am
Hi Gail
unfortunately i work on an AFB and I am blocked from that site
makes it difficult to get info i need to do my job sometimes
is there any particular reason you do not use MP index rebuild except on small databases ?
Thanks
Jim
July 16, 2009 at 11:55 am
Because they rebuild all indexes every time they're run regardless of whether or not the index needs rebuilding. It's a waste of time and resources to rebuild an index that's only 2% fragmented.
Ask your internet security guys or your boss if they can get you access to that site. It's a very good SQL blog.
There are similar scripts in the script library 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
July 16, 2009 at 12:01 pm
If you can't reach the other site, maybe you can reach this one at Tara Kizer's blog:
Defragmenting/Rebuilding Indexes in SQL Server 2005
July 16, 2009 at 12:22 pm
Thanks MVJ
Got there !
I will take a look at it
Jim
July 16, 2009 at 1:08 pm
Hi Gail
we are talking USAF here 🙂
Government !
They are not interested in getting the job done
Thanks
Jim
July 16, 2009 at 2:18 pm
JC (7/16/2009)
we are talking USAF here 🙂Government !
Ok, you didn't say so before.
Something I used to use to get past the bank's security (which may or may not work here) was to use the google search caches to read pages that were otherwise restricted. Of course, that requires access to google, which I don't know if you have.
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
July 16, 2009 at 2:28 pm
Google I have 🙂
July 16, 2009 at 3:04 pm
Google for 'index rebuild site:sqlfool.com'
When the results page comes up, click on the 'cached' link. Voila.
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
July 16, 2009 at 3:36 pm
Hi Gail
no can do
same issue
thanks anyway
Jim
December 16, 2009 at 3:44 pm
I've read in various places that rebuilding indexes will update your statistics. This makes sense all of the data gets 'touched' in the rebuild.
However, I can't find any Microsoft site that confirms this. I'm only finding it on various SQL Sites or Blogs.
Can anyone confirm this is true?
Are all statistics updated in this case?
It seems redundant to update stats then rebuild indexes.
Is this the same in SQL 2005 and SQL 2008?
December 17, 2009 at 12:41 am
BobMcC (12/16/2009)
I've read in various places that rebuilding indexes will update your statistics. This makes sense all of the data gets 'touched' in the rebuild.
It does. Rebuilding an index updates that index's stats with full scan. Reorganising an index does not.
Are all statistics updated in this case?
Nope. Just the stats associated with the indexes that have being rebuild. Column stats (stats not associated with any index) won't be updated. Hence, if using a maint plan, an option is the rebuild indexes task followed by the update stats, with the options on that set to columm stats only, full scan.
There's a powerpoint presentation on my blog about this. http://sqlinthewild.co.za/resources/ under the PASS Community Summit 2009 heading.
p.s. Please in future start a new thread for a new question, rather than resurrecting an old, semi-relevant thread. Thanks.
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply