DBA 101: Best Practices All DBAs Should Follow

DBA 101_Best Practices for All DBAs to Follow--Auckland.pdf

Check & Verify
• Installing & Upgrading SQL Server
• General Server Configuration
• Memory Configuration
• User Data and Log File Management
• Tempdb Management
• Database Configuration Settings
• Configuring Jobs—General
• Create Index Rebuilding/Reorganize Job
• Create Data Corruption Detection Job
• Set Up Alerts for Critical Errors
• Security Basics
• Implement a Backup/Restore Strategy
• Create a Disaster Recovery Plan
• Test Everything

Tempdb Management

• Pre-size files so autogrowth doesn’t have to happen often
(8MB is default).
• Set autogrowth to avoid many growth spurts, use a fixed
amount that minimizes autogrowth use. (10% is default).
• If very active, locate it on its own volume.
• If very active, consider dividing the tempdb into multiple
physical files so that the number of files is about 50% to 100%
of the number of CPU cores your server has. Each physical file
must be the same size.

Database Configuration Settings
• Auto Create Statistics: On
• Auto Update Statistics: On
• Auto Shrink: Off
• Page Verify: Use Checksum (2005/2008), don’t turn off
• Autogrowth: Leave on. Use mainly for catching mistakes. File
growth should be managed manually. Use fixed amount that
minimizes autogrowth occurrences.
• Production databases should be set to FULL RECOVERY so
transaction log backups can be made.

Create Index Rebuilding/Reorganize Job

• Indexes need to be rebuilt or reorganized regularly to minimize fragmentation and reduce wasted space.

• Consider rebuilding an index if it is heavily fragmented (>30%). In Enterprise Edition, can perform online. If Standard Edition, consider it an off-line job. This automatically updates statistics, do you don’t need to do this again.

• Consider reorganizing an index if it is not heavily fragmented (>5% and <= 30%). This is an online operation and doesn’t use a lot of resources. You must update statistics afterwards, as this is not automatically done for you.

• Ideally, you should only rebuild or reorganize indexes that need it. Use sys.dm_db_index_physical_stats to  dentify what tables/indexes need to be rebuilt/reorganized.