SQL Server Setup and Tuning for IT Professionals

SQL Server is the database most Windows shops already run. These are the setup decisions and tuning steps that matter in practice — not the textbook version, the version where you inherit a server that's been running since 2019 and nobody optimised it.

Installation Checklist

When installing SQL Server, lock down these settings from the start:

• TempDB files — create one file per CPU core (up to 8), all same size, pre-grown to avoid autogrowth during peak load
• Max server memory — set this. Default is unlimited; SQL Server will take all available RAM and starve the OS. Leave 4–8GB for the system
• Max degree of parallelism (MAXDOP) — set to number of physical cores per NUMA node, max 8. Default parallelism across all cores causes contention
• Cost threshold for parallelism — raise from 5 to 50. The default sends far too many simple queries to the parallel plan pipeline
• Instant file initialisation — grant the SQL Server service account Perform Volume Maintenance Tasks. Cuts data file growth from minutes to seconds

-- Set max memory (example: 24GB server, leave 4GB for OS)
EXEC sp_configure 'max server memory', 20480;
RECONFIGURE;

-- Set MAXDOP (adjust to your core count)
EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE;

-- Raise parallelism cost threshold
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

Backup Strategy

The minimum viable backup strategy for SQL Server:

• Full backup — weekly, Sunday night
• Differential backup — nightly, Mon–Sat
• Log backups — every 15 minutes for databases in Full recovery model

Store backups on a separate drive from the database files. If your backup and database live on the same disk and it dies, both go together.

Ola Hallengren's maintenance solution covers backups, integrity checks and index maintenance in a single free script: ola.hallengren.com. Run it. It saves hours of writing your own jobs.

Finding Performance Problems Fast

These two queries identify your worst-performing queries without a profiler:

-- Top 10 queries by total CPU time
SELECT TOP 10
  qs.total_worker_time/qs.execution_count AS avg_cpu,
  qs.execution_count,
  SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
    ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
      ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_cpu DESC;

-- Missing index recommendations
SELECT TOP 20
  migs.avg_user_impact,
  migs.user_seeks + migs.user_scans AS total_uses,
  mid.statement AS table_name,
  mid.equality_columns,
  mid.inequality_columns,
  mid.included_columns
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig ON migs.group_id = mig.index_group_id
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY migs.avg_user_impact DESC;

SQL Server in Docker (Dev Environment)

Stop installing SQL Server on dev machines. Run it in Docker:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=YourStr0ng!Pass' \
  -p 1433:1433 --name sqlserver --hostname sqlserver \
  -d mcr.microsoft.com/mssql/server:2022-latest

Connect with Azure Data Studio (free, cross-platform) or SSMS on Windows. A clean SQL Server 2022 instance in under a minute.

---

*As an Amazon Associate, IT Lad earns from qualifying purchases made through links on this page. [Read the full affiliate disclosure](/affiliate-disclosure).*