Why Is My SQL Query Slow Only in Production? (The Parameter Sniffing Trap)

azhadsuhaimi — Mon, 10 Aug 2026 01:08:43 +0000

It’s every developer's favorite Friday afternoon nightmare:

A user complains that a feature in the app is hanging. You take the exact SQL query executed by the application, paste it into SQL Server Management Studio (SSMS), hit Execute... and it finishes in 0.02 seconds.

You run it again. Blazing fast.

Yet, inside the application, it continues to time out.

If you’ve been building database-backed apps long enough, you’ve almost certainly run into Parameter Sniffing.

What Actually Happens Under the Hood?

When a parameterized query or Stored Procedure runs for the very first time, SQL Server looks at the parameters passed in at that specific moment. It uses those values to estimate how many rows will be returned and compiles an execution plan tailored for that payload.

Scenario A: The first run passes a parameter that returns 5 rows. SQL Server creates a plan using an Index Seek. Fast and lightweight.
Scenario B: Later, another user passes a parameter that returns 500,000 rows. SQL Server reuses the cached "Index Seek" plan instead of doing an Index Scan.

Result? The server chokes trying to force a lightweight plan onto a massive dataset.

How to Catch Bad Plans in the Cache

Instead of guessing or restarting the SQL Server service (which wipes the entire cache and hides the evidence!), you can inspect the plan cache to see what parameter values were used during compilation versus execution.

Here’s a quick DMV snippet I use to find queries where the average execution duration is wildly higher than expected:

SELECT TOP 10
    qs.execution_count AS [Exec_Count],
    (qs.total_elapsed_time / 1000.0) / qs.execution_count AS [Avg_Duration_ms],
    (qs.total_worker_time / 1000.0) / qs.execution_count AS [Avg_CPU_ms],
    qs.total_logical_reads / qs.execution_count AS [Avg_Logical_Reads],
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
              WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset
          END - qs.statement_start_offset) / 2) + 1) AS [Query_Text],
    qp.query_plan AS [XML_Execution_Plan]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.execution_count > 5
ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC;
Clicking on the XML_Execution_Plan column lets you view the actual graphical execution plan directly in SSMS.

Look for the Parameter List inside the properties window—it will show you the Compiled Value vs the Runtime Value. If they look drastically different, you've found your parameter sniffing culprit.

How Do You Usually Fix Parameter Sniffing?
There are several ways to tackle this depending on the SQL Server version and business context:

Adding OPTIMIZE FOR UNKNOWN to the query.

Using local variables inside stored procedures.

Updating stale index statistics.

Using Query Store (if enabled) to force a known good plan.

How do you usually handle this in your production apps? Do you rely on query hints, or do you prefer fixing it at the database configuration level?

Drop your thoughts in the comments!

Why Do We Keep Forgetting About the Plan Cache in SQL Server?

azhadsuhaimi — Fri, 07 Aug 2026 23:26:04 +0000

Picture this: A user reports that the system is running painfully slow.

What is your immediate knee-jerk reaction?

For a long time, mine was to check table indexes, look at active locks, or blame the ORM for generating terrible SQL queries. But more often than not, the actual root cause was sitting right under my nose—inside SQL Server’s plan cache.

We talk a lot about optimizing queries during development, but we rarely talk about how much insight SQL Server passively collects for us in production while it's running.

The Unsung Hero: `sys.dm_exec_query_stats`

When SQL Server executes a query, it compiles an execution plan and caches it to save CPU time on subsequent runs. Along with that plan, it tracks execution metrics inside sys.dm_exec_query_stats.

It stores critical telemetry like:

Execution Count: Is a query slow because it’s inherently heavy, or because it’s being executed 50,000 times a minute (the classic N+1 problem)?
Worker Time: How much raw CPU time (total_worker_time) has this specific query consumed since the last service restart?
Logical Reads: Is the query thrashing memory and IO?

Instead of firing up SQL Server Profiler or Extended Events (which can add performance overhead to a live server), pulling directly from the plan cache is practically free.

The Tricky Part: Taming Large Batch Queries

If you've ever queried sys.dm_exec_query_stats and joined it with sys.dm_exec_sql_text, you’ve probably hit a common pain point: It gives you the entire batch or stored procedure text.

If a slow statement is hidden inside a 500-line stored procedure, looking at the entire text block doesn't immediately tell you which exact query ate the CPU.

To isolate the specific culprit, you have to do some offset math using statement_start_offset and statement_end_offset.

Here is a lightweight snippet I usually keep handy when I need to quickly inspect top CPU hogs without reading through endless blocks of SQL text:

SELECT TOP 10
    qs.execution_count AS [Execution_Count],
    qs.total_worker_time / 1000.0 AS [Total_CPU_ms],
    (qs.total_worker_time / 1000.0) / qs.execution_count AS [Avg_CPU_ms],
    qs.total_elapsed_time / 1000.0 AS [Total_Duration_ms],
    qs.total_logical_reads AS [Total_Logical_Reads],
    qs.creation_time AS [Plan_Cached_Since],
    DB_NAME(st.dbid) AS [Database_Name],
    OBJECT_NAME(st.objectid, st.dbid) AS [Object_Name],
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
              WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset
          END - qs.statement_start_offset) / 2) + 1) AS [Query_Text]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;

Dividing total_worker_time by 1000 converts microseconds to readable milliseconds, and the SUBSTRING logic slices out the exact offending query statement.

What's Your First Move During a Performance Issue?
I’ve been working on organizing my own lightweight, read-only diagnostic scripts lately because I got tired of rewriting these DMV queries from memory during high-pressure troubleshooting.

But I’m curious to know how other developers handle this:

Do you regularly rely on Dynamic Management Views (DMVs) for quick health checks?

Or do you prefer APM tools like Datadog, New Relic, or built-in Extended Events?

Let’s discuss in the comments below!