SQL Server 2005 Best Practices

This article lists the best practices for SQL Server 2005

Tempdb:

  1. Set recovery model as Simple.
  2. Allow tempdb files to grow automatically until the disk is full
  3. Set the file growth for tempdb in terms of 10%
  4. Enable Instant file initialization to speed up Auto growth, addition of new files
  5. Put tempdb database files in a separate drive different from those of user databases
  6. Create multiple tempdb database files and place them in different drives

Memory:

  1. Physical Address Extension (PAE) enables x86 processors to access up to 128 GB of physical memory. X64 windows does not support this option
  2. 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
  3. Using /PAE with /3GB limits the operating system (32 bit) with only 16GB of physical memory
  4. 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.
  5. Enable lock pages in memory in x64 if SQL Server memory is getting paged out
  6. Enable max and min server memory settings while running multiple instances of SQL Server

Database Engine:

  1. Database backup files and database files must be on separate volumes
  2. Place the log files in uncompressed drives
  3. For high critical databases use Full Recovery model with regular transaction log backups
  4. Enable default trace to capture DDL changes
  5. Enable Instant File initialization for databases to speedup auto growth
  6. Enable Blocked process threshold to capture blocked processes
  7. Use the same collation for user and system databases
  8. Set the database option AUTO_Close to OFF for all databases
  9. Set the database option AUTO_Shrink to OFF for all databases
  10. Set the max degree of parallelism option to 8 or less
  11. Set the Auto_update_statistics option to ON for all databases

Replication:

  1. Enable Latency warning for transactional replication topology
  2. Replicate stored procedure execution when making batch updates to published tables.
  3. Set XACT_Abort ON while replicating stored procedures
  4. Run snapshot agent only during off-peak hours
  5. Place the snapshot folder on a drive local to the Distributor that is not used to store database or log files.
  6. Consider specifying a recovery model of simple or bulk-logged while applying snapshot to a subscriber.

Optimizing Indexes:

  1. Rebuild the indexes if the fragmentation is 30% or above else reorganize the indexes
  2. Perform them during off-peak hours since it requires a lot of resources and causes blocks
  3. Set the Fill factor as 100 for highly read only tables with less number of updates
  4. Set the Fill factor as 70 for highly write intensive tables
  5. Set the Fill factor as 80 to 90 for highly read and write intensive objects
  6. Check the integrity of all the databases using DBCC Checkdb for every 14 days
  7. Set the AUTO_UPDATE_STATISTICS option to ON to automatically update the statistics
  8. Set the AUTO_UPDATE_STATISTICS_ASYNC to ON to use asynchronous statistic updating
  9. Set the STATISTICS_NORECOMPUTE to OFF in the Alter index statement to enable auto update statistics
  10. 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

Leave a Reply

Your email address will not be published. Required fields are marked *

*