A few weeks back, a customer reached out to me:
We recently had a performance issue with the Web application that may have been tied to database tuning. Could you please take a quick look at the sql server and see if it there are any "red flags" for performance or tuning, or opportunities to improve performance?
I ran a fast health check that afternoon. Within less than one hour, I had the answer — five configuration issues, most of them out-of-the-box defaults that had never been touched, all of them silently compounding since the day the server was built.
Here's what I found:
1. Max Server Memory: Never configured.
The setting was still at 2,147,483,647 MB — roughly 2 terabytes. The server had 12GB of physical RAM. Because it wasn't given a cap, SQL Server was continually trying to claim more than it had to give, starving the OS and anything else trying to run on that box. I recommended setting it to 8GB.
2. Power Plan: Balanced mode.
The server had 2.10GHz processors, but Windows was throttling them to save energy -- on a database server. 🤔 I can assure you, SQL Server wants and needs high performance. I recommended switching to High Performance.
3. TempDB: Undersized and misconfigured.
Four data files at 392MB each, a 72GB log file, and all of them set to grow in 64MB increments. This is a bottleneck waiting to happen. I recommended adding 4 more datafiles and resizing all files to 2GB each with appropriate filegrowth settings.
4. Cost Threshold for Parallelism: Default of 5.
This configuration setting hasn't been appropriate for most workloads in over two decades. I recommended changing it to 60.
5. Transaction log larger than the data.
The user database had a 3.3GB transaction log and a 813MB data file on a database in SIMPLE recovery mode. That's a sign something went sideways at some point and was never corrected.
Beyond the configuration issues, I also found evidence of serious plan cache inefficiency: one query alone had generated over 1,000 different execution plans, and there were 14,000+ single-use plans consuming memory that should have been caching data.
But here's the thing — those kinds of symptoms are almost expected when the underlying server configuration is this far off. You can't get a clear read on plan cache behavior when the server is starved for memory and throttled at the CPU. Fix the foundation first, let it burn in, then revisit the plan cache for inefficiencies.
All of the above fixes were applied in a few minutes collectively without any interruption.
Two weeks later, I checked back in.
The server looked better across the board. That query with 1,008 cached plans? Down to 857 — and we hadn't touched the code. The plan cache was behaving more predictably now that the server had proper resources to work with.
The remaining issues I flagged — cursor usage, implicit conversions, key lookups, 20,000+ ad hoc plans — were application-level concerns, not configuration. Now we knew exactly where to look next, and we had a stable baseline to measure against.
My final recommendation: enable optimize for ad hoc workloads, clean up the mass of user tables that had accumulated in the master database, and move forward with a cleaner foundation.
This is what a health check does.
It's not magic. It's experience and knowing where to look. Many SQL Server environments have issues like these — settings that were never tuned, defaults that don't make sense for production workloads, small problems that compound quietly over the years before they surface.
You don't know they're there until someone looks.
If your SQL Server has never had a health check, or if it's been a while - let's talk. I do this remotely, I do it fast, and I will find the things that have been silently costing you since day one.
No comments:
Post a Comment