Settings That Could Be Hurting Your Performance
If you’ve ever created a new SQL Server database and just left the default settings alone, you’re not alone.
Microsoft provides a lot of “helpful” options under the hood (especially at the database level) designed for flexibility and ease of use. Some of them are better suited for development and testing environments, not production.
Let’s take a look at the three most misunderstood “auto” settings in SQL Server, and how they might be quietly causing you pain behind the scenes.
Preface: SQL Server is a shared resource system. A database with bad settings/performance can be much like a screaming baby on a plane. One crying person, but everyone is affected.
Auto_Close – The Bad
This setting causes the database to shut itself down after the last user disconnects. Sounds resource-friendly, right? Until you realize it has to reopen the database every time someone queries it again, which can take time and may result in application timeouts. Per Microsoft, “The AUTO_CLOSE process is asynchronous; repeatedly opening and closing the database doesn’t reduce performance”
- Impact: Adds latency and I/O traffic every time the database “wakes up”
- Use case: Most useful in local development or single-user environments
- Recommendation: Set to FALSE for anything but the most trivial uses
Auto_Shrink – The Ugly
This setting allows SQL Server to automatically shrink your database files to reclaim space. Unfortunately, it often creates more problems than it solves.
- Impact: Causes frequent file growth/shrink cycles, which may lead to fragmentation and performance issues. Shrinking of data files can cause locking and blocking issues. Do these manually, and off hours when required.
- Use case: Almost never appropriate for production
- Recommendation: Set to FALSE and manage growth/shrink operations manually and intentionally
Auto_Update_Statistics – The Good (Usually)
This one usually helps. SQL Server uses statistics to create execution plans. Keeping them current is critical for performance.
- Impact: Keeps query plans accurate and efficient
- Watch out: On very large tables, this setting can trigger noticeable delays if updates happen mid-query
- Recommendation: Leave it TRUE in most cases. Also consider enabling AUTO_UPDATE_STATISTICS_ASYNC to prevent blocking.
Other “Auto” Settings – Grouped for Sanity
Here are a few more database-level settings worth knowing about:
- Auto_Create_Statistics: Generally good. Leave it on unless you’re manually managing stats at an advanced level.
- Auto_Update_Statistics_Async: Helpful in larger environments to reduce blocking on stats updates.
- Auto Create Incremental Statistics (2014+): Niche but useful for partitioned tables.
- ALTER DATABASE [Yourdatabase] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = ON)
- Automatic_Tuning (starting with SQL Server 2017):
- Requires Query Store to be enabled and in read-write mode
- AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = { DEFAULT | ON | OFF } )
- I have not used this yet…proceed with caution
The Bottom Line: Set It and Regret It?
Default “auto” settings are often a compromise between convenience and control. For accidental DBAs and small teams, it’s easy to assume the defaults are “smart.” But smart for Microsoft’s general use case doesn’t always mean smart for your production workload.
It’s worth taking a few minutes per database to review these settings and make intentional decisions, especially Auto-Close and Auto-Shrink. These two alone are the source of more performance headaches than almost any others I see during client assessments.
SQL TidBits:
Want to quickly find which databases have these settings enabled?
SELECT
[name] AS DatabaseName,
is_auto_close_on,
is_auto_shrink_on,
is_auto_create_stats_on,
is_auto_update_stats_on,
is_auto_update_stats_async_on
FROM
sys.databases;
Thanks for reading!
— Kevin
