March 18, 2011 at 7:04 am
Is this the correct order when I rebuild/reorg indexes?
1. Database Consistency Check
2. Rebuild or Reorg Indexes
3. Update Stats
Thanks Fellow DBAs.
Patti
March 18, 2011 at 7:23 am
The order should be :-
1. checking database integrity
2. reorganizing indexes
3. updating stats
But if u r doing rebuild indexes :-
1. checking database integrity.
2. rebuild indexes.
Rebuild indexes updates the stats automatically.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
March 18, 2011 at 7:26 am
Thank you very much! 🙂
March 18, 2011 at 8:32 am
Rebuilding indexes only updates stats associated with those indexes. There are still column stats that may need to be updated.
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
March 18, 2011 at 11:11 am
Gail thank you again for you help!
March 18, 2011 at 3:13 pm
Depending on the system, you may want to take the consistency checks offline, perform them on a restored copy of the database or something. I'd move that away from any dependency on the index and statistics maintenance. Coupling with with the FULL backups makes sense though.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 18, 2011 at 3:18 pm
Grant Fritchey (3/18/2011)
...you may want to take the consistency checks offline, perform them on a restored copy of the database or something.
I have to agree; also helps minimize the maintenance window of a production database.
But the big reason is it kills two birds with one stone: integrity check and testing the restore regularly. Course, don't ONLY use this as your restore test since tlog's or differential backups are not tested. However, it is nice to have a more-than-quarterly test that the backups are basically working (or whatever timing your policy dictates).
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 18, 2011 at 3:25 pm
The issue with updating statistics after a rebuild is that it will update statistics that were just updated by the rebuild index task.
Instead of using the update statistics task in a maintenance plan, or using UPDATE STATISTICS over all the tables - you can use sp_updatestats which will identify statistics that need to be updated and only update those statistics.
Just be aware that using this procedure with the default parameters will only update the statistics with a sample. You have the option of using the RESAMPLE parameter, which tells the procedure to use the last sampling rate.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 18, 2011 at 3:26 pm
[Jim].[dba].[Murphy] (3/18/2011)
Grant Fritchey (3/18/2011)
...you may want to take the consistency checks offline, perform them on a restored copy of the database or something.I have to agree; also helps minimize the maintenance window of a production database.
But the big reason is it kills two birds with one stone: integrity check and testing the restore regularly. Course, don't ONLY use this as your restore test since tlog's or differential backups are not tested. However, it is nice to have a more-than-quarterly test that the backups are basically working (or whatever timing your policy dictates).
Jim
Dude, you're reading my mind. Now stay out. It's already crowdede enough in here.
But you're right. Taking the DBCC offline through a restore kills three birds with one stone ('cause it also reduces the overhead on the production system).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 18, 2011 at 3:31 pm
Great minds think alike.... and so do ours.
Oh yea? How about four birds: Restore functions as a poor-mans 'reporting' Day-Old.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 18, 2011 at 3:46 pm
Thanks Guys. These are all good ideas if you have another server to restore to.
March 18, 2011 at 3:57 pm
Even if you restore to the same server, you will get some benefit; in fact, most listed here. Course, don't restore over the same DB and ensure you have the disk space. Also, having a copy of the database in the same instance of SQL Server could be a problem too. Mainly in the buffer cache being used by the copy and not the main database, some potential disk IO issues, etc. However, it also offloads locks (potentially fewer shared locks on the OLTP db since reports would be pointing to the copy). Depending on how utilized your hardware is, you would still benefit from the restore and DBCC. But do some testing and proceed slowly if using the other db for a reporting day-old copy.
It is also a nice way to structure things so it makes it more natural to pitch a new box to your boss. Once everyone mentally segregates the data between OLTP and a read-only day-old reporting copy, then the next step is to get it its own server. Step one is getting everyone to think of the data differently. This may also be over kill or what you are doing and not beneficial, but it can give you a direction to head when the existing hardware utilization starts causing production problems and queries/indexes have already been tuned, etc.
But ya, you do get the most benefit when you have more hardware lying around.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 18, 2011 at 8:12 pm
All true but, the load placed on the TEMPDB by the consistency operations won't go away and could lead to to contention. If I was going to bother with offloading it, I'd get it to another server, if possible.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 20, 2011 at 8:41 pm
Oh, right. Good point.
Ya, different hardware would definitely be best.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 20, 2011 at 10:08 pm
in addition to above post just one more thing,
before considering the rebuild operation, Do consider the page count as well and then decide is it worth of rebuilding or not
----------
Ashish
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply