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:
-
- MAX DEGREE OF PARALLELISMÂ and MAXDOP
- COST THRESHOLD FOR PARALLELISM
- Soft-NUMA
- Memory
- /PAE, /AWE, /3GB (not needed for 32 bit machines)
- Lock Pages in Memory
- MAX/MIN SERVER MEMORY
- FileGroups and Partitioning:
-
- Multiple FG & Files
- No data in Primary FG (for online restore)
- Use Table Partitions
- Allocate large file size initially for data files (MDFs NDFs)
- Allocate calculated large size for transactional log file
- Instant File Initialization
- Indexes and related tasks:
-
- Covered Indexes
- Separate nCI and CI
- Online index rebuild
- Filtered Indexes
- Indexed Views (Automatic Query Substitution)
- Fill Factor and Pad Index
- Update Stats regularly
- Disks:
-
- Disk Partition Alignment
- Separate disks for os/binaries, sysDB, TempDB, MDF, LDF and BAK file locations
- RAIO 10
- Maintenance:
-
- Stripe Backup
- SET PAGE_VERIFY CHECKSUM
- BACKUP with CHECKSUM
- RESTORE VERIFYONLY WITH CHECKSUM
- DBCC CHECKDB Optimization
- Archiving
Hope this helps,
Advertisements
[…] newer versions of Sql Server, starting 2008, there are several ways to minimize this overlap or contention. Isolation Levels, Table Partitioning, Efficient code […]