SQL Server Performance Forum – Threads Archive
Reorganize DB with EM’s Maintenance Plan
Dear all, I’m going to reorganize a DB on MSSQL Server 7 using EM’s Maintenance Plan. Is there any method I can estimate the time needed to complete the task? My DB size is about 10g and is running on a Dual PIII server with 1024MB Ram and NT4 as the OS. Is anyone can help? Thanks.We have a 8GB database which takes nearly 3 hours to complete the job during midnight.
Make sure no other jobs overlap during this job to ensure the performance of this job. _________
Satya SKJ
There is no way you can determinr the time for a mainenance activity. It depends on lot of factors like disk I/o, available CPU, memory, database size etc. The only way is to run it once and then the time would essentially be the same for any repeatitions given the conditions are the same. Also as satya said, try running the job when there is least load on the system to make this fast. Gaurav
We have Server with 2 GB Ram with NT 4 and Sql 7, we have
database size of 40gb to reindex it is taking 2 hrs 30 min. I feel to know the time for reindexing it is better to do this activity in one
of the standby server u can analyse the timings. More over the timings are depends on the number of indexes which the database has,
as well the the size of the table and indexes and the growth of the database when
reindexing happens because indexes requires groom in the database.
Thanks
Rushendra
Since this is the first time you are performing this on your server, the DB might take longer to perform the DBCC Checks, any backups, updating statistics (if they are not auto updated) and Rebuilld Indexes. I think subsequent frequently scheduled maintenance plans would take less time. With almost identical hardware as Rushi and a 25GB Database I once waited a whole 2 hours for the plan to finish. NHO
Why oh why are you doing this? Becaues EM makes it easy? Joe E O
Another plan can be to run the reindex manually based on the priority and fragmentation level of table and depending on the time window that you are left after a table is over, continue to reindex the next table. Gaurav
Leaving ‘total’ time estimates for the maintenance plan alone would actually do you some good. As Gaurav has suggested, fragmenting your DB reoganization into smaller and priority based manageable tasks would be one of the best ways of ensuring that you are able to beat the clock especially with highly used databases. Joseph: Yes EM makes it easy for DBAs to set up plans which can run forever. Some people might prefer to use the stored procedures provided for the purpose of creating maintenance jobs. Others (like me who don’t like waiting for long periods) might not use the DMP Wizard and opt to do the reindexing and other consistency checks manually and sometimes even on a table by table basis. It can be a lot of work but I suppose one is allowed to choose their own poison even when the obvious could be definitely lifesaving. NHO
The comment I made was a rhetorical regarding the need to defragment. In my current position the first week I started the performance was awful everyday until early afternoon (We are talking 8 CPUs all pegged at 100 % everyday). They previous dba(s) were rebuilding all indeces every night. I found that once we got all the page-splits out of the way the performance improved. Defragging or in this case rebuilding was just not needed , as I believe it is not needed in most cases. I have mentioned a technote from MS once before "MS SQL Server 2000 Index Defragmention Best Practices". The doc has a declaimer in the first paragraph: Note Defragmentation does not yield performance gains in every case. Every scenario is different. Similarly, determining when you should run the defragmentation statements requires analysis. So DEFRAGGING != "GO FAST" and in some cases makes things worse. It is something that needs to be analyzed. I never hear or read anyone mention any performance metrics when discussing defragmenting or rebuilding indeces. When we tune SPs – we see the query plan before and after, we measure the amount of IO performed – that is how we can tell if our efforts are improving performance. I am not seeing any analysis when it comes to defragging… My two cents… Joe E O
Joe, I think you need to review the fillfactor of the indexes. Because if you are having too many page splits, then you probably have high fillfactor while rebuilding indexes. Analyze your indexes first and then according to the amount and kind of updates / inserts on the system, decide on the fillfactor. Defragmentation is definatly required for a healthy database. There is so much text available on this. Seehttp://www.sql-server-performance.com/rebuilding_indexes.asp for more details. Gaurav
Defragmentation is required for a healthy database? SO if you do it every nighty your database will be supper fast and healthy? Ok, prove it. Show me a reproduceable case. Show me the metric that was bad before deraggind an good after defragging. I could see rebuilding with a certain fill factor if your original value was really way off but rebuilding on a regular basis? Not unless I was running some sort of DSS app that was doing lots of index scans. Joe E O
Hi Joseph, > So DEFRAGGING != "GO FAST" and in some cases makes things worse I agree 100% with your comments about metrics. When it comes to sql performance, IMO if you cant quantify it, you probably shouldnt be doing it on a production server. But Im unconvinced about your comment I quoted above. Can you suggest an example where defragging would be a *bad* idea, in that it would predictably degrade performance (ignoring the cost associated with the actual defrag process)? Regards Chappy
It was never mentioned in my post that you defragment it every day. It was just a clean statement that defragmenting the database on regular basis (note: regular does not mean daily) gives great performance. Also I don’t think when we talk about production systems, we have a dialy window of database maintenance left after the required tasks like backups are done. And the idea of having appropiate fill factor cam from the statement of large no. of page splits. Hope this clears the misunderstanding. Gaurav
]]>