SQL Server Configuration: “What NOT to DO”

 🚫 SQL Server Configuration: “What NOT to Touch”

A Practical DBA Checklist to Avoid Self-Inflicted Performance Problems

SQL Server exposes dozens of configuration options. Some are powerful. Some are dangerous.
And some are frequently misused, especially under performance pressure.

This article compiles a battle-tested “what NOT to touch” checklist for SQL Server DBAs and performance engineers—with reasons, real risks, and safe alternatives.

If you’ve ever seen performance “fixed” by clearing cache, restarting SQL Server, or disabling parallelism—you’ll recognize these immediately.

---

Why This Checklist Matters

Most SQL Server outages are not caused by hardware or bugs.
They are caused by well-intentioned but harmful configuration changes.

🔑 Golden rule:
If you don’t clearly understand what a setting fixes, don’t touch it.

---

❌ 1. Lightweight Pooling (Fiber Mode)

What people change

sp_configure 'lightweight pooling'

Why this is dangerous

• Switches SQL Server from threads to fibers
• Disables parallelism entirely
• Breaks modern CPU scalability
• Offers no benefit on modern hardware

Real-world impact

• Slower queries
• Poor CPU utilization
• Harder troubleshooting

✅ Correct approach

• Leave OFF (0)
• Fix CPU pressure via:
• Query tuning
• MAXDOP and CTP configuration

---

❌ 2. MAXDOP = 1 (Globally)

Why people do it

“To fix CXPACKET waits”

Why it’s wrong

• Eliminates parallel plans for all queries
• Hides real query and indexing problems
• Slows reporting and maintenance tasks

✅ Correct approach

• Set MAXDOP properly:
• Cores per NUMA node
• Maximum 8
• Control parallelism per query, not globally

---

❌ 3. Cost Threshold for Parallelism = 5

Why it’s a red flag

• Default value is far too low
• Causes even small OLTP queries to go parallel
• Increases CPU contention and CXPACKET waits

✅ Correct approach

• OLTP systems: 50–100
• Mixed workloads: 30–75
• Adjust gradually and observe

---

❌ 4. Clearing the Buffer Cache

DBCC DROPCLEANBUFFERS

Why this is extremely harmful

• Flushes clean pages from memory
• Forces massive physical I/O afterward
• Destroys performance baselines
• Does NOT fix performance

When it is acceptable

• Lab testing only
• Benchmark comparisons

🚫 Never run this in production.

✅ Correct approach

• Investigate:
• Execution plans
• Wait stats
• I/O latency
• Let SQL Server manage memory

---

❌ 5. Clearing the Procedure Cache

DBCC FREEPROCCACHE

Why it’s a red flag

• Forces recompilation of all queries
• CPU spikes
• Temporary “improvement” hides root cause

✅ Correct approach

• Fix:
• Parameter sniffing properly
• Bad indexes
• Use:
• Query Store plan forcing
• Targeted plan eviction only if necessary

---

❌ 6. Restarting SQL Server to “Fix” Performance

Why this works (temporarily)

• Clears buffer cache
• Clears plan cache

Why it’s dangerous

• Problem always comes back
• Zero root cause analysis
• Hides systemic issues

✅ Correct approach

• Capture baselines
• Fix queries, indexes, and configuration

---

❌ 7. Blanket Index Rebuild Jobs

Why DBAs do this

“Fragmentation is bad”

Why it’s harmful

• Massive log generation
• CPU spikes
• Cache churn
• Often zero performance benefit

✅ Correct approach

• Rebuild only when:
• Fragmentation > 30%
• Page count > 1000
• Reorganize between 10–30%
• Ignore small indexes

---

❌ 8. Shrinking Data or Log Files

DBCC SHRINKFILE

Why this is a classic DBA mistake

• Causes extreme fragmentation
• Log grows back immediately
• Adds unnecessary I/O

✅ Correct approach

• Pre-size data and log files
• Shrink only as a last-resort emergency

---

❌ 9. NOLOCK Everywhere (READ UNCOMMITTED)

Why it’s abused

“To avoid blocking”

Why it’s dangerous

• Dirty reads
• Missing rows
• Duplicate rows
• Inconsistent results

✅ Correct approach

• Use:
• Snapshot isolation
• Proper indexing
• Fix blocking at its source

---

❌ 10. Disabling Auto Update / Auto Create Statistics

Why this breaks performance

• Optimizer relies on statistics
• Leads to bad cardinality estimates
• Causes wrong join strategies

✅ Correct approach

• Leave both ON
• Use async stats if needed

---

❌ 11. Turning Off Query Store

Why this is a red flag

• Loses plan history
• Cannot track regressions
• Makes troubleshooting harder

✅ Correct approach

• Query Store should be ON for almost all systems
• Especially OLTP

---

❌ 12. Ignoring TempDB Configuration

Common red flags

• Single data file
• Default 8MB size
• No monitoring

Consequences

• PAGELATCH_UP waits
• Random performance drops
• Allocation contention

✅ Correct approach

• Multiple data files
• Pre-size appropriately
• Monitor TempDB usage

---

❌ 13. Forcing Join Hints and Query Hints Everywhere

Why it’s risky

• Locks the optimizer into bad choices
• Breaks when data changes
• Causes long-term regressions

✅ Correct approach

• Fix statistics
• Fix indexes
• Use hints only when justified and tested

---

❌ 14. Excessive Fill Factor Changes

FILLFACTOR = 50

Why this backfires

• Bloated indexes
• More I/O
• Worse cache efficiency

✅ Correct approach

• Adjust fill factor only for:
• Hot, heavily updated indexes
• Typical OLTP: 90–95

---

❌ 15. Treating CXPACKET as a Problem

Reality

• CXPACKET is a symptom, not a root cause

Wrong fix

• Disabling parallelism

✅ Correct approach

• Tune:
• Cost Threshold for Parallelism
• MAXDOP
• Fix skewed execution plans

---

❌ 16. No Baseline Metrics

Why this is dangerous

• You don’t know what “normal” is
• Can’t measure improvement or regression

✅ Correct approach

Track:

• Wait stats
• CPU usage
• I/O latency
• Query performance trends

---

❌ 17. Blind Trust in Execution Plan % Cost

Why it misleads

• Percentages are relative, not absolute
• Cheap plan can still be slow

✅ Correct approach

Always check:

• Actual vs estimated rows
• Logical reads
• Execution time
• Memory spills

---

🚨 Ultimate DBA Red Flags (Top 10)

If you see these in production, investigate immediately:

1. DBCC DROPCLEANBUFFERS
2. DBCC FREEPROCCACHE
3. Frequent SQL Server restarts
4. MAXDOP = 1
5. Cost Threshold = 5
6. Lightweight pooling ON
7. Rebuild-all-indexes jobs
8. NOLOCK everywhere
9. Single TempDB data file
10. Query Store OFF

---

🧠 Final Takeaway

If a command makes SQL Server “forget” things, it’s almost never a fix.

Real SQL Server performance tuning is about:

• Understanding execution plans
• Fixing data access patterns
• Letting the optimizer do its job

Not about panic-driven configuration changes.