One of my favorite movies from my youth was Terry Gilliam’s Time Bandits. It’s about a young boy who gets accidentally caught up with some time-travelling thieves, who are being chased by forces of good and evil.
And, without giving too much away, at the end of the movie the lead character shouts “Don’t touch it! It’s evil!” Which is exactly how you should think about some of the configuration options in SQL Server.
SQL Server has quite a few instance and database configuration options, which is great if you need to make changes for different business workloads. But some of these configurations can do more harm than good, especially with modern version of the product.
As a consultant, I’ve had the opportunity to work with many clients who have a diverse range of configurations for their instances. And every now and then I see some that have been configured for what I can only presume is a predilection for danger. I mean, little to no good can come of them.
So today I wanted to share with you a few that I have seen used or changed, and to recommend to you with all the influence that I may have, that you DON’T TOUCH THEM – THEY’RE EVIL!
In plain words, here are some things in SQL Server you should never touch (or at least should have a very, very specific reason for even thinking about touching) in 2026. And I’m only mentioning them because I’ve seen them changed somewhere and had to clean up the mess.
Database-level settings
Auto Close. Enabling this closes the database when the last user disconnects, requiring it to reopen (with all the overhead of recovery, cache rebuilding, etc.) on the next connection. This made some sense on resource-constrained 1990s desktops; today it just causes inexplicable latency spikes and clutters the error log. Always leave it OFF.
Auto Shrink. Enabling this periodically shrinks data and log files. And when is “periodically”? Like Monty Python’s Spanish Inquisition, it’s when you least expect it. These shrink events cause massive index fragmentation, generates huge amounts of I/O, and best of all, the files usually just grow right back. It’s a textbook example of a setting that sounds helpful but actively harms performance. Always leave it OFF.
Auto Create Statistics. Leave it ON, which is the default. Disabling this is almost always a mistake, because the optimizer needs statistics to make good plans. Sometimes clients turn this off because they “manage statistics manually” and of course that never ends well.
Auto UPDATE Statistics. Same story, leave it ON. If you’re worried about updates happening at bad times, enable AUTO_UPDATE_STATISTICS_ASYNC as a reasonable middle ground for OLTP workloads.
Delayed Durability. Setting this to FORCED allows transactions to commit before the log record is hardened to disk. This means you’re trading durability for throughput, which means a power loss or crash can lose committed transactions. In plain English, that means lost data. There are narrow OLTP-with-log-bottleneck scenarios where it’s defensible, but even those should use ALLOWED instead of enabling it database-wide. It’s best to leave it DISABLED unless you have a clearly defined use case, and the business has explicitly accepted data loss risk.
Page Verify = NONE or TORN_PAGE_DETECTION. CHECKSUM has been the best answer for page verification since SQL Server 2005. Unfortunately, some folks keep copying databases from before Y2K from instance to instance (or worse, installing in-place upgrades) without ever changing this setting. The other older options leave you blind to certain forms of corruption, and corruption is also evil.
Recursive Triggers. Enabling this allows for a trigger to call another trigger, which could potentially call another trigger, which could potentially call another trigger, which could…see, infinite loops are no fun. Leave this OFF.
Trustworthy. For security reasons, please, please, please do NOT turn this on. Enabling this allows code in the database to access resources outside it under the database owner’s context. If the owner is a high-privilege account (like sa or some other sysadmin), then this is an easy privilege escalation path for malicious actors. That said, you might be told this setting is required for some specific scenarios, but if that’s the case then you should audit the activity of any database with this on.
DB Chaining. Enabling this allows for cross-database ownership chaining. This is also a security risk, for similar reasons to TRUSTWORTHY. Leave it OFF unless specifically required by someone who has signed in blood (metaphorically speaking, of course) that they know what this does.
Instance-level settings
Common criteria compliance enabled. Off by default, so only enable if you require Common Criteria certification compliance. This setting changes login auditing, residual information protection, and column GRANT behavior in ways that can… let’s just say surprises people, and in a bad way.
C2 audit mode. Also OFF by default. If you turn this ON, it generates massive trace files and will shut down the instance if it can’t write audit data. Sounds like fun, right? SQL Server Audit is a better option for modern versions.
Default trace enabled. This is ON by default. Turning this OFF blinds you to a lot of auditing information for negligible performance benefit. Only an anarchist would change this.
Fill factor (%). Server-wide fill factor default should be left at 0 (which means 100% of your data pages are filled with delicious data). Setting a server-wide non-zero value applies to every index that doesn’t specify its own, which is almost always wrong. Only set fill factor per index based on the index’s update pattern.
Lightweight pooling (also called “fiber mode”). This sounds modern, but it solves a problem that effectively doesn’t exist on modern hardware. And by modern I mean your CPUs are from the 21st Century. Almost no workload benefits, and it breaks CLR, OLE DB/linked servers, and various other features that rely on thread affinity, so Microsoft recommends never enabling it unless explicitly told to by Microsoft Support.
Locks. Manually setting a lock count was a thing in the SQL 7/2000 era, but you don’t need to do that now. Leave it at the default of 0 (dynamic) and let SQL Server manage the locks.
Min server memory. Leave it at the default is 0. Setting this aggressively high is a common mistake, where people think it “reserves” memory for SQL Server, but SQL Server doesn’t actually allocate at this value at startup. The setting actually sets a floor below which SQL Server won’t release memory under pressure, and that can make the OS cranky with memory limitations. If Windows ain’t happy, ain’t nobody happy.
Ole Automation Procedures. This is OFF by default. In 2008 you may have thought using sp_OACreate and other related procs was cool, but now this is significant a security concern and a sign of legacy code that should be rewritten.
Priority Boost. It’s 2026 and this is still in the product, and I still every now and again see instances with this enabled. Because it sounds magical, right? Sure, magical like Voldemort, because it raises SQL Server’s process priority above the OS itself, which can starve critical OS threads (including the ones SQL Server depends on) and cause cluster failures. Always leave it OFF, because Microsoft has deprecated it and explicitly recommends never enabling it.
User connections. You can set this up to 32,767 but leave it at 0 (which means no manual limit). Setting a manual cap creates a weird ceiling that can cause connection failures during traffic spikes.
Summary
I know that’s quite a few settings to consider, but think of all these as things you never need to know about…unless you see someone has turned them on. And at that point, you should start asking questions.