10 Overlooked SQL Server Configuration Changes That Dramatically Improve Performance
Over the years working with SQL Server—starting back in 1990—I’ve seen countless environments struggling not because of bad hardware or poor application design, but because of a handful of small, overlooked configuration issues. These are things that don’t always show up on day one, but quietly drag down performance as workloads grow.
In this post, I’m recapping what I covered in a recent episode of the Stedman SQL Podcast: the SQL Server settings and habits that have the biggest impact on real-world performance. These are the same areas our team focuses on every week through our SQL Server Managed Services at Stedman Solutions, which you can learn more about here:
https://stedmansolutions.com/managed-services/
If you want a quick win list of places to look for performance improvements, this is it.
TempDB Configuration
TempDB is the busiest database on every SQL Server instance. Table variables, temp tables, spills, version store activity, row versioning, and a huge amount of SQL Server’s internal work all happen there. If TempDB isn’t configured well, your entire SQL Server can slow to a crawl.
The fundamentals still matter. Multiple data files. Fast storage. Separation of data and log. On newer versions we sometimes go beyond the old “one file per core up to eight” guideline, but only when it makes sense.
This is also an area where Database Health Monitor shines. The TempDB history, IO by hour, allocation breakdown, and high-usage query reports make it easy to see when TempDB is the real bottleneck. You can download Database Health Monitor at:
http://DatabaseHealth.com
Trace Flag 3226
This is one of the simplest but most useful configuration changes you can make. Without TF 3226, SQL Server writes every single backup operation to the error log—even though that information is already stored in MSDB. On systems with frequent log backups, this can inflate the error log with tens of thousands of unnecessary messages per day.
The error log should contain errors, not noise. Turning on this trace flag keeps it clean and makes troubleshooting much easier.
Separating Data and Log Files
For heavily loaded systems or environments with tiered storage, separating the data and log files can be a major performance win. Log writes are sequential and benefit from the fastest available storage. Data file reads and writes have different IO patterns.
We often see Azure environments where someone accidentally provisions slow disks for log files. Simply increasing the allocated IOPS or moving the files to faster storage instantly fixes months of performance complaints.
File Growth Settings and VLFs
One of the most common misconfigurations I see is percentage-based file growth. A database growing by 10% at 500GB means a 50GB instantaneous growth event. SQL Server must zero that space before continuing, and everything waits—sometimes for minutes.
Use fixed-size growth settings appropriate to your storage speed, usually between 256MB and 1GB. And don’t forget to check your Virtual Log Files (VLFs). Databases with thousands or tens of thousands of VLFs will experience slow restores and degraded performance.
Database Health Monitor makes reviewing VLF counts extremely easy, and includes tools to help you fix them safely.
Alerts
Alerts don’t directly speed up SQL Server, but they prevent performance disasters. Severity 19 through 25 errors and 823, 824, 825 conditions can warn you of IO failures or corruption before things go completely sideways.
I’ve seen too many outages that could have been prevented with one properly configured alert.
Smarter Index Maintenance (and Avoiding Maintenance Plans)
SQL Server Maintenance Plans have been causing performance headaches for decades. They tend to perform unnecessary rebuilds, reorganize when it isn’t needed, and blindly rebuild statistics the moment your indexes were already rebuilt.
We use the Ola Hallengren scripts with customized thresholds. They rebuild only what needs attention, reorganize only when appropriate, and avoid hours of unnecessary work.
A client I worked with recently had nightly maintenance running from 11pm to nearly 11am—slowing the system for everyone each morning. By replacing the maintenance plan with the OLA scripts and proper settings, we brought the window down to about two hours.
Fill Factor Configuration
The old advice for fill factor came from the days of slow spinning disks. Most modern systems on SSDs benefit from higher fill factors—often 99% or even 100%. Fewer pages means fewer reads, and faster queries.
There are exceptions, of course, but many environments never revisit this setting and leave performance on the table.
Backup Compression
Backup compression is a no-brainer for almost every SQL Server. It reduces IO, speeds up backups, and dramatically speeds up restores. Unless your CPU is pegged at 99% nonstop—and if it is, you have bigger problems—turning on compression is almost always a win.
SQL Server 2019 and newer fixed issues with compression and Transparent Data Encryption (TDE), so modern versions handle this much better.
Read Committed Snapshot Isolation (RCSI)
For many clients, RCSI has been the single biggest performance improvement we’ve ever implemented. RCSI reduces blocking and deadlocking in read-heavy environments by using the version store in TempDB to serve consistent reads without interfering with writers.
It’s not for every environment, and it requires SQL Server 2019 or newer for best results. You should also test first and make sure TempDB is configured properly. But when it fits, it transforms systems that used to grind to a halt under blocking.
For deeper details, check out our dedicated RCSI podcast episode:
https://Stedman.us/RCSI
Monitoring and Tracking Change
If there’s one truth in SQL Server performance tuning, it’s this:
You cannot improve what you’re not monitoring.
I’ve worked with developers who deploy changes and declare them “faster,” when the actual wait stats and IO usage showed the exact opposite. Without monitoring, your SQL Server can silently degrade until the day it collapses under load.
This is exactly why I built Database Health Monitor. It baselines performance, tracks wait stats, highlights blocking, reports on indexing issues, monitors TempDB, and gives you a full picture of server health over time.
Download it at:
http://DatabaseHealth.com
Final Thoughts
Try applying one change per week, and track the results. Monitoring is the key—if you flip a switch but don’t measure anything, you’ll never know whether you improved performance or made it worse.
If you want to improve your SQL Server skills, November is Stedman SQL School month with big promotions happening throughout the month:
Stedman.us/school
And to stay on top of SQL Server performance topics like this, check out the Stedman SQL Podcast:
https://stedmansolutions.com/home/sql-server-podcast/
Watch this episode now: https://stedmansolutions.com/2025/11/19/stedman-sql-podcast-sn-2-ep-28-10-things-every-dba-should-know-for-performance/
Listen on Spotify: https://open.spotify.com/episode/1tfq37F6sBnv6hZ55NYtFA?si=uwmBUHL6Tg6eRW3zCac39A
SQL Server Managed Services: Why choose Stedman Solutions over a DBA.
Check out Season 3 episodes: https://stedmansolutions.com/home/sql-server-podcast/season-3/
Don’t miss the other episodes from Season 2
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!