Scalability and Performance design techniques for high transactional volume, VLDBs

Update: March 26, 2017 – Added links to posts with details steps.

When designing VLDBs for large transactional volume, I keep these design techniques for better scalability and performance. Details on each of these points will come in future.

• Processor related:

1. 1. MAX DEGREE OF PARALLELISM  and MAXDOP
   2. COST THRESHOLD FOR PARALLELISM
   3. Soft-NUMA

• Memory
  1. /PAE, /AWE, /3GB (not needed for 32 bit machines)
  2. Lock Pages in Memory
  3. MAX/MIN SERVER MEMORY

• FileGroups and Partitioning:

1. 1. Multiple FG & Files
   2. No data in Primary FG (for online restore)
   3. Use Table Partitions
   4. Allocate large file size initially for data files (MDFs NDFs)
   5. Allocate calculated large size for transactional log file
   6. Instant File Initialization

• Indexes and related tasks:

1. 1. Covered Indexes
   2. Separate nCI and CI
   3. Online index rebuild
   4. Filtered Indexes
   5. Indexed Views (Automatic Query Substitution)
   6. Fill Factor and Pad Index
   7. Update Stats regularly

• Disks:

1. 1. Disk Partition Alignment
   2. Separate disks for os/binaries, sysDB, TempDB, MDF, LDF and BAK file locations
   3. RAIO 10

• Maintenance:

1. 1. Stripe Backup
   2. SET PAGE_VERIFY CHECKSUM
   3. BACKUP with CHECKSUM
   4. RESTORE VERIFYONLY WITH CHECKSUM
   5. DBCC CHECKDB Optimization
   6. Archiving

Hope this helps,

_SqlTimes