Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DavidSh
Starting Member
4 Posts |
 Posted - 2007-02-07 : 06:07:30
|
| Hi, As a newbie SQL Server 2000 DBA I've inherited a production database with a database maintenance plan already in place. However, I've noticed that the optimization plan does not currently include 'Reorganize data and index pages' which I believe is affecting the performance of the database. I would like to add this into the plan, however when viewing the plan using EM there is a warning message advising that the existing maintenance plan should not be altered due to associated scheduled jobs. So I have two questions:1. Is it ok to alter the maintenance plan or should is it safer to remove and then recreate the plan with the additional option that I require?2. Are we likely to experience a rapid growth in the size of the transaction log if the plan reorganises indexes on the database?I want to tread carefully on this as it is the production database in question so any advice appreciated.Many thanksDavid. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-07 : 07:31:01
|
1. I would make a new plan, with JUST the reorganise job.Make sure that you do NOT select the "10% free space" option - which is the default. It will change all your index FillFactor to 90%  2. Yes!If your database is using Full Recovery Model make sure that TLog backups are frequent (I would need some persuading that intervals longer than 10 minutes are justifiable - "Are you prepared to lose an hours data when you could be backing up your TLogs every 10 minutes?"  You are then [in simplistic terms] only at risk of TLog expansion to whatever ReIndex can accomplish in 10 minutes [in practice it may be more than that if there are whole transactions that are longer than 10 minutes run time, but you will mitigate the problem, at least]My rule-of-thumb is that Log files (LDF) are often up to 120% of Data Files (MDF). If yours are lots smaller than this ratio then make sure you have enough room for them to reach that size when you run your first Reindex; then review based on what size they actually grow to.Kristen |
 |
|
|
DavidSh
Starting Member
4 Posts |
Posted - 2007-02-07 : 07:56:27
|
| Hi Kristen, |
|
|
|
DavidSh
Starting Member
4 Posts |
Posted - 2007-02-07 : 08:00:48
|
| Hi Kristen,Thank you for the reply. Can you just clarify a couple of points:1. What is the 'optimum' index FillFactor to use as part of the plan?2. If the database .mdf file is currently 22GB approx and the last trans log backup occurs with the .ldf file at 1.5GB approx does that mean that we need approx 27GB worth of space for potential log growth as the maintenance job runs?Many thanksDavid |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-07 : 09:39:24
|
| 1. Leave it alone. They are probably set up correctly on each table, and even if not "one size for all" is definitely not a good plan!In particular tables with an Identity column PK should be 100% and tables that have a random-insert-pattern PK should NOT be 100%2. Well that's my rule of thumb, but it may not be any use for the specifics of a first-time-reindex. With frequent (10 minutely or so) TLog backups you will have sensible control over TLog growth, but it would be best to assume you may need acres of disk space for this maneuver the first time you try it. if it uses a horrible amount of disk space you can use SHRINK to bring the files back to a reasonable size, but Disk Full would be best avoided.I think the best worse case scenario (if that's valid English!) is:There is NO free space in the MDF file. It is "nicely full".Reindex decides to rebuild your biggest tableThe whole table is moved to the end of the MDF file (in order to reorganise it)The original table is then released (which makes free space for any other table needing reorganisation)All this activity triggers some serious LDF activity too.so at this point if your "biggest table" is 99% of your database the MDF file will in effect have doubled. And there will have been some serious volume of stuff in the LDF file too.In reality you will have some slack in your MDF file, which will get used as working-space by the Reindex, and hopefully your biggest table is a lot less than 99% of the database size. But you will still need a fair bit of LDF space I expect (mitigated by frequent TLog backups)If you are worried about the impact of this, and have a PC with huge local disks, I would put MSDE on the PC and trial-run the job there first, and check the size of the files so you at least know what to expect on the production server.Kristen |
|
|
|
|
|
|
|
|
Database Maintenance Plan on Production Database