• Skip to main content
  • Skip to primary sidebar

DallasDBAs.com

SQL Server Database Consulting

  • Services
  • Pocket DBA®
  • Blog
  • Testimonials
  • Contact
  • About

Install

SQL Server Auto-settings: The Good, the Bad, and the Ugly

July 9, 2025 by Kevin3NF Leave a Comment

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

Filed Under: Configuration, Install, Performance, SQL Tagged With: syndicated

SQL Server Editions: What’s the Difference and Why It Matters

June 25, 2025 by Kevin3NF Leave a Comment

Understanding the Limits Before You Hit Them

If you’re responsible for a SQL Server instance but don’t live and breathe licensing, you’re not alone.

Many IT managers and sysadmins inherit a SQL Server setup only to find out later that they’re boxed in by the edition they didn’t choose. And by the time those limits show up, it’s often during a crisis. I’ve seen this many times over the years.

Let’s break down the main SQL Server editions: Express, Web, Standard, and Enterprise as well as what makes each one different. Not just in features, but in what they let you do… or stop you from doing.

 

SQL Server Express: Free, But with Hard Limits

This is Microsoft’s free entry-level edition, often bundled with lightweight apps or used by developers.

  • Max database size: 10 GB per database
  • Max memory usage (SQL engine): 1.4 GB
  • Max CPU usage: lesser of 1 physical socket, or 4 cores
  • Agent jobs: Not supported – this is a big deal

 

Use case: OK for dev/test, very small apps, or teaching environments.

Gotchas: You’ll quickly hit resource limits on memory or database size. Backup and maintenance automation via SQL Server Agent isn’t available. You have to script around it, and schedule jobs externally.

 

SQL Server Web: Affordable for Hosting, Limited for Business Use

Designed for web hosting providers – only available through certain Service Provider License Agreement partners.

  • Max DB size: No enforced limit
  • Max memory usage: 64 GB for DB engine
  • Max CPU usage: lesser of 4 sockets or 16 cores
  • SQL Agent is available for job scheduling
  • Key limitations: No advanced high availability (e.g., no AGs), no TDE (encryption), limited BI and performance features

 

Use case: Public-facing websites and web apps hosted via service providers.

Gotchas: Often confused with Standard Edition, but it’s not intended for in-house business workloads.

 

SQL Server Standard: The Most Common, and Commonly Misunderstood

This edition powers a majority of small and mid-size businesses.

  • Max DB size: No enforced limit (limited only by hardware)
  • Max memory usage: 128 GB for the SQL engine (other components like SSRS have their own limits)
  • Max CPU usage: lesser of 4 sockets or 24 cores (configuring your VM setup is critical here)
  • Includes: Backup compression, basic availability features (e.g., basic Always-On AG with 1 DB), TDE (SQL 2019+)
  • SQL Server Failover Clustering on 2 nodes is available
  • SQL Agent is available for job scheduling

 

Use case: OLTP workloads, departmental databases, general business apps.

Gotchas: Hit memory and CPU limits faster than expected in busy environments. Lacks advanced features like partitioning, online index rebuilds, and many encryption/performance options.

 

SQL Server Enterprise: All the Bells and Whistles

The top-tier edition. If you’re asking “Can SQL Server do this?” Enterprise almost always says yes.

  • Max DB size: No enforced limit
  • Max memory usage: OS limit (can use terabytes)
  • Max CPU usage: OS limit (can use 64+ cores)
  • Includes: Online index operations, table partitioning, in-memory OLTP, Always On with multiple DBs, TDE, columnstore indexes, and more
  • SQL Agent is available for job scheduling

 

Use case: High-performance, mission-critical systems. Often used with HA/DR needs, heavy workloads, and large-scale BI.

Gotchas: Very expensive. Licensing is per core, with a 4-core minimum per physical server or VM.

 

SQL Server Developer: Enterprise for non-production usage.

SQL 2025 might have a Dev edition coming with Standard Edition compatibility. As of this writing 2025 is in public preview

 

So… Which Edition Do You Have?

You can find out by running this query:

SELECT
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('EngineEdition') AS EngineEdition;

 

The Bottom Line:

Don’t wait until a restore fails or CPU caps out to figure out what edition you’re on. Knowing your edition helps you plan capacity, budget smarter, and avoid downtime when the server starts groaning.

Filed Under: Configuration, Install, SQL Tagged With: syndicated

IRL #2: Cannot Connect to SQL Server

January 23, 2019 by Kevin3NF Leave a Comment

IRL #2 – Connectivity Issues

Problem: Application owner is trying connect to SQL Server to create a database, connection failed.  Error message suggests checking Instance Name and “Allow Remote Connections” option set to True.

Background: This client has a wide variety of SQL Servers in Prod, Test and Dev.  Many different versions and editions, quite a few coming from 3rd party vendors.  This was in a new test environment for an application already up and running in Prod.

