Performance

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!

SQL Server Wait Stats

April 16, 2025 by Kevin3NF Leave a Comment

Everything in SQL Server is waiting for something…this is by design. And most people never think about it…

Until your application is waiting to complete its request, sell the product or provide “the report” to the CEO!

Most SQL Server performance issues come down to one thing: waiting. Every time a query slows down, SQL Server records what it was waiting on. These wait stats are one of the best diagnostic tools for DBAs, sysadmins, and developers – but only if you know how to use them.

What Are Wait Stats?

SQL Server tracks the time spent waiting on resources like CPU, memory, disk, or locks. Think of them as traffic reports – some delays are normal, while others indicate serious bottlenecks. If your database is slow, wait stats tell you where to start looking.

How to Read Wait Stats

A simple query can reveal where your server is spending the most time:

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms 
FROM sys.dm_os_wait_stats 
ORDER BY wait_time_ms DESC;

Key columns:

wait_type – The specific type of wait that might be affecting performance
waiting_tasks_count – Number of times this wait has occurred
wait_time_ms – Total time spent waiting (cumulative since last clear)

Common Wait Types and What They Mean:

A table of various SQL Server Wait types and possible fixes

Many other waits exist, but these are some of the most common. Microsoft provides a full reference here.

SQL Skills has the best list of wait types and things to know I’ve ever seen. Go there and read before changing anything. MANY recorded waits are benign and not worth looking into.

Finding and Fixing Performance Issues: A guide for you to try in your development environment

Run the wait stats query to capture the current state.
Identify the top wait type and correlate it with system metrics. Research it
Apply a tuning change (indexing, parallelism adjustments, memory tuning).
Re-run the query to compare results.

This process provides a clear before-and-after snapshot of how performance improves.

The Bottom Line

Wait stats don’t tell you exactly what’s broken, but they show where to investigate. The key is to correlate them with query execution plans, server configuration, and system resource usage. Tracking these waits over time can also help spot performance trends before they become critical issues.

SQL Server is always waiting for something. The question is – what are you going to do about it? What are you waiting for?!

Thanks for reading!

–Kevin

Get the first month of Pocket DBA® FREE from Dallas DBAs!

Our SQL Server is Slow! What Do I Do First?

March 19, 2025 by Kevin3NF Leave a Comment

Don’t Panic!

It’s a vague but common complaint, frequently with no additional details. Before blindly restarting SQL Server, let’s walk through quick checks that don’t require deep DBA skills.

Step 1: Is the Server Itself Struggling?

Before blaming SQL, check system-level resources

Check:
- Is CPU pinned at 100%? SQL Server could be a victim of something else on the box
  - Task Manager is your friend for CPU…less so for memory
- Is SQL Server using 100% of the memory, and starving windows? It should be capped to maybe 90%
- Are disk latencies/usage high? Use Resource Monitor to check this
Quick Fixes:
- If CPU is the problem, find the process consuming it. SQL Server? Another app?
- If memory is an issue, see if SQL is configured to take everything (default setting is 2 PB).
- Low disk space? It won’t typically cause slow performance but it can cause errors.

Step 2: What Queries Are Running Right Now?

Use sp_whoisactive. This free tool has helped solve thousands of “slow” complaints over many years
- Look for:
  - Long-running queries
  - Blocked Processes
  - High CPU usage queries
  - Locking issues
SQL Server has a bunch of reports at the instance level, as well as a Performance Dashboard:

Quick Fixes:

If a query has been running for hours and shouldn’t be, investigate before killing it.

If blocking is an issue, find the lead blocker. It’s frequently the real problem. There are “lead blocker” scripts all over the internet

I don’t recall where I got this one, but it works:

SELECT
    spid
    ,sp.STATUS
    ,loginame   = SUBSTRING(loginame, 1, 12)
    ,hostname   = SUBSTRING(hostname, 1, 12)
    ,blk        = CONVERT(CHAR(3), blocked)
    ,open_tran
    ,dbname     = SUBSTRING(DB_NAME(sp.dbid),1,10)
    ,cmd
    ,waittype
    ,waittime
    ,last_batch
    ,SQLStatement       =
        SUBSTRING
        (
            qt.text,
            er.statement_start_offset/2,
            (CASE WHEN er.statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                ELSE er.statement_end_offset
                END - er.statement_start_offset)/2
        )
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
    ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0

