Parameter Sniffing Performance Impact
in SQL Server
This post can help you in understanding the “Parameter Sniffing Performance Impact in SQL Server”. Parameter sniffing is one of the major factors that impact the performance in SQL Server. Every SQL DBA or SQL Developer should aware of the side effects of parameter sniffing. Before going to see how parameter sniffing affects the performance first we’ll quickly see what it is.
Q. What is Parameter Sniffing in SQL Server?
When a stored procedure is compiled or recompiled, SQL Server optimizer designs the execution plan by using (sniffing) the input parameters. This is known as parameter sniffing. The designed execution plan is perfectly works for the input parameters which are sent in the first go. This execution plan is stored in cache and the same plan is used when the next time the procedure is called.
Q. Why parameter sniffing is bad?
How SQL Server Optimizer Calculates the Estimated Row Count
This post can help you out in understanding “How SQL Server Optimizer Calculates the Estimated Row Count”. Once query is submitted, query optimizer takes the help from Histogram (Cardinality Estimates) statistics and builds the execution plan. Now we’ll see how histogram helps optimizer to get the estimated row count. Histogram contains 5 columns:
RANGE_HI_Key: Top data distributed values based on index or previous executed search conditions.
RANGE_Rows: When optimizer can’t find the exact match but nearby values. It captures the number of rows between the given nearby values.
EQ_ROWS: Number of rows exactly matching the search condition.
DISTINCT_Range_Rows: When optimizer can’t find the exact match but nearby values. It captures the number of distinct rows between the given nearby values.
AVG_RANGE_ROWS: When optimizer can’t find the exact match it still gets the estimated row count by using the formula: [Range_Rows]/[Distinct_Range_Rows]
Create a Table and Insert Data:
--Create a new table
CREATE TABLE Sales_Test (
OrderID INT IDENTITY(1,1) NOT NULL,
ProductID SMALLINT NOT NULL,
OrderQty SMALLINT NOT NULL,
UnitPrice MONEY NOT NULL)
--Create a clustered index on OrderID
CREATE CLUSTERED INDEX CL_IX_OrderID ON Sales_Test(OrderID);
--Create a Non-Clustered index on ProductID
CREATE NONCLUSTERED INDEX NON_CL_IX_ProductID ON Sales_Test(ProductID);
-- Insert Data from the table AdventureWorks2014.SALES.SalesOrderDetail
INSERT INTO Sales_Test
SELECT ProductID, OrderQty, UnitPrice
Implicit Conversion Performance Impact
in SQL Server
This post can help you to understand Implicit Conversion Performance Impact in SQL Server. First let’s understand the implicit conversion and then we’ll see how it impacts the performance.
Q. Why Conversion Required?
Data conversion has to be occurred whenever we need to compare data with two different datatypes. This comparison happens based on the data type precedence, lower precedence data types will always be implicitly converted up to the higher precedence type.
Q. What are the types of conversions?
There are two types of data conversions:
Explicit Conversion: When you explicitly convert data using data conversion functions CAST or CONVERT is known as explicit conversion. This conversion is clearly visible to the user.
Ex: WHERE Col1 = CAST($698.4 AS VARCHAR(10));
Implicit Conversion: SQL Server internally converts data from one data type to another. This conversion can’t be visible to the user. For example an INT type column is compared with TINYINT type column then SQL Server internally converts TINYINT to INT as INT have the higher precedence than TINYINT.
— Col1 – TINYINT and Col2 INT
EX: WHERE Col1 = Col2
Q. What is datatype Precedence?