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

Email:deepaksubramoniam14@gmail.com

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
WHERE ED.DepName =@DepName
GO



DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'Sales'
GO

DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'HR'
GO


SELECT name, value FROM sys.database_scoped_configurations where name= 'PARAMETER_SNIFFING'


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
WHERE ED.DepName =@DepName
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
WHERE ED.DepName =@DepName
GO



DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'Sales'
GO

DBCC FREEPROCCACHE()
GO
exec EmpPerDEP 'HR'
GO


SELECT name, value FROM sys.database_scoped_configurations where name= 'PARAMETER_SNIFFING'


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
WHERE ED.DepName =@DepName
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

Видео SQL PARAMETER SNIFFING in Tamil | Performance Tricks | SQL Developer | SQL Database Administrator канала DSS Training Solutions DSS