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 https://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 https://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 https://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
- See https://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
Comments
Anonymous
May 03, 2007
Excellent list!Anonymous
May 03, 2007
This Blog ROCKS ! Any caveats on using DBCC Shrinkfile for tran log files (in Simple recovery mode) ?Anonymous
May 06, 2007
Thanks! No issues wuth using shrinkfile on the log - let me know if you're having problems.Anonymous
May 06, 2007
The comment has been removedAnonymous
May 07, 2007
Correct - stats are not recomputed as part of a REORGANIZE (or DBCC INDEXDEFRAG) operation. This is because the command does not see an overall snapshot of the index - only the few pages its working on at a time. This means that as soon as its through with some pages, the value distributions coul change dramatically. Interesting - I was sure I'd put it into BOL for ALTER INDEX. I'll make sure its there in future. ThanksAnonymous
May 07, 2007
The comment has been removedAnonymous
May 08, 2007
Bill - please send me an email with these questions in. ThanksAnonymous
May 08, 2007
Thanks..I sent the mail to you this morning.Anonymous
August 09, 2007
转载、解释一下来自SQL Server Storage Team的VLDB维护建议清单。 虽然主题是维护，但相信理解这些对于从事数据库开发的人员来说也有一定价值。Anonymous
January 08, 2015
Nice List! I also resort to this 10 tips that have proven to be very useful sqlturbo.com/top-10-must-do-items-for-your-sql-server-very-large-database