One evening last week I sat down with
for 5 minutes to come up with a top-of-our heads list of VLDB maintenance concerns for a company migrating a multi-TB database to SQL Server 2005. This isn't in any way based on the VLDB survey I've been doing (see previous posts) but is a common-sense list of things that everyone should do. People really liked the list so I'm posting it here. Maybe we should turn this into a book???
Hope this helps.
Have page checksums turned on
Make sure auto-stats update is turned on
Pay attention to index fragmentation
Logical fragmentation only affects read-ahead performance
Only rebuild/defrag indexes that have this will help
Low page density affects IO throughput and memory usage
Low page density could be a sign of page-splits, so investigate the cause
If you defrag instead of rebuild, make sure you manually update stats
Be wary of doing large index maintenance jobs if you use log shipping or DBM
They contribute to large log backups
Index rebuilds are always full-logged when DBM is present