SQL Server 2005 Best Practices
This article lists the best practices for SQL Server 2005
- Set recovery model as Simple.
- Allow tempdb files to grow automatically until the disk is full
- Set the file growth for tempdb in terms of 10%
- Enable Instant file initialization to speed up Auto growth, addition of new files
- Put tempdb database files in a separate drive different from those of user databases
- Create multiple tempdb database files and place them in different drives
- Physical Address Extension (PAE) enables x86 processors to access up to 128 GB of physical memory. X64 windows does not support this option
- The /3GB switch makes a full 3 GB of virtual address space available to applications and reduces the amount available to the system to 1 GB
- Using /PAE with /3GB limits the operating system (32 bit) with only 16GB of physical memory
- AWE is a set of extensions to the memory management functions of Windows that allow applications to address more memory than the 2-3 GB that is available through standard 32-bit addressing. AWE is not required and cannot be configured on 64-bit operating systems.
- Enable lock pages in memory in x64 if SQL Server memory is getting paged out
- Enable max and min server memory settings while running multiple instances of SQL Server
- Database backup files and database files must be on separate volumes
- Place the log files in uncompressed drives
- For high critical databases use Full Recovery model with regular transaction log backups
- Enable default trace to capture DDL changes
- Enable Instant File initialization for databases to speedup auto growth
- Enable Blocked process threshold to capture blocked processes
- Use the same collation for user and system databases
- Set the database option AUTO_Close to OFF for all databases
- Set the database option AUTO_Shrink to OFF for all databases
- Set the max degree of parallelism option to 8 or less
- Set the Auto_update_statistics option to ON for all databases
- Enable Latency warning for transactional replication topology
- Replicate stored procedure execution when making batch updates to published tables.
- Set XACT_Abort ON while replicating stored procedures
- Run snapshot agent only during off-peak hours
- Place the snapshot folder on a drive local to the Distributor that is not used to store database or log files.
- Consider specifying a recovery model of simple or bulk-logged while applying snapshot to a subscriber.
- Rebuild the indexes if the fragmentation is 30% or above else reorganize the indexes
- Perform them during off-peak hours since it requires a lot of resources and causes blocks
- Set the Fill factor as 100 for highly read only tables with less number of updates
- Set the Fill factor as 70 for highly write intensive tables
- Set the Fill factor as 80 to 90 for highly read and write intensive objects
- Check the integrity of all the databases using DBCC Checkdb for every 14 days
- Set the AUTO_UPDATE_STATISTICS option to ON to automatically update the statistics
- Set the AUTO_UPDATE_STATISTICS_ASYNC to ON to use asynchronous statistic updating
- Set the STATISTICS_NORECOMPUTE to OFF in the Alter index statement to enable auto update statistics
- CREATE INDEX and ALTER INDEX REBUILD are always minimally logged in Bulk_logged and Simple recovery model, but the rest are always fully logged so its advisable to change the recovery model to Bulk logged or Simple during these operations