Step 3: Are There Index Problems?

SQL Server needs good indexing. Missing or fragmented indexes = bad performance.
- Check for missing indexes:
  - SELECT * FROM sys.dm_db_missing_index_details
- Check for fragmentation:
  - SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘LIMITED’)
    - If you are on SSDs, this is very unlikely to be the issue.
Quick Fixes:
- If missing indexes show up, don’t blindly create them—validate first.
- Rebuilding/reorganizing indexes might help, but don’t do it blindly on huge tables.

Step 4: Is TempDB a Bottleneck?

If TempDB is under stress, everything suffers.
Check for open/hung transactions:
DBCC OPENTRAN
- Long-running transactions can fill up tempdb and transaction log files.
Quick Fixes:
- Verify you have proper number of TempDB data files (this is a specific fix for allocation performance issues).
- Restarting SQL clears TempDB, but that’s a temporary fix.

Step 5: Query Store

If Query Store is enabled, use it to find consistently bad queries.
Look for:
- High resource-consuming queries
- Execution plans that suddenly changed
Quick Fix:
- For unstable plans, forcing a known good plan might be a short-term fix.

The Bottom Line: Don’t Panic! Measure First

Gather stats before you restart – outside of Query Store, SQL Server logs almost nothing to help you troubleshoot.
Most slowdowns have a root cause rather than “just SQL being slow.”
Use these steps to find where the problem is before making changes.
If these checks don’t point to a clear fix? That’s when you bring in a DBA.

SQL Server Performance – What IT Leaders Need to Know

March 13, 2025 by Kevin3NF Leave a Comment

IT leaders have a lot on their plates! Budgets, staffing, security, uptime, and keeping everything running smoothly.

When SQL Server performance becomes an issue, a common reaction is often to throw money (or Google) at the problem: more CPUs, bigger servers, or Enterprise licensing. But before you go visit the CFO, let’s review some things that really impact SQL Server performance.

More CPUs Might Not Be the Solution

Adding more CPUs sounds like an easy fix, but SQL Server performance issues are often tied to blocking, inefficient indexing, or poor query design rather than CPU bottlenecks. SQL Server licensing costs are per core, so scaling up can drain your budget without improving performance. A SQL Server health check can pinpoint the real bottlenecks before you commit to more hardware. Your DBA team (staff or outside contractors) should know how to quickly identify what is causing the issue as it happens and over time. A CPU bump in a cloud environment as a stop-gap is a valid step to consider, but let the DBA gather the SQL stats first. Restarts wipe out most of the performance meta-data.

Enterprise Licenses Might Not Be the Solution

Enterprise Edition unlocks powerful features and more CPU/memory capacity but if your server is struggling, the root cause may not be the edition. More often than not it’s the workload or configuration. Before upgrading, assess indexing strategies, query optimization, and memory settings. Standard Edition can handle a lot if configured properly, and sometimes the right tuning eliminates the need for an expensive upgrade. $7500 per Enterprise core is a costly guess.

Monitoring SQL Server Is Different from Monitoring Other Servers

Traditional server monitoring focuses on CPU, RAM, and disk usage, but SQL Server needs specialized monitoring. Wait stats, deadlocks, blocking, missing indexes, etc. are all potential indicators of SQL Server health. General-purpose server monitoring tools miss some of these details, leading to wasted troubleshooting time. SQL-specific monitoring is critical. Most non-SQL Server folks don’t realize that 90%+ memory utilization is common and expected for a SQL Server. 100% by the SQL Server process is probably a configuration item.

RPO and RTO Goals Impact Performance

Your Recovery Point Objective (RPO) and Recovery Time Objective (RTO) dictate how quickly and accurately you can restore your data. Frequent transaction log backups improve RPO but can add overhead and can reduce Virtual Log File count. A tight RTO might require high-availability solutions like Always-On Availability Groups, which introduce additional performance considerations. Performance and recoverability must be balanced, not treated as separate issues.

Virtualization Adds Complexity

SQL Server generally runs well in a virtualized environment, but it requires tuning. Overcommitted CPU resources, shared storage contention, and improper memory allocation are common performance killers. Your DBA (or consultant) should be involved in virtualization decisions to avoid costly mistakes. Does your virtualization layer offer multiple paths to the storage (virtual controllers)? Are you taking advantage of that?

Indexing Strategies Matter More Than You Think

