First published on MSDN on Apr 30, 2007
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
-
Even a VVVVLDB can be checked for corruptions
-
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-p...
-
Understand your SLAs
-
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
-
Consider turning on Instant Initialization to speed up file growth and restores