quote:

---

Originally posted by robvolk

That seems to contradict what Ken says here:

http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

---

 

---dbo.ParameterSniffing Demo
USE Northwind

Create table dbo.ParameterSniffing ( i int identity(1,1), Gender Char(1), lastname varchar(100))
GO
-- Fill the table with lopsided data:  10000 rows of 'M', 2 rows of 'F'

Declare @i int

Set @i = 0
While @i < 10000
	Begin
		Insert into dbo.ParameterSniffing(Gender) values('M')
		Set @i = @i + 1
	end

-- Fill only 2 rows for Female:

Insert into dbo.ParameterSniffing(Gender) values('F')
Insert into dbo.ParameterSniffing(Gender) values('F')

--Create indicies on the table ParameterSniffing

Create clustered index clus_ind on dbo.ParameterSniffing(i)
Create index ind on dbo.ParameterSniffing(Gender)
GO
--Stored procedure takes one parameter and gives us output:

Create procedure dbo.SniffTest

	@Gender char(1)
As
	Select * from dbo.ParameterSniffing where Gender = @Gender
GO

-- Free Procedure cache clearing out all execution plans.
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Set statistics profile on
Set statistics time on
Set statistics io on

EXEC dbo.SniffTest 'M' --Execute procedure with parameter male('M'):  Execution plan shows clustered index SCAN
--Execute procedure with parameter male('M'):  Execution plan shows clustered index SCAN (sub-optimal for parameter F)
EXEC dbo.SniffTest 'F'
-- Free Procedure cache clearing out all execution plans.
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

EXEC dbo.SniffTest 'F' --Execute procedure with parameter male('M'):  Execution plan shows index SEEK
--Execute procedure with parameter male('M'):  Execution plan shows index SEEK (sub-optimal for parameter M)
EXEC dbo.SniffTest 'M' 


Set statistics profile off
Set statistics time off
Set statistics io off
Go

-- Clean Up
DROP Procedure dbo.SniffTest
DROP TABLE dbo.ParameterSniffing
GO