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 |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2008-09-29 : 07:45:38
|
| Hi,My DB is growing large in size, and to be honest I haven't been running any nightly jobs. I was having some unknown errors previously in my wizard generated maintenance plans, so I deleted them.My database has tables that grow daily, the largest with 155 million records and adding 300k per day. The next biggest just 15 million.These tables seem to be slowing down. I realize I can look at deleting stale data, which I plan to do, but wanted to nail nightly maintenance first.I ran Tara's ISP_ALTER_INDEX as found below [Thanks Tara! :)]Since i hadn't run inwhile it took 8 hours to complete, at 25% min defrag. I've since run it a few more times and got it to 10%.Should I run this at 1% for best perfomance ? 0% ? Should I change the SAMPLED to an option that provides better defragmentation, perhaps DETAILED ? (Will this be ok ? Should I test to find out ? )I have 4-5 hours in the night where I'd like to do what I can do make the DB run faster in the day. I can slow the DB during these hours, but can't take it offline.What else can I do to improve ? How often should I be doing it ? Can I run it too much ? What processes should I avoid running at the same time, or should I avoid any specific orders? For example, run ISP_UPDATE_STATISTICS *AFTER* the index defrag ? Any general guidance much appreciated ! I'm trying to avoid using the sql wizard here, as I think most of you here have said to avoid.Thanks very much!Mike123 |
|
|
|
|
|
general database maintenace questions