When a stored procedure is compiled for the first time, the values of the parameters supplied with the execution call are used to optimize the statements within that stored procedure.
Parameter Sniffing occurs when the first execution has atypical parameter values (comparative to the expected values).
Actually, Parameter sniffing occurs every time a procedure is compiled - issues may arise when you want to get different query plans for different parameter values...
Note: Basically, Parameter sniffing is not bad thing... Only when the query plan was set by unexpected values.
Actually, Parameter sniffing occurs every time a procedure is compiled - issues may arise when you want to get different query plans for different parameter values...
Note: Basically, Parameter sniffing is not bad thing... Only when the query plan was set by unexpected values.
Find out Parameter Sniffing
There are few options to find out Parameter Sniffing, but no one is absolute.
1. WITH RECOMPILE:
Add 'WITH RECOMPILE' to the procedure and see If the problem disappears. If yes - the problem is related to Parameter Sniffing.
2. Different query plans:
Check if there are different query plans. Use profiler or DMVs. Check if there are different database users or session settings.
3. Inconsistent execution times:
Look for procedures that have inconsistent execution times.
Example:
---------------------------------------------------------------------------
-- simple query without parameters
-- no parameters, no Parameter sniffing :-)
-- a lot of rows - index scan, few rows - index seek
DBCC freeproccache
go
SELECT * FROM dbo.SomeTable
where ColumnInt between 100000 and 100100 --return 81 rows
go
SELECT * FROM dbo.SomeTable
where ColumnInt between 100 and 100100 --return 44710 rows
go
DBCC freeproccache
go
SELECT * FROM dbo.SomeTable
where ColumnInt between 100 and 100100 --return 44710 rows
go
SELECT * FROM dbo.SomeTable
where ColumnInt between 100000 and 100100 --return 81 rows
go
---------------------------------------------------------------------------
-- execute query with parameters
-- Parameter sniffing:
-- first query return a lot of rows - index scan for the 2 queries
-- first query return few rows - index seek for the 2 queries
DBCC freeproccache
GO
EXEC sp_executesql N'SELECT * FROM dbo.SomeTable where ColumnInt between @ColumnInt and @ColumnIntMax',N'@ColumnInt INT, @ColumnIntMax INT',@ColumnInt=100000,@ColumnIntMax=100100
GO
EXEC sp_executesql N'SELECT * FROM dbo.SomeTable where ColumnInt between @ColumnInt and @ColumnIntMax',N'@ColumnInt INT, @ColumnIntMax INT',@ColumnInt=100,@ColumnIntMax=100100
GO
DBCC freeproccache
GO
EXEC sp_executesql N'SELECT * FROM dbo.SomeTable where ColumnInt between @ColumnInt and @ColumnIntMax',N'@ColumnInt INT, @ColumnIntMax INT',@ColumnInt=100,@ColumnIntMax=100100
GO
EXEC sp_executesql N'SELECT * FROM dbo.SomeTable where ColumnInt between @ColumnInt and @ColumnIntMax',N'@ColumnInt INT, @ColumnIntMax INT',@ColumnInt=100000,@ColumnIntMax=100100
GO
---------------------------------------------------------------------------
-- execute query with parameters
-- Parameter sniffing as above:
-- first query return a lot of rows - index scan for the 2 queries
-- first query return few rows - index seek for the 2 queries
CREATE PROCEDURE dbo.ItaiParameterSniffing
@ColumnInt INT,
@ColumnIntMax INT
as
SELECT * FROM dbo.SomeTable
where ColumnInt between @ColumnInt and @ColumnIntMax
GO
DBCC freeproccache
GO
exec dbo.ItaiParameterSniffing 100, 100100
go
exec dbo.ItaiParameterSniffing 100000, 100100
go
DBCC freeproccache
GO
exec dbo.ItaiParameterSniffing 100000, 100100
go
exec dbo.ItaiParameterSniffing 100, 100100
go
DROP PROCEDURE dbo.ItaiParameterSniffing
GO
---------------------------------------------------------------------------
No comments:
Post a Comment