What is Parameter Sniffing in SQL Server | sql server performance tuning | sql server interview q&a

What is Parameter Sniffing in SQL Server? 🤔

Parameter Sniffing happens when SQL Server creates an execution plan using the first parameter value passed to a stored procedure and then reuses the same plan for future executions.

This can improve performance in some cases, but it can also cause serious slowdowns when different parameter values return very different amounts of data.

What is Parameter Sniffing?
SQL Server reuses execution plans for stored procedures.
First execution parameter influences the cached execution plan.
Later executions may reuse a bad plan for different parameter values.
This can cause slow performance, table scans, or incorrect index usage.

Why Does It Happen?
SQL Server optimizes queries based on first parameter value.
Execution plan gets cached in memory.
Different data distributions need different plans.
Reusing a single plan can hurt performance.

Symptoms?
Query suddenly becomes slow.
Sometimes fast, sometimes very slow.
High CPU or memory usage.
Index seek changes to table scan.
Execution time varies for different parameters.

How to Detect?
Compare execution times.
Check actual execution plan.
Use DMV queries.
Observe logical reads and scans.
Look for plan reuse in Query Store.
Solutions?
OPTION(RECOMPILE)
OPTIMIZE FOR UNKNOWN
Local variables

OPTION(RECOMPILE) Example?
SELECT * FROM Sales
WHERE Region = @Region
OPTION(RECOMPILE)

• SQL Server creates fresh plan each execution.
• Better performance but higher compilation cost.
OPTION(RECOMPILE) Example?
SELECT * FROM Sales
WHERE Region = @Region
OPTION(RECOMPILE)

• SQL Server creates fresh plan each execution.
• Better performance but higher compilation cost.

OPTIMIZE FOR UNKNOWN?
SELECT * FROM Sales
WHERE Region = @Region
OPTION(OPTIMIZE FOR UNKNOWN)
Uses average statistics instead of sniffed value.

Query:-

CREATE TABLE Orders (
OrderID INT IDENTITY(1,1),
Status VARCHAR(20),
Amount INT
);

INSERT INTO Orders (Status, Amount)
SELECT
'Completed',
100
FROM sys.objects a
CROSS JOIN sys.objects b;
INSERT INTO Orders (Status, Amount)
VALUES
('Cancelled', 100),
('Cancelled', 200),
('Cancelled', 300);

CREATE INDEX idx_status
ON Orders(Status);
go

UPDATE STATISTICS Orders;
go

--DROP PROC GetOrders
ALTER PROCEDURE GetOrders
@Status VARCHAR(20)
AS
BEGIN
declare @s varchar(10)=@status
SELECT *
FROM Orders
WHERE Status = @s
--option(recompile)
END
DBCC FREEPROCCACHE;

EXEC GetOrders 'Cancelled';

EXEC GetOrders 'Completed';

Видео What is Parameter Sniffing in SQL Server | sql server performance tuning | sql server interview q&a канала SS UNITECH