TempDB: The Unsung Hero Living in Your SQL Server's Basement

You know that friend who lets everyone crash at their place after parties? That's the tempdb.  It's SQL Server's communal workspace, shared playground, and sometimes... total chaos zone.

Why TempDB is Having All the Fun (And All the Problems)

Unlike your user databases with their carefully planned schemas, tempdb is like a Vegas hotel room: what happens there should stay there, everything resets on checkout (restart)... and sometimes you find some really weird stuff left behind.

Every single session on your SQL Server shares this ONE database. It's handling:

• Your temporary tables (#temp) and table variables (@table)
• Sort operations that spill from memory
• Row versioning for snapshot isolation
• Internal worktables for spools, hashing, and sorting
• DBCC CHECKDB operations
• Online index builds

The '8 Files or Bust' Rule

Here's the deal: tempdb defaults to one data file. That's like having one bathroom at a Metallica concert.  It's going to get ugly.

The golden rule: Configure one tempdb data file per CPU core, up to 8 files. Beyond 8, you're probably not gaining much unless you are in a very specific high-contention scenario.

Why? SQL Server uses a proportional fill algorithm, spreading writes across files. More files = more GAM/SGAM pages = less allocation contention.  It's beautiful when it works.

-- Are your tempdb files sized equally?
SELECT name, size/128.0 AS size_mb, growth
FROM tempdb.sys.database_files
WHERE type_desc = 'ROWS';

-- Who's hogging tempdb space RIGHT NOW?
SELECT 
    s.session_id,
    DB_NAME(r.database_id) AS database_name,
    CAST(SUM(u.user_objects_alloc_page_count) * 8.0/1024 AS DECIMAL(10,2)) AS user_objects_mb,
    CAST(SUM(u.internal_objects_alloc_page_count) * 8.0/1024 AS DECIMAL(10,2)) AS internal_objects_mb
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_db_task_space_usage u ON r.session_id = u.session_id
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE u.user_objects_alloc_page_count > 0 
   OR u.internal_objects_alloc_page_count > 0
GROUP BY s.session_id, r.database_id
ORDER BY (u.user_objects_alloc_page_count + u.internal_objects_alloc_page_count) DESC;

Pro Tips That'll Save Your Bacon

1. Size all files identically - If one file is 1GB and another is 10GB, SQL Server will hammer that smaller file trying to keep proportions.  Don't do this to yourself.

2. Set reasonable autogrowth - 64MB or 10% on a 500GB file?  Please.  Use 256MB for data and 128MB for log files.  Autogrowth events are expensive.

3. Instant file initialization is your friend - Enable it at the Windows level. Tempdb creates new files on every restart, and you don't want to wait for Windows to zero out 100GB.

4. Put it on fast storage - Tempdb is high-throughput, low-durability. SSDs are perfect.  It doesn't need to be on your expensive SAN.

Next time someone says 'the database is slow', check tempdb first:

-- What's waiting on tempdb?
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGE%LATCH%'
   OR wait_type LIKE 'PAGELATCH%'
ORDER BY wait_time_ms DESC;

If you see PAGELATCH_UP or PAGELATCH_EX waits on 2:1:1 or 2:1:3 (PFS and SGAM pages), you've got allocation contention.  Time to add more files.

The Bottom Line

TempDB is the workhorse of your SQL Server instance. Treat her right: give her more space than she needs, multiple files, fast disk, and for the love of all things holy, size those files equally.

Your future self (and your users) will thank you.  I promise.  

More to read

Brent Ozar: Tempdb Cheat Sheet: How to configure tempdb

Brent Ozar: How to Tell if You Need More Tempdb Files

MSFT: The tempdb

Same Query. Same Index. Totally Different Performance. Why?

In electronic trading, nothing is more frustrating than a query that:

• Runs in 200 ms during UAT
• Then takes 45 seconds in PROD at market open…

With the same proc, same index, and same code.

You rebuild indexes and update stats with fullscan.. but it still behaves like a moody market maker on a Monday.

Most likely not bad indexing. More likely Parameter Sniffing.

Quick Reminder: What is Parameter Sniffing?

SQL Server does something clever when you run a parameterized query:

1. On the first execution, it “sniffs” the parameter values.
2. It compiles an execution plan optimized for those values.
3. It caches and reuses that plan for future executions.

Most of the time this is a big win, but when your data distribution is skewed, that cached plan can be perfect for one set of orders and terrible for another. Think:

• One trader ID with a few intraday fills.
• Another trader ID with hundreds of of micro-lots across USD/JPY and EUR/USD.

Where Indexes Enter the Drama

Consider a simplified orders table:

CREATE TABLE dbo.Orders
(
  OrderID          bigint IDENTITY(1,1) PRIMARY KEY,
  TimeOfExecution  datetime2(3),
  TraderID         int,
  Symbol           varchar(16),
  Side             char(1),      -- 'B' = Buy, 'S' = Sell
  Qty              int,
  Price            decimal(18,4),
  Status           char(1)       -- 'O' = Open, 'F' = Filled, 'C' = Cancelled
);

CREATE INDEX idx_Orders_TimeOfExec_TraderID
    ON dbo.Orders (TimeOfExecution, TraderID);

CREATE INDEX idx_Orders_TimeOfExec_Symbol
    ON dbo.Orders (TimeOfExecution, Symbol);

And a stored procedure on a busy reporting box:

CREATE OR ALTER PROCEDURE dbo.usp_GetTradersTrades
    @TraderID int,
    @FromTime datetime2(3),
    @ToTime   datetime2(3)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderID, TraderID, Symbol, Side, TimeOfExecution, Qty, Price, Status
    FROM dbo.Orders
    WHERE TraderID        = @TraderID
      AND TimeOfExecution >= @FromTime
      AND TimeOfExecution <  @ToTime;
END;
GO

Run 1: Quiet Retail Trader

EXEC dbo.usp_GetTradersTrades
    @TraderID = 101,   -- 200 rows
    @FromTime = '2025-11-13 09:00:00',
    @ToTime   = '2025-11-13 16:00:00';

SQL Server sniffs these parameters, sees a very selective trader and creates a plan. The plan is cheap, tight, and fast -- and now it is cached.

Run 2: HFT / Flow Account from the Same Proc

EXEC dbo.usp_GetTradersTrades
    @TraderID = 9001, -- 10 thousand rows
    @FromTime = '2025-11-13 09:00:00',
    @ToTime   = '2025-11-13 16:00:00';

Now we hit an HFT account with thousands of fills across the day. The small trader plan is reused on this account --- and quickly degenerates into a slow, row-by-row grind with massive I/O.

Did the index suddenly become bad? No. Very simply, the chosen plan is wrong for these new parameters, but it’s being forced to reuse it.

Hello Parameter Sniffing.

• The 'perfect' index can amplify the gap between good and bad plans.
• You can get:
  • One plan that loves the quiet retail trader, and
  • The same plan that punishes the HFT account at the opening bell.

If your query runs fast sometimes and face-plants other times with the same index, start suspecting plans + parameters, rather than just the index.

Fast Triage in a Trading Environment

Run the proc with a good TraderID and a bad TraderID:

SET STATISTICS IO, TIME ON;

EXEC dbo.usp_GetTradersTrades @TraderID = 101,  @FromTime = ..., @ToTime = ...;  -- Good
EXEC dbo.usp_GetTradersTrades @TraderID = 9001, @FromTime = ..., @ToTime = ...;  -- Bad

If one execution wants a seek + nested loops and the other really needs a scan + hash join, you’re looking at parameter sniffing.

Force a Fresh Plan on the Bad Execution

EXEC dbo.usp_GetTradersTrades
    @TraderID = 9001,
    @FromTime = '2025-11-13 09:00:00',
    @ToTime   = '2025-11-13 16:00:00'
OPTION (RECOMPILE);

Practical Fixes That Don’t Suck

1. Targeted OPTION (RECOMPILE)

For procedures that:

• Run less frequently throughout the day (end-of-day reports, risk aggregation), or
• Are extremely sensitive to trader / symbol distribution,

You can selectively recompile:

SELECT OrderID, TraderID, Symbol, Side, TimeOfExecution, Qty, Price, Status
FROM dbo.Orders
WHERE TraderID        = @TraderID
  AND TimeOfExecution >= @FromTime
  AND TimeOfExecution <  @ToTime
OPTION (RECOMPILE);

2. 'Optimize For' the Common Case

If 90% of calls are for high-volume flow accounts, you can bias the optimizer:

SELECT ...
FROM dbo.Orders
WHERE TraderID        = @TraderID
  AND TimeOfExecution >= @FromTime
  AND TimeOfExecution <  @ToTime
OPTION (OPTIMIZE FOR (@TraderID UNKNOWN));

UNKNOWN pushes the optimizer toward an average selectivity plan instead of letting one weird trader dominate the compilation. You avoid full recompilation while still stabilizing behavior.

3. Split the Logic on Purpose

Sometimes your data model really does have two worlds:

• A small set of ultra-high-volume trader IDs.
• A very long tail of low-volume accounts.

In that case, it can be cleaner to admit that in code:

IF @TraderID IN (9001, 9002, 9003)  -- known firehose accounts
BEGIN
    SELECT ...
    FROM dbo.Orders
    WHERE TraderID        = @TraderID
      AND TimeOfExecution >= @FromTime
      AND TimeOfExecution <  @ToTime;
END
ELSE
BEGIN
    SELECT ...
    FROM dbo.Orders WITH (INDEX(idx_Orders_TimeOfExec_TraderID))
    WHERE TraderID        = @TraderID
      AND TimeOfExecution >= @FromTime
      AND TimeOfExecution <  @ToTime;
END

Not pure from a theoretical standpoint, but brutally clear when you’re on call during a volatile session and need behavior you can predict.

Index Tuning with Market Behavior in Mind

When tuning indexes for trading workloads, think beyond just the columns...

1. Data distribution
   Are there whale traders or symbols that dwarf everything else? Are you mixing those in the same proc as tiny retail accounts?
2. Parameter sets
   Test both:
   • Selective cases (light traders, off-peak times).
   • Worst-case flow (HFT / heavy flow accounts at market open).
3. Plan stability
   If the plan flips between instant and agonizing with no code changes -- you’re not done tuning yet.

The One-Line Takeaway

When performance flips between instant and miserable on the same proc, in the same trading system, your index probably isn’t lying to you – your cached plan is.

Keep tuning your indexes, but be aware of Parameter Sniffing. In electronic trading, indexes and parameter sniffing are a package deal. Ignore one, and the other will eventually creep up on you.

More to read

• Brent Ozar: Parameter Sniffing in Action
• Microsoft: Handling Parameter Sniffing

SQL Server 2025 RC1 — The Calm Before a Very Big Shift

Microsoft quietly dropped the first Release Candidate for SQL Server 2025, and if you blinked, you might have missed one of the most important signals for the data platform’s future.

The official announcement calls out acceleration, but buried inside the noise is something deeper: the database engine itself is evolving faster than our operational playbooks. The 2025 build isn’t just another compatibility bump -- it’s a re-tuning of how SQL Server thinks, plans, and executes.

Highlights That Should Make Every DBA Pay Attention

• Adaptive Compilation Pipeline: The optimizer now learns from workload patterns dynamically. Parameter sniffing workarounds and hint gymnastics might finally have an expiration date.
• Vector Indexing and JSON/Regex Enhancements: Microsoft is embedding AI-friendly data structures directly into the core engine. On-prem databases will now play in the same field as cloud analytics. This is very interesting.
• Availability and Failover Resilience: Improvements in synchronization and resource isolation hint that AGs and FCI deployments could see real-world performance gains — less lag, faster recovery, and fewer “mystery” disconnects. Wow.
• Performance Isolation: Query Store now interacts with workload groups more intelligently, minimizing interference between heavy maintenance tasks and user queries.

What It Means for Practicing DBAs

If your environment depends on custom job synchronization, failover pipelines, or tightly-tuned Agent workflows, this release is a perfect dress rehearsal. Spin up a sandbox, restore a copy of your critical AG, and test your procedures under the RC engine. Look for differences in plan stability, job duration, and any change in background I/O or tempdb contention. You’ll want to capture these deltas before production catches up to you.

The truth is, this release isn’t about shiny features — it’s about a shifting baseline. Your known good queries, jobs, and maintenance scripts may still work, but they will work differently. The earlier you test, the less surprise you’ll have when the inevitable patch train arrives.

Bottom Line

SQL Server 2025 RC1 feels like the moment right before the storm — calm, stable, almost routine — until you realize how much has changed underneath. Those who prepare now will glide into the next cycle. Those who don’t will find themselves debugging “nothing changed” problems all over again.

If you live and breathe uptime, replication health, and Agent discipline — this is your early warning. Test the RC, measure, document, and adapt. Because when the final GA lands, you’ll want your environment to just work.