The 5 Most Common SQL Server Misconfigurations (and How to Fix Them)

After years of reviewing SQL Server instances and building AutoDBA's rule engine, I've seen the same configuration mistakes over and over again. These are the five that show up on almost every server we scan, and the good news is they're all easy to fix.

1. Max Server Memory Left at Default

This is the single most common misconfiguration we see. SQL Server ships with max server memory set to 2,147,483,647 MB (essentially unlimited). This means SQL Server will happily consume all available RAM on the machine, leaving nothing for the operating system, other applications, or the file system cache.

The result? Memory pressure, OS paging, and in bad cases the server becomes unresponsive. I've seen production outages caused by this exact setting being left at default after a fresh install.

The fix:

-- Leave 4GB or 10% (whichever is larger) for the OS
-- Example for a 64GB server:
EXEC sp_configure 'max server memory (MB)', 57344;
RECONFIGURE;

A good rule of thumb: take your total physical memory, subtract 4GB for the OS (or more if you're running other services), and set max server memory to that value. For servers with 128GB+ RAM, leave 10% for the OS.

2. Optimize for Ad Hoc Workloads is Disabled

When this setting is off (the default), SQL Server caches a full execution plan for every single query it sees, including one-off queries that will never run again. On systems with lots of dynamic SQL or ORM-generated queries, the plan cache fills up with thousands of single-use plans that waste memory.

With the setting enabled, SQL Server stores only a small plan stub on first execution. If the same query runs again, then it gets a full cached plan. This is almost always the right behavior.

The fix:

EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

This is safe to enable on virtually every SQL Server instance. I've never seen it cause a problem but I've seen it reduce plan cache memory usage by 50-90% on systems with heavy ad hoc query patterns.

3. TempDB Running on a Single File

TempDB is SQL Server's scratchpad. Every sort, hash join, temp table, and table variable uses it. When you have only one data file for TempDB, you get contention on allocation pages (PFS, GAM, SGAM) which creates the dreaded PAGELATCH waits.

Microsoft's recommendation is straightforward: one TempDB data file per logical processor, up to 8 files. All files should be the same size.

The fix:

-- Check current TempDB file count
SELECT COUNT(*) as file_count
FROM tempdb.sys.database_files
WHERE type_desc = 'ROWS';

-- Add files to match CPU count (up to 8)
-- Adjust path and size to match your enviroment
ALTER DATABASE tempdb ADD FILE (
    NAME = 'tempdev2',
    FILENAME = 'T:\TempDB\tempdev2.ndf',
    SIZE = 8192MB,
    FILEGROWTH = 1024MB
);

Make sure all TempDB files are the same size. SQL Server uses a proportional fill algorithm, so if files are different sizes you'll end up with uneven IO distribution which defeats the whole purpose.

4. Auto-Shrink is Enabled

This one makes me cringe every time I see it. Auto-shrink periodically checks if a database has more than 25% free space, and if so, shrinks the file. Sounds helpful right? It's actually terrible.

Shrinking causes massive index fragmentation because it moves pages from the end of the file to fill gaps earlier in the file, scrambling the physical order of your indexes. Then your next index rebuild grows the file back to where it was. So you end up in a cycle: shrink fragments indexes, rebuild grows file back, shrink runs again. You're burning IO for literally nothing.

The fix:

-- Check which databases have auto-shrink enabled
SELECT name, is_auto_shrink_on
FROM sys.databases
WHERE is_auto_shrink_on = 1;

-- Disable it
ALTER DATABASE [YourDatabase] SET AUTO_SHRINK OFF;

If you genuinely need to reclaim space from a database, do it manually with DBCC SHRINKFILE and then immediately rebuild your indexes. But auto-shrink should never be enabled on any production system. Full stop.

5. Cost Threshold for Parallelism at Default (5)

The "cost threshold for parallelism" setting controls how expensive a query has to be (in optimizer cost units) before SQL Server considers running it in parallel across multiple CPUs. The default is 5, which is absurdly low. Almost any query more complex than a simple key lookup will exceed cost 5.

This means your server is trying to parallelize tons of small queries that would run faster on a single thread. Parallel execution has overhead for thread coordination, memory grants, and exchange operators. For cheap queries, that overhead is more expensive than just doing the work on one core.

The fix:

-- Set to 50 as a reasonable starting point
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

50 is a good starting point for most workloads. Some people go higher (100 or even 200) on OLTP systems. The idea is that only genuinely expensive queries should go parallel. You can look at your query store or plan cache to see what cost values your queries typically have and adjust from there.

How to Check All Five at Once

You can obviously run these checks manually with sp_configure and sys.databases. Or you can run AutoDBA's free collection script and get a full diagnostic report that checks these plus about 20 other common issues automatically.

The free tier catches all five of these misconfigurations. If you want the AI-powered deep analysis with specific remediation scripts tailored to your workload, that's in the starter plan.