Operating System
Page File
8GB – 12GB File Size
TCP Global Settings
Chimney Offload State: Disabled
Receive Segment Coalescing State: Disabled
Power Level Settings
High Performance
SQL Server Settings
Memory
Reserve 4GB from the first 16GB of RAM and then 1GB from each additional 8GB of RAM for the operating system and other applications.
Advanced Settings
Cost Threshold for Parallelism
This value should be changed from 0 to 50
Max Degree of Parallelism (MaxDOP)
The MaxDOP setting relates to the SQL Server’s CPUs. Please see below for the different scenarios.
Server with single Numa Node | Less than 8 Logical Processors | Keep Maxdop at or below # of logical processors |
Server with single Numa Node | Greater than 8 Logical Processors | Keep Maxdop at 8 |
Server with multiple Numa Nodes | Less than 8 logical processors per Numa Node | Keep Maxdop at or below # of logical processors per Numa node |
Server with multiple Numa Nodes | Greater than 8 logical processors per Numa node | Keep Maxdop at 8 |
Disk Configuration
TeleTracking has devised three different level of Tiers depending on the amount of TeleTracking applications being deployed to a SQL Server. It is highly recommended to have the Test applications deployed to a separate SQL Environment.
The tiers have different levels of segregation to prevent disk contention. Each bullet indicates a dedicated LUN or hard disk.
All drives should use GPT (GUID Partition Table) Partition Structure
Any log or data files should have an Allocation Unit Size of 64K; This can be accomplished at the time of formatting the drive - SQL grabs disk in 8 - 8K extents, so by setting this to 64K you will allow the OS to more efficiently grab data on SQL's behalf.
Tier 1 Standalone Applications (Standalone: XT, RTLS, CRS, or CWS)
Data Drive
Log
TempDB, TempDB T-Log, and System DBs
*Backups
Tier 2 Standalone Applications (Standalone: XT, RTLS, CRS, or CWS)
Data
Log
TempDB
TempDB Transaction Logs
System DBs
*Backups
Tier 3 (Standalone Applications)
Data 1 (XT, CRS, CWS)
Data 2 (RTLS, TempTracking)
Transction Logs
TempDB
TempDB Transaction Logs
System DB
*Backups
Tier 4 (Standalone Applications)
Data 1 (XT, CRS, CRS)
Data 2 (XTArchive)
Data 3 (RLTS, TempTracking)
Data 4 (RTLSArchive)
Transction Logs
TempDB
TempDB Transaction Logs
System DB
*Backups
*Backups should reside on a separate drive for disaster recovery purposes if not stored at a network location
Database Growth Settings (TempDBs and User DBs)
Ensure file auto-growth / max size is managed appropriately – this will help to keep the files to a manageable size and reduce fragmentation.
Database less than 20 GB
Data files – 250 MB, unlimited
Log files – 100 MB, unlimited
Databases larger than 20 GB
Data files – 1 GB, unlimited
Log files – 100 MB, unlimited
TempDBs
The number of TempDB data files should equal the number of processors up until 8 cores. After 8 TempDB data files, the investment of return is reduced due to disk contention.
Maintenance Plans
A great solution to use is on SQL Server Backup, Integrity Check, Index and Statistics Maintenance It is very configurable and is always kept up to date. This solution includes:
Full Backups - nightly
Diff Backups - optional; only needed on very large database (1 TB or more) where the full backups take a very long time to complete and don't finish within the maintenance window; if needed, run this nightly and run the full backup weekly
Log Backups - Best practice to take these as often as possible; We recommend every 30 minutes if possible; this will help to keep the log file size manageable.
Database Integrity Checks - nightly if possible
Index Maintenance - Nightly if possible; But if it does not complete within the allotted maintenance window, then weekly should suffice
Additional jobs
Cycle error logs - use sp_cycle_errorlog – weekly
Recommended Values for Variables
Index Maintenance
Index Maintenance should be executed after the XTArchive process is completed to ensure databases have the latest statistics.
Set page size to >= 500; Have indexes reorg from 10 – 30 % fragmentation; Anything above 30% fragmentation – rebuild indexes; want to run this first to get a clean efficient backup
Statistics should be updated for Columns and should use a 100% sample size.
Database Integrity Checks – make sure the results get written out to a log directory somewhere so that they can be reviewed in the event of data corruption – run this immediately after index maintenance to ensure there are no corruption issues
Full backups – nightly if it fits into the maintenance window; if it doesn’t fit in the maintenance window, have it run weekly
Differential backups– only need to run this if you can’t run nightly fulls; otherwise this can be disabled
TLog backups – preferably every 15 – 30 minutes to keep the log file in check