• Skip to main content
  • Skip to primary sidebar

DallasDBAs.com

SQL Server Database Consulting

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

Configuration

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

SQL Server TempDB: The Most Overlooked Bottleneck

June 4, 2025 by Kevin3NF Leave a Comment

Configuration, Performance, and Unnecessary Usage

TempDB is the SQL Server equivalent of a junk drawer – everyone uses it, nobody monitors it, and eventually it becomes a bottleneck you can’t ignore.

Whether it’s poorly configured from the start or getting hammered by bad execution plans, TempDB often becomes the silent killer of performance. The good news? A few targeted changes can make a big impact.

TempDB Configuration:

  • File Count: “one file per core” is outdated. Only go past 8 if you have verified PFS or SGAM contention exists
  • File Size and Growth: Pre-size your files to avoid autogrow OR ensure Instant File Initialization is enabled
  • Trace Flags 1117 & 1118: relevant for SQL Server 2014 and older, not in newer versions
  • Placement: Should TempDB be on its own disk? Ideally, yes. Fast disk, Fastest RAID, separate controller/path to the drives

 

Identifying TempDB Bottlenecks and Stalls

  • Read/Write Latency: Query sys.dm_io_virtual_file_stats to see stats on ms/read and ms/write. Write is usually worse.
  • Spills: Look for queries that are spilling out to tempdb due to poorly estimated memory grants
  • Monitoring Tools: I use exec sp_blitzcache @Sortorder = ‘Spills’ for this (part of the First Responder Kit)

 

Reducing Traffic TO TempDB

  • Avoid Unnecessary Sorts and Spills: Bad execution plans are a TempDB killer, and unneeded sorts make it worse
  • Test the Use of CTEs, Table Variables, and #Temp Tables: Test your code with the different types of temp objects, rather than just blindly using the easiest one. Nobody cares for 10 rows. They ALL care about 10 Million rows!
  • Version Store Traffic: If you are using RCSI on multiple databases, one transaction left open can blow out tempdb.
Select
   db_name(database_id)
   , reserved_page_count
   , reserved_space_kb/1024/1024 as [GB Used]
From
   sys.dm_tran_version_store_space_usage
Order by
   reserved_space_kb desc
  • Cursors: Some (all?) cursors live in tempdb. At some point a cursor gets too be to be efficient and you’ll want to re-code for speed, as well as tempdb space

 

TempDB Best Practices and Quick Wins

  • Multiple Files with Equal Size: Equal size is key…autogrow all files is on by default since 2016.
  • Instant File Initialization: Saves time on growth events.
  • Regular Review: TempDB isn’t “set it and forget it”. Schedule health checks, just like for the rest of your SQL Server

 

Bonus Content: What’s in your tempdb right now?

