SQL PARAMETER SNIFFING in Tamil | Performance Tricks | SQL Developer | SQL Database Administrator

In this video, we will discuss about sql parameter sniffing in tamil. This video will be helpful for developers and dba

The sql scripts used in training is available in following section

create database test
go

CREATE TABLE Employees
(
EmpID INT identity NOT NULL ,
EmpName VARCHAR(50) NOT NULL ,
EmpAddress VARCHAR(50) NOT NULL ,
EmpDEPID int NOT NULL ,
EmpBirthDay DATETIME ,
PRIMARY KEY CLUSTERED ( EmpID )
)
GO

CREATE TABLE Employee_Department
(
DepID INT NOT NULL ,
DepName VARCHAR(50) NOT NULL ,
-- PRIMARY KEY CLUSTERED ( DepID )
)
GO

CREATE INDEX IX_Employees_EmpDEPID
ON Employees(EmpDEPID)
GO

CREATE PROCEDURE EmpPerDEP
@DepName VARCHAR(50)
AS
SELECT *
FROM [test].[dbo].[Employees] EMP
JOIN [test].[dbo].[Employee_Department] ED
ON EMP.EmpDEPID=ED.DepID
GO



DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'Sales'
GO


DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'HR'
GO




DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'HR'
GO
exec EmpPerDEP 'Sales'
GO


DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'Sales'
GO
exec EmpPerDEP 'HR'
GO

--to overcome , we recompile

ALTER PROCEDURE EmpPerDEP
@DepName VARCHAR(50)
WITH RECOMPILE
AS
begin

SELECT *
FROM [test].[dbo].[Employees] EMP
JOIN [test].[dbo].[Employee_Department] ED
ON EMP.EmpDEPID=ED.DepID
WHERE ED.DepName ='Sales'

SELECT top 100 *
FROM [test].[dbo].[Employees] EMP
JOIN [test].[dbo].[Employee_Department] ED
ON EMP.EmpDEPID=ED.DepID
WHERE ED.DepName ='Hr'
end
GO

DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'Sales'
GO
exec EmpPerDEP 'HR'
GO


ALTER PROCEDURE EmpPerDEP
@DepName VARCHAR(50)
AS
SELECT *
FROM [test].[dbo].[Employees] EMP
JOIN [test].[dbo].[Employee_Department] ED
ON EMP.EmpDEPID=ED.DepID
OPTION(RECOMPILE)
GO


ALTER PROCEDURE EmpPerDEP
@DepName VARCHAR(50)
AS
SELECT *
FROM [test].[dbo].[Employees] EMP
JOIN [test].[dbo].[Employee_Department] ED
ON EMP.EmpDEPID=ED.DepID
WHERE ED.DepName ='HR'
OPTION(OPTIMIZE FOR UNKNOWN )
GO


DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'Sales'
GO
exec EmpPerDEP 'HR'
GO



ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'HR'
GO
exec EmpPerDEP 'Sales'
GO


create database test
go

use test
go

CREATE TABLE Employees
(
EmpID INT identity NOT NULL ,
EmpName VARCHAR(50) NOT NULL ,
EmpAddress VARCHAR(50) NOT NULL ,
EmpDEPID int NOT NULL ,
EmpBirthDay DATETIME ,
PRIMARY KEY CLUSTERED ( EmpID )
)
GO

drop table employee_department
CREATE TABLE Employee_Department
(
DepID INT NOT NULL ,
DepName VARCHAR(50) NOT NULL ,
-- PRIMARY KEY CLUSTERED ( DepID )
)
GO
select count(*) from employee_department with (nolock)

CREATE INDEX IX_Employees_EmpDEPID
ON Employees(EmpDEPID)
GO

/*ALTER TABLE [dbo].[Employees] WITH CHECK ADD CONSTRAINT [FK_Employees_EmpDep] FOREIGN KEY([EmpDEPID])
REFERENCES [dbo].[Employee_Department] ([DepID])
GO

ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_EmpDep]
GO*/

CREATE PROCEDURE EmpPerDEP
@DepName VARCHAR(50)

AS
SELECT *
FROM [test].[dbo].[Employees] EMP
JOIN [test].[dbo].[Employee_Department] ED
ON EMP.EmpDEPID=ED.DepID
GO



DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'Sales'
GO


DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'HR'
GO




DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'HR'
GO
exec EmpPerDEP 'Sales'
GO


DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'Sales'
GO
exec EmpPerDEP 'HR'
GO

--to overcome , we recompile

ALTER PROCEDURE EmpPerDEP
@DepName VARCHAR(50)
WITH RECOMPILE
AS
begin

SELECT *
FROM [test].[dbo].[Employees] EMP
JOIN [test].[dbo].[Employee_Department] ED
ON EMP.EmpDEPID=ED.DepID
WHERE ED.DepName ='Sales'

SELECT top 100 *
FROM [test].[dbo].[Employees] EMP
JOIN [test].[dbo].[Employee_Department] ED
ON EMP.EmpDEPID=ED.DepID
WHERE ED.DepName ='Hr'
end
GO

DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'Sales'
GO
exec EmpPerDEP 'HR'
GO


ALTER PROCEDURE EmpPerDEP
@DepName VARCHAR(50)
AS
SELECT *
FROM [test].[dbo].[Employees] EMP
JOIN [test].[dbo].[Employee_Department] ED
ON EMP.EmpDEPID=ED.DepID
OPTION(RECOMPILE)
GO


ALTER PROCEDURE EmpPerDEP
@DepName VARCHAR(50)
AS
SELECT *
FROM [test].[dbo].[Employees] EMP
JOIN [test].[dbo].[Employee_Department] ED
ON EMP.EmpDEPID=ED.DepID
WHERE ED.DepName ='HR'
OPTION(OPTIMIZE FOR UNKNOWN )
GO


DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'Sales'
GO
exec EmpPerDEP 'HR'
GO



ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'HR'
GO
exec EmpPerDEP 'Sales'
GO