Lesson 1: Server Configuration Philosophy (Stable Defaults + Controlled Changes)

Section 7 — Lesson 1: Server Configuration Philosophy (Stable Defaults + Controlled Changes)

Welcome to Section 7. This section is where you start thinking like a production DBA.
In the real world, most outages are not caused by “one bad query.” They are caused by
unstable configuration, undocumented changes, poor defaults left untouched for years,
or well-meaning “tuning” that was never validated.

Your goal as a DBA is not to make the server “fast today.” Your goal is to make the server
predictable, supportable, and safe—so performance stays consistent and incidents are easier to diagnose.

Lesson description: You will learn the DBA configuration mindset used in production:
baseline-first, change control, one change at a time, evidence-based validation, and disciplined documentation.
You will build a repeatable workflow to capture a configuration snapshot (before/after),
define what a “safe baseline” means, and create rollback-friendly change notes.

1) The DBA Configuration Mindset (What Professionals Actually Do)

New DBAs often think configuration is a set of “best practice numbers” you copy from the internet.
That approach causes outages.

Production DBAs use a different mindset:

1. Baseline: Capture what you have today (settings + workload signals).
2. Decide: Make a small, justified change with a clear objective.
3. Control: Apply changes through change control (window, approvals, rollback plan).
4. Validate: Prove improvement (or prove no harm) using before/after evidence.
5. Document: Record what changed, why, when, and how to reverse it.

Rule of thumb: If you cannot explain why a setting should change and how you will validate it,
you are not ready to change it.

2) “Stable Defaults” vs “Controlled Changes” (What Those Words Mean)

2.1 Stable defaults

Stable defaults mean your instance has a known, consistent configuration baseline that avoids common outages.
You are not constantly experimenting. You are operating a stable system.

2.2 Controlled changes

Controlled changes mean:

• you change one thing at a time whenever possible
• you change it during a reasonable window (or with clear risk acceptance)
• you can revert it quickly if behavior worsens
• you capture evidence before and after

DBA truth: “Best practices” without validation become “best guesses.”

3) The “Outage Settings” DBAs Avoid Touching Randomly

Some settings have high blast radius. You do not change them casually:

• Memory settings (can starve Windows or cause instability)
• Parallelism settings (can dramatically change workload behavior)
• tempdb configuration (can impact the whole server under concurrency)
• Autogrowth strategy (can create performance cliffs and outages)
• Database options like AUTO_CLOSE/AUTO_SHRINK (quietly destroy stability)

In this section you will learn how to set these correctly. In this lesson, you learn how to manage changes safely.

4) Your Baseline Folder and Documentation Standard (Do This First)

Create a consistent place to save “before/after” configuration snapshots. In your lab, use:

C:\DBA\
  Baselines\
    InstanceConfig\
  Logs\
    Changes\
  Notes\

Naming standard (simple and professional):

• baseline_instance_config_before_YYYYMMDD_HHMM.txt
• baseline_instance_config_after_YYYYMMDD_HHMM.txt
• change_note_YYYYMMDD_HHMM.txt

5) Baseline Capture Scripts (Safe, Read-Only)

Run these scripts to capture a baseline. These are safe read-only queries.
In production, DBAs run baseline capture before changing anything.

5.1 Instance identity (server name, version, edition)

SELECT
    GETDATE() AS captured_at,
    @@SERVERNAME AS server_name,
    SERVERPROPERTY('MachineName') AS machine_name,
    SERVERPROPERTY('InstanceName') AS instance_name,
    SERVERPROPERTY('Edition') AS edition,
    SERVERPROPERTY('ProductVersion') AS product_version,
    SERVERPROPERTY('ProductLevel') AS product_level;

5.2 Current server configuration values (the most important snapshot)

This shows configured value and value-in-use, which matters when a restart is required.

SELECT
    name,
    description,
    value AS configured_value,
    value_in_use,
    minimum,
    maximum,
    is_dynamic,
    is_advanced