Poor indexing is one of the biggest performance killers. Without the right indexes, SQL Server scans entire tables instead of seeking just the rows it needs—slowing everything down. Worse, adding too many indexes can cause excessive writes and degrade performance. Finding the right balance is key. Index tuning is both art and science, and should be reviewed periodically as workload changes.

Maintenance Plans Aren’t Just a Checkbox

SQL Server needs regular maintenance: index rebuilds (it depends), statistics updates (it does not depend), and proper backup strategies. Many IT teams assume that setting up a default maintenance plan is enough. It’s not. A neglected database will slow down over time, impacting performance in ways that hardware upgrades won’t fix.

The Bottom Line

SQL Server performance resolution isn’t just about throwing more resources at the problem. Tt’s about understanding how SQL Server works. If your team doesn’t have a dedicated DBA, getting an expert to assess your setup can save you a lot of time, money, and frustration.

Get a free Health Check for your most important SQL Server today from Dallas DBAs.

3rd Party Applications Have Issues

May 2, 2024 by Kevin3NF Leave a Comment

As a SQL DBA, what do you do when a vendor application has performance problems that are code related?

Server settings don’t generally seem to be an issue.

Queries and vendor code…total hands off. I just point at code and say “There’s a great choice for optimizing in your next update!”

Indexes are the “Sticky Bits” in between client data and vendor code.

Seems like we get 30 indexes, or zero. Neither is usually right.

I get it…how do you tune an app for every possible use case for any client that may install it?

I always have my client get the official rules from the vendor. We don’t want to break an upgrade, or void a support agreement.

In some cases we’ve had to “Don’t Ask, Don’t Tell” our way into a solution with undo/redo scripts for very necessary indexes.

What do you do when tuning a 3rd party app?

— Kevin

Set-Based vs Row-Based Database Code

December 1, 2023 by Kevin3NF Leave a Comment

In SQL Server, the terms “set-based” and “row-based” refer to different approaches or styles of writing SQL code to manipulate data. These styles have implications for performance, readability, and the way queries are processed. Let’s explore the differences between set-based and row-based code:

Set-Based Code:
- Operations on Sets: Set-based code focuses on performing operations on entire sets of data rather than individual rows. This is aligned with the relational nature of databases, where data is stored in tables.
- Declarative: Set-based code is often more declarative, meaning you specify what you want to achieve without explicitly instructing how to do it. SQL is designed to work with sets of data, and set-based operations leverage this design.
- Performance: Set-based operations are generally more efficient because they allow the database engine to optimize the query execution plan. The database engine can decide the most efficient way to retrieve and process the data.
Row-Based Code:
- Operations on Individual Rows: Row-based code, on the other hand, involves operations that are performed on individual rows one at a time. This style of coding is more procedural and might resemble how you would approach data manipulation in a programming language like a procedural language (e.g., C# or Java).
- Imperative: Row-based code is often more imperative, specifying the step-by-step instructions to achieve a particular result. It may involve using cursors or loops to iterate through individual rows.
- Performance Concerns: Row-based operations can be less efficient, especially when dealing with large datasets, as they might lead to more I/O operations and additional processing overhead.

Example:

Consider an example where you want to update the prices of all products in a certain category. A set-based approach would involve a single UPDATE statement that modifies all relevant rows at once:

UPDATE Products
SET Price = Price * 1.1
WHERE CategoryID = 2;

In contrast, a row-based approach might involve using a cursor to loop through each row and update it individually, which could be less efficient:

DECLARE @ProductID INT, @Price DECIMAL;

DECLARE ProductCursor CURSOR FOR
SELECT ProductID, Price
FROM Products
WHERE CategoryID = 2;

OPEN ProductCursor;

FETCH NEXT FROM ProductCursor INTO @ProductID, @Price;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Price = @Price * 1.1;

    UPDATE Products
    SET Price = @Price
    WHERE ProductID = @ProductID;

    FETCH NEXT FROM ProductCursor INTO @ProductID, @Price;
END;

CLOSE ProductCursor;
DEALLOCATE ProductCursor;

In general, it’s recommended to use set-based operations whenever possible in SQL Server as they are usually more efficient and better aligned with the relational database paradigm. However, there may be cases where row-based operations are necessary or more practical, such as when working with procedural code or handling complex business logic that is not easily expressed in a set-based manner.

Thanks for reading!

Kevin

Follow @Dallas_DBAs