-- Determining the amount of free space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [tempdb free pages],
  (SUM(unallocated_extent_page_count)*1.0/128) AS [tempdb free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by the version store
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
  (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by internal objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
  (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by user objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
  (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

Thanks for reading!

 

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

Mistakes IT Shops Without a DBA Make

March 7, 2025 by Kevin3NF Leave a Comment

We get it.

There’s no budget for a DBA on staff, or even a contract support firm like Dallas DBAs.

You are not alone! There are thousands of firms running a vendor-supplied or home-grown application with SQL Server as the back end. The vendor may be the only support option you have. Many of those vendors don’t have a DBA either!

So, you throw hardware and Google at it.

We get it.

If this is you, please check out this list of things you need to be aware of.

Skipping Backups:

SQL Server is not automatically backing up your databases for you. You must tell it this most basic concept. Both system databases (master, model, msdb) and user databases. You can use the built-in Maintenance Plans (a little cumbersome, but they work), a 3rd party product like Red Gate’s SQL Backup Pro or Idera’s SQL Safe Backup. There are also free “community scripts” such as Ola Hallengren’s Maintenance Solution.

I’ll preach this until I retire and beyond…part of a backup plan is to test the resulting file to make sure you can restore it! Use Test-DbaLastBackup  from dbatools.io to do this. It’s free.

Corruption Checking:

SQL Server is only doing the lightest level of corruption checking, and that is only if your databases and backups are set to Checksum:

Database Options:

 

 

 

SQL Server level, Database Settings page:

 

 

 

 

 

 

IF these are enabled, SQL Server might throw an error into the SQL Server Errorlog and maybe even into msdb.dbo.suspect_pages

You will want to enable some alerts, set up Database Mail, and send emails to someone on your team to deal with this. Corruption won’t fix itself. Many 3rd party monitoring tools will monitor this for you.

Index and Stats Maintenance

Indexes and Statistics are separate items but tied closely together.

Index fragmentation is not as big of a problem with the advent of solid-state drives (SSD), but it can still be an issue.  Non-clustered indexes store their data in 8K pages, which must be pulled into memory just like data pages. The more fragmentation, the more pages in memory. If you are running a low memory system (relative to data size), you may consider more frequent defragmentation of your busiest tables.

Statistics are generated on indexes and unindexed columns that are part of a query. These stats are critical to getting an optimized query plan from the system. Updating them gives you the best chance of queries running efficiently, along with query code, proper hardware layout, etc.

My default setup for most production servers is to defragment indexes weekly (at most), and update stats nightly.

My defrag parameters: 0-30% fragmentation, ignore. 30-50%, reorganize. 50% or higher, rebuild. Minimum number of pages, 5000.

I use the default algorithm for stats updates unless I have evidence that I need a higher sample size.

How to do these? Again – 3rd party tools, built-in maintenance plans, Ola Hallengren scripts or (yuck) re-invent the wheel and roll your own T-SQL code.

 

The Bottom Line:

If you don’t do any maintenance on your SQL Server, you are risking data loss and performance issues. And you’ll probably be calling me with a 2TB log file.

Want to dig deeper? Check out my Getting Started with SQL Server Maintenance course on Pluralsight. It’s retired, but still relevant. You’ll need a subscription.

Filed Under: Accidental DBA, Beginner, Configuration, SQL Tagged With: syndicated

SQL Server Express Size Limit

July 2, 2020 by Kevin3NF Leave a Comment

My Pluralsight course for new SQL Server DBAs

 

(Needs to upgrade to a bigger bowl)

Common scenario:

Company is using SQL Server Express to save on licensing costs for a small database. Over time this database grows and is getting close to the hard 10GB limit.

I just got off the phone with a client and their client is in this situation. During the conversation, she mentioned that the DB has information for four “regions.”

They already have a purge process in place, and compression is not an Express Edition feature.

Options I gave:

  • Decrease the retention (her client already said no to this)
  • Split the data into 4 databases – 1 for each region
    • Might require some re-work of applications and connection strings
  • Install a second Express instance and split the data between them
    • Easier on their app
  • Convince the end customer to buy SQL Standard (1% chance this is going to happen)
  • Buy Standard Edition for them and eat/split the cost to keep a huge customer happy
  • Move to a different platform (nope…all SQL Server shop)
  • I saw a blog post that you can bypass 10GB by using the Master database instead
    • That’s the worst idea I’ve ever read. If you do that, never call me. 🙂

If you have other legitimate things I should add to this list that Microsoft will support, please share in the comments.

Conclusion:

If your data is important, buy the proper version for your needs.  Maybe that’s Standard, maybe an Azure VM and spread out the SQL licensing over many months. But don’t do crazy things to try to get around the limits.

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs | |

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

SQL Server Identity Skipping

February 4, 2020 by Kevin3NF 2 Comments

My Pluralsight course for new SQL Server DBAs

SQL Server may skip 1000 numbers on an Identity column if the server crashes. Here’s why:

Too long, didn’t watch version:

SQL Caches 1000 numbers at a time to boost insert performance. In a crash and recovery, those numbers are gone.

SQL 2016 and earlier – use instance-wide trace flag 272 to turn off this behavior (performance might suffer).

SQL 2017 and later – its now a database scoped config item:

use MyDB;
go
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO

Video shows a walk-through of before and after each fix, plus a “Two guys walk into a bar” joke when I disappeared to troubleshoot a broken demo…

Thanks for reading and/or watching!

Kevin3NF

My Pluralsight course for new DBAs

Follow @Dallas_DBAs

Filed Under: Configuration, video Tagged With: syndicated

  • Go to page 1
  • Go to page 2
  • Go to page 3
  • 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...