Parameter Sniffing Performance Impact in SQL Server

Parameter Sniffing Performance Impact

in SQL Server

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?

Ans:

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?

Ans:

Continue reading

Posted in Performance Tuning, SQL Development, SQL Server DBA | Tagged , , , , , | 1 Comment

How SQL Server Optimizer Calculates the Estimated Row Count

How SQL Server Optimizer Calculates the Estimated Row Count

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:

 

View Histogram:

Continue reading

Posted in Performance Tuning, SQL Development, SQL Server DBA | Tagged , , , , | 1 Comment

Implicit Conversion Performance Impact in SQL Server

Implicit Conversion Performance Impact

in SQL Server

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?

Ans:

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?

Ans:

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?

Ans:

Continue reading

Posted in Performance Tuning, SQL Development, SQL Server DBA | Tagged , , , , | Leave a comment