DallasDBAs.com

SQL Server Database Consulting

Configuration

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

by 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.

SQL Server TempDB: The Most Overlooked Bottleneck

by 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!

 

Mistakes IT Shops Without a DBA Make

by 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.

SQL Server Express Size Limit

by 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

| |

SQL Server Identity Skipping

by 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

IRL #3: SQL Server HealthCheck

by Leave a Comment

IRL #3 – Slow Server

Problem:

I was recently approached by a firm outside the US for some help with his SQL Server which was “running slow.”

Background:

They don’t have a DBA, and I didn’t support international customers at the time so I told him I would donate 30 minutes of my time out of pure curiosity.

Action Taken:

I sent my HealthCheck tool along with instructions to run it once the server has been up at least 7 days.

Results:

After reviewing the gathered info for 30 minutes, these are the results I sent back as recommendations to look into:

  • Backups and Data on the same drive
  • Other databases never backed up
  • No CheckDB since 2011, if ever
  • Max Server Memory: 24,000 MB of 32GB installed
    • Memory used –3.4GB
  • Windows Server 2008
  • Auto-Shrink enabled on prod databases
  • No alerts when bad things happen
  • No Operators
  • SQL Agent offline
  • Balanced Power Plan
  • Priority Boost enabled
  • TempDB on C drive, only 1 data file
  • 10 years of backup history
  • PLE – 292 seconds
  • 32-bit SQL Server 2014 (unsupported build)
  • I didn’t even get back any index information, as those queries timed out.

Solution:

  • Immediate:
    • Upgrade to 64-bit O/S (assuming 32 bit here) and SQL Server
    • Patch to supported build
    • Turn off Priority boost
    • Set up proper alerts and maintenance
    • High performance power plan
  • Next
    • Address everything else

Lessons:

Start at the outside and work your way in.  The SQL version can be the biggest issue.  Start at Hardware and O/S, then Instance settings, then indexing, then queries (in most cases).

All of this took less than 30 minutes to find.  I can do similar (but much more thorough) work for you.

 

Other posts in the IRL – In Real Life series:

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

Thanks for reading!

Kevin3NF

Home Blog About Privacy Policy