Quick list of VLDB maintenance best practices

One evening last week I sat down with Kimberly 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


• Make sure all indexes are actually needed




• See http://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/20/how-can-you-tell-if-an-index-is-being-used.aspx


• Run adequate consistency checks


• Even a VVVVLDB can be checked for corruptions




• See http://blogs.msdn.com/sqlserverstorageengine/archive/2006/10/20/consistency-checking-options-for-a-vldb.aspx


• Have a disaster recovery plan


• Test it before you have to use it


• Make sure the most junior DBA can follow it


• See http://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/17/example-corrupt-database-to-play-with.aspx


• Understand your SLAs




• See http://blogs.msdn.com/sqlserverstorageengine/archive/tags/Service+Level+Agreements/default.aspx


• Have a backup strategy that allows you meet your SLAs




• E.g. Weekly full backups with no HA solution only won’t allow you to meet zero data-loss SLAs


• Make sure tempdb is optimized for the workload


• Make sure you’re managing the transaction log correctly




• E.g. being in full recovery mode with no log backups means you’ll eventually run out of space on the log drive


• Don’t have multiple log files – no need


• Don’t let auto-grow go nuts – causes VLF fragmentation which leads to horrible perf


• Don’t run database shrink




• See http://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx


• Don’t rely on auto-grow


• Pro-actively manage file growth but have auto-grow on as a safeguard


• Consider turning on Instant Initialization to speed up file growth and restores




• See http://www.sqlskills.com/blogs/kimberly/2007/03/04/InstantInitializationWhatWhyAndHow.aspx