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

SQL Server Update Statistics Performance Impact

SQL Server Update Statistics Performance Impact

SQL Server Update Statistics Performance Impact

The post “SQL Server Update Statistics Performance Impact” shows some interesting points on how statistics impact the query performance in SQL Server. Now before look into the impact we’ll quickly go through the essential basics:

Index Statistics:

Index statistics contain information about the distribution of index key values. By distribution, I mean the number of rows associated with each key value. SQL Server uses this information to determine what kind of execution plan to use when processing a query.

Types of Statistics:

  • Statistics created by optimizer.
  • Statistics created due to index creation.
  • User defined statistics created from “CREATE STATISTICS”
  • Query statistics generated from SET options. i.e SET STATISTICS IO / TIME ON / OFF;

Continue reading

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