Resolution Attempts:

  • Try to Connect via IP address – failed
  • Verify Instance name – verified
  • Verify “Allow Remote Connections” is set to 1/True – it is
  • Verify SQL Browser Service Running – discovered Set to Disabled
  • Verify TCP/IP network protocol enabled – discovered disabled

Solution:

  • Enable TCP/IP for Named Instance SQLEXPRESS03
  • Restart Instance
  • Set SQL Browser Service to Automatic and start
  • Test logon via UDL file – success

Lessons you can benefit from:

  • SQL Express default install only enables Shared memory connections
  • Error messages are not always going to give you complete information
  • Have a process for testing

This entire process took about 10 minutes, including the instance restart.  There are 3 other Express edition installs on this server.

 

Other posts in the IRL – In Real Life series:

https://dallasdbas.com/category/sql/irl/

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

Filed Under: Configuration, Install, IRL, Troubleshooting

Your SQL Servers are Misconfigured

August 2, 2018 by Kevin3NF Leave a Comment

Corgi pupper is sad your Server is slow

So are some of mine.

So are 90% of the servers I run across, especially in mid-size businesses ( <1000 employees, or 10-20 servers typically).

Its probably not your fault.

There are a bunch of instance level settings in SQL Server that need to be set that are specific to your environment and the workload you are going to run against it.  Most of the defaults that are in the SQL Server installer are set up to make sure the performance is acceptable on a very modest server.  Until version 2016, the installer had not changed much.

The problem is that in a large number of organizations, the person doing the installation of SQL Server is a Developer, or a SysAdmin running a script.   Both are great at their core jobs, but does either of them know what Cost Threshold for Parallelism is?  It is not in the installer…it has to be configured post install or left at a very old default number that most likely makes no sense for you.

Likewise, if you are running SQL Server installed from a script that came with a 3rd party software product, you very likely have the “safest” possible settings in place.

Some of the most common things I see everywhere:

  • Instant File Initialization not enabled
  • Cost Threshold for Parallelism not set correctly
  • MAXDOP set to 1 or ALL CPUs
  • MAX Memory left at default
  • MAX Memory left at default on a multi-instance server
  • Default file locations for system databases, or all
  • No maintenance processes

That is the short list.

Let’s talk.  Click HERE to set a meeting on my Calendar.

Thanks for reading!

Kevin3NF

Filed Under: Configuration, Install

SQL Saturday Minnesota 2017

August 31, 2017 by Kevin3NF Leave a Comment

Now that the official schedule has been published, I am pleased to announce that I will be doing a pre-con and a regular session at SQL Saturday Minnesota on October 6-7. 2017.

The Friday pre-conference will be “DBA Fundamentals for the Junior, Accidental and non-DBAs.”  This will be a wide ranging day-long discussion on the underlying fundamental pieces of SQL Server Administration that are beneficial to both new DBAs, and those that have to work with or fill in for them.  Please see the link for details, and feel free to contact me with any questions.  There is a $110 (plus service fees) charge for this, which is split between me and the local SQL User group organizing the event, after expenses.

The Saturday regular session will be “Backups for new/non-DBAs…the Why, not the How”, where we will cover Disaster recovery scenarios and the part SQL Server plays in the recovery.  We won’t spend much time on specifically defining backup types, etc.    Currently this is scheduled for the 1-2pm time slot, but that may change.

Edit: my Saturday session is now 10:15am – 11:15am 🙂

I’m very excited to be coming to the MSP area!  I even have a friend that lives in the area that I get to see for the first time in 3 years…plus all of the new friends I will make at the events!

Thanks,

Kevin3NF

Filed Under: Accidental DBA, backup, Beginner, EntryLevel, HADR, Install, Security, SQLSaturday

Installing SQL 2016 Developer – Extreme Basics

December 29, 2016 by Kevin3NF Leave a Comment

If you are a student, a developer or a non-dba wanting to get started with learning more about SQL Server and you need an install to play with, you have a couple of free choices:

  • SQL Server Express – free, but has some size and performance restrictions. Also, no SQL Agent, so no job scheduling.
  • SQL Server Developer – also free, full code of the Enterprise Edition, but cannot be used legally in production. Because its a dev edition.

If you’ve never installed SQL server before and are not sure what to do, this video is for you. It is targeted to new SQL users, installing on their laptops/workstations. I take all the defaults and describe why. I install just the database engine (and the SQL Agent that comes along with that)…no Reporting services, no Analysis Services, etc.

This is just so you can see it before you do it. Enjoy!

Thanks for watching!

Kevin3NF

Filed Under: Accidental DBA, Beginner, Install, SQL

  • Go to page 1
  • Go to page 2
  • Go to Next Page »

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!

Home Blog About Privacy Policy
  • Home-draft
  • Blog
  • About Us

Copyright © 2025 · WordPress · Log in

 

Loading Comments...