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-play-with.aspx
- 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
Anonymous comments are disabled
About Paul Randal - MSFT
Paul started in the industry in 1994 working for DEC on the VMS file system and check/repair tools.
In 1999 he moved to Microsoft to work on SQL Server, specifically on DBCC. For SQL Server 2000, he concentrated on index fragmentation (writing DBCC INDEXDEFRAG and DBCC SHOWCONTIG) plus various algorithms in DBCC CHECKDB. During SQL Server 2005 development Paul was the lead developer/manager of one the core dev teams in the Storage Engine, responsible for data access and storage (DBCC, allocation, indexes & heaps, pages/records, text/LOB storage, snapshot isolation, etc). He also spent several years rewriting DBCC CHECKDB and repair. For SQL Server 2008, Paul managed the Program Management team for the core Storage Engine to become more focused on customer/partner engagement and feature set definition.
In 2007, after 8.5 years on the SQL Server team, Paul left Microsoft to join his wife, Kimberly Tripp, running SQLskills.com and pursuing his passion for presenting and consulting.
Paul regularly presents at conferences and user groups around the world on high-availability, disaster recovery and Storage Engine internals. His popular blog is at http://www.sqlskills.com/blogs/paul/.
