Covering Index Performance Impact in SQL Server

Covering Index Performance Impact in SQL Server

Covering Index Performance Impact in SQL Server

This post can help you to understand the “Covering Index Performance Impact in SQL Server”. First we’ll understand the covering index basics and we’ll see the performance impact using T-SQL code.

Q. What is a covering Index?

Ans:

  • A non-clustered index that is extended to include non-key columns in addition to the key columns is called as covering index.
  • Non-Key columns are not considered by the Database Engine when calculating the number of index key columns or index key size.
  • Need to use INCLUDE clause while creating index. This is mostly used to cover all columns in most frequently used queries in OLTP.
  • Covering index covers a query that means this index contains all columns which are used in SELECT, JOIN, WHERE clause
  • If you know that your application will be performing the same query over and over on the same table, consider creating a covering index on the table.
  • A covering index Without INCLUDE clause is same as composite index.

Q. Why covering index is required?

Ans:

  • Covering Index is used to remove KEY or RID Lookups. When we design a query in OLTP where the query usage frequency is very high and every time the query is issuing KEY/RID lookups then we can get benefit by creating covering index.
  • A covering index, which is a form of a composite non clustered index, includes all of the columns referenced in SELECT, JOIN, and WHERE clauses of a query.
  • Since the index is covering all columns from the query, the index contains the data you are looking for and SQL Server doesn’t have to look up the actual data in the table which reduce the logical and/or physical I/O, and boosting performance.
  • Also when we need to bypass the index limitations in number or size we can use Covering Index

Q. Since covering index improves the performance, I’ll create covering index to cover all of my queries. Does it make sense?

Ans:

Continue reading

Posted in Performance Tuning, SQL Development, SQL Server DBA | Tagged , , , , , , | 2 Comments

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:

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

FROM SALES.SalesOrderDetail

GO 5

 

View Histogram:

Continue reading

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