August 4, 2011 at 2:50 pm
Hi All,
Bit of a sql noob, and would appreciate some help.
My current maintenance task is taking too long to perform and I am wondering if it is ok to split it.
Here is the current task:
1) Check Database Integrity
2) Reorganise index
3) Update Statistics.
What I am wanting to do is to split the task up so do the Check integrity on Weds and the Reorg & update Stats on a Sat.
My question is, is this good/not good, does there need to be an integrity check before a reorg?
Regards,
Dan
August 4, 2011 at 3:55 pm
you can do it that way. no problem
But is better know
Do you really need to do Integrity check every week , what if you can do for a month?---Depends how transactional is your database
It is better to do re-org or re-Index based on level of fragmentation instead doing reindexing even if they are not fragmented. --->This step would gain you more time.
Update stats should'nt take much long compare to Integrity and Re-indexing.
August 4, 2011 at 4:38 pm
Im not exactly sure how to set the Reorg index task to check the fragmentation first.
Its SQL 2005, just did the drag and drop tasks (and set the target database).
August 4, 2011 at 4:58 pm
you don't need to run an integrity check before a reindex, so yes run on different nights.
But you should run integrity checks on a regular basis, at least weekly. run them frequently enough to be able to restore a clean backup before the corruption. so if you have 3 days worth of backups available, run the integrity check every 3 days.
If you are running the maintenance plan rebuild index task, you don't need to run the update stats as well.
If you are running the reorganise task, you do need to run update stats.
there are scripts out there which only rebuild or reorganise indexes which are actually fragmented and need rebuilding. If you replace the maintenance plan reindex task with one of those scripts it will be faster and save resources. there is a good script for this in BOL under 'Sys.dm_db_index_physical_stats ' entry.
---------------------------------------------------------------------
August 5, 2011 at 1:21 am
August 5, 2011 at 3:28 am
yes I thought about that but as the OP admits to being 'a bit of a noob' i thought it might be better to start with a simple solution and have a few less bells and whistles.
all good reading material though.
---------------------------------------------------------------------
August 5, 2011 at 3:31 am
Well, I find Ola's script very easy to use.
However, if he gets stuck, we can alway help, no? 😉
-- Gianluca Sartori
August 5, 2011 at 3:34 am
.....one thing I would do though whatever script you use is to exclude small tables from the reindex as there is no benefit from defragmenting them, so anything with less than about 500 pages.
so add 'and page_count > 500'
to the where clause when reading sys.dm_db_index_physical_stats
---------------------------------------------------------------------
August 5, 2011 at 3:38 am
Gianluca Sartori (8/5/2011)
Well, I find Ola's script very easy to use.However, if he gets stuck, we can alway help, no? 😉
thats why we're here! 🙂
---------------------------------------------------------------------
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply