0

I recently learned about the concept of parameter sniffing and I am having a confusion about a specific scenario where I am not able to understand the behavior of SQL Server. Anybody who can help me to understand will be great:

SCENARIO:

For instance we have a below stored procedure:

create procedure GetEmployeeDetails  @a int as 
BEGIN
    IF @a = 1
    BEGIN
    select * from HumanResources.employee where BusinessEntityID = @a
    END
    IF @a = 2
    BEGIN
    select * from HumanResources.employee
    END
END

And when I execute a stored procedure like below, will parameter sniffing should happen? I already tried it is not happening but I am amazed that why it is not happening. should the exact code be running for different parameters for parameter sniffing to happen? Is it a pre-requisite?

DBCC FREEPROCCACHE;
exec GetEmployeeDetails @a = 1
exec GetEmployeeDetails @a = 2

Any inputs are appreciated. Thanks.

  • @sp_BlitzErik I am still not completely clear on why it is happening? – sachin-SQLServernewbiee May 26 '18 at 12:47
  • Can you be really specific about what you're not clear on? I have no idea how to help you any further. – Erik Darling May 26 '18 at 13:03