FROM sys.configurations
ORDER BY name;

5.3 Key database options that cause instability (scan quickly)

SELECT
    name,
    state_desc,
    recovery_model_desc,
    compatibility_level,
    is_auto_close_on,
    is_auto_shrink_on,
    is_read_committed_snapshot_on,
    is_parameterization_forced
FROM sys.databases
ORDER BY name;

What you are looking for (for later lessons):
AUTO_CLOSE and AUTO_SHRINK being ON are usually red flags in real environments.

5.4 File growth settings overview (high-level risk scan)

SELECT
    DB_NAME(mf.database_id) AS database_name,
    mf.type_desc,
    mf.name AS logical_file_name,
    mf.size * 8 / 1024 AS size_mb,
    mf.growth,
    mf.is_percent_growth,
    mf.physical_name
FROM sys.master_files AS mf
ORDER BY database_name, mf.type_desc, mf.file_id;

6) The DBA Change Template (Write This Every Time)

In production, a change note is not optional. It is what protects you, the business, and the system.
Use this template in a text file:

CHANGE NOTE (DBA)

Date/Time:
Environment: (Dev/Test/Stage/Prod)
Server/Instance:
Requested by:
Approved by:

Objective (what problem are we solving?):
Risk (what could break?):
Rollback plan (how do we undo it quickly?):

Before evidence captured (file names):
After evidence captured (file names):

Change steps executed (exact commands/settings):
Validation performed (what proved success?):
Notes (anything unusual observed):

This habit is what separates a “button-clicker” from a job-ready DBA.

7) What “Validation” Looks Like (Not Guessing)

Validation means you compare before/after evidence. Common DBA validation items include:

• Did the setting actually take effect? (configured vs in_use)
• Did waits or contention improve?
• Did query duration or throughput improve?
• Did CPU, memory pressure, or I/O stalls worsen?
• Did tempdb activity change?

In later lessons you will validate specific settings (MAXDOP, memory, tempdb, growth strategy).
For now, you must learn the process and discipline.

8) The “One Change at a Time” Rule (How DBAs Avoid Confusing Results)

If you change MAXDOP, memory, tempdb files, and autogrowth settings in one day,
and performance changes, you will not know why.

Job-ready DBAs avoid this by:

• changing one major category at a time
• recording the exact command executed
• waiting long enough to observe workload effects (where possible)
• keeping a rollback option ready

Practice (Required)

1. Create the folders:
   C:\DBA\Baselines\InstanceConfig\ and C:\DBA\Logs\Changes\
2. Run the baseline scripts in section 5 and save outputs as:
   • C:\DBA\Baselines\InstanceConfig\baseline_instance_config_before_YYYYMMDD_HHMM.txt
   • C:\DBA\Baselines\InstanceConfig\baseline_db_options_before_YYYYMMDD_HHMM.txt
   • C:\DBA\Baselines\InstanceConfig\baseline_file_growth_before_YYYYMMDD_HHMM.txt
3. Create a change note file using the template in section 6:
   C:\DBA\Logs\Changes\change_note_YYYYMMDD_HHMM.txt
   (You are not executing a change yet—this is practice for professional workflow.)
4. In your DBA notebook, answer:
   • Why is “baseline first” a DBA rule?
   • What does “controlled change” mean in production?
   • What are three configuration categories that can cause outages if changed carelessly?

Lesson Summary

• Professional DBAs manage configuration with baselines, controlled change, validation, and documentation.
• DMVs and sys views provide evidence; your job is to capture snapshots before and after.
• One change at a time prevents confusion and reduces incident risk.

Next Lesson: MAXDOP and Cost Threshold for Parallelism (How to Set Correctly)

Next, you will learn one of the most impactful SQL Server configuration areas:
parallelism. You will understand what MAXDOP controls, how Cost Threshold works,
why bad settings cause CPU storms or slow queries, and how DBAs set these safely with validation.