Posted in Admin

Performance Tuning Part -4 (Constant Scan, Compute Scalar, Parameter sniffing and Temp DB spill)

   Published Date: August 12, 2019  Leave a Comment on Performance Tuning Part -4 (Constant Scan, Compute Scalar, Parameter sniffing and Temp DB spill)

Scripts:

CREATE OR ALTER PROCEDURE Proc_SalesDetails
(@UnitPrice FLOAT,@TaxAmt FLOAT)
AS
BEGIN
Select A.SalesOrderID,A.CarrierTrackingNumber,C.TerritoryID
,B.SalesOrderNumber
FROM Sales.SalesOrderDetail A
INNER JOIN Sales.SalesOrderHeader B
ON A.SalesOrderID=B.SalesOrderID
INNER JOIN Sales.SalesPerson C
ON C.BusinessEntityID=B.SalesPersonID
Where UnitPrice<@UnitPrice
AND B.TaxAmt<@TaxAmt
END
GO
CREATE OR ALTER PROCEDURE Proc_SalesDetails
(@UnitPrice Money,@TaxAmt Money)
AS
BEGIN
Select A.SalesOrderID,A.CarrierTrackingNumber,C.TerritoryID
,B.SalesOrderNumber
FROM Sales.SalesOrderDetail A
INNER JOIN Sales.SalesOrderHeader B
ON A.SalesOrderID=B.SalesOrderID
INNER JOIN Sales.SalesPerson C
ON C.BusinessEntityID=B.SalesPersonID
Where UnitPrice<@UnitPrice
AND B.TaxAmt<@TaxAmt
END
GO

EXEC Proc_SalesDetails 2,5
GO
EXEC Proc_SalesDetails 4000,7500
GO
EXEC sp_recompile Proc_SalesDetails
GO
CREATE OR ALTER PROCEDURE Proc_SalesDetails
(@UnitPrice Money,@TaxAmt Money)
AS
BEGIN
Select A.SalesOrderID,A.CarrierTrackingNumber,C.TerritoryID
,B.SalesOrderNumber
FROM Sales.SalesOrderDetail A
INNER JOIN Sales.SalesOrderHeader B
ON A.SalesOrderID=B.SalesOrderID
INNER JOIN Sales.SalesPerson C
ON C.BusinessEntityID=B.SalesPersonID
Where UnitPrice<@UnitPrice
AND B.TaxAmt<@TaxAmt
OPTION (RECOMPILE)
END
GO
CREATE OR ALTER PROCEDURE Proc_SalesDetails
(@UnitPrice Money,@TaxAmt Money)
AS
BEGIN
Select A.SalesOrderID,A.CarrierTrackingNumber,C.TerritoryID
,B.SalesOrderNumber
FROM Sales.SalesOrderDetail A
INNER JOIN Sales.SalesOrderHeader B
ON A.SalesOrderID=B.SalesOrderID
INNER JOIN Sales.SalesPerson C
ON C.BusinessEntityID=B.SalesPersonID
Where UnitPrice<@UnitPrice
AND B.TaxAmt<@TaxAmt
OPTION (OPTIMIZE FOR UNKNOWN)
END

GO
CREATE OR ALTER PROCEDURE Proc_SalesDetails
(@UnitPrice Money,@TaxAmt Money)
AS
BEGIN
DECLARE @UnitPrice1 MONEY=@UnitPrice
DECLARE @TaxAmt1 MONEY=@TaxAmt
Select A.SalesOrderID,A.CarrierTrackingNumber,C.TerritoryID
,B.SalesOrderNumber
FROM Sales.SalesOrderDetail A
INNER JOIN Sales.SalesOrderHeader B
ON A.SalesOrderID=B.SalesOrderID
INNER JOIN Sales.SalesPerson C
ON C.BusinessEntityID=B.SalesPersonID
Where UnitPrice<@UnitPrice1
AND B.TaxAmt<@TaxAmt1
END

Post Views: 808

Author: Sauras Pandey

Sauras Pandey is an MCP, SQL Server DBA/Architect with 9+ years of experience working in a variety of environments

Leave a Reply

Your email address will not be published.