sqlfingers.com: 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:

On the first execution, it “sniffs” the parameter values.
It compiles an execution plan optimized for those values.
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...

Data distribution
Are there whale traders or symbols that dwarf everything else? Are you mixing those in the same proc as tiny retail accounts?
Parameter sets
Test both:
- Selective cases (light traders, off-peak times).
- Worst-case flow (HFT / heavy flow accounts at market open).
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.

SQL Server Consulting

Thursday, November 13, 2025

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