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?
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 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;