Optimizing Query Performance with Indexes in SQL Server

By - Priyansh

In this blog, we will go through how Indexes help us optimize SQL queries. 

Indexes are essential for optimizing query performance in relational databases like SQL Server. 

Type of Indexes:

1. Clustered Index: This defines the physical order of rows in the table. Sorts and stores the data rows of the table based on the index key. There can only be one clustered index per table because the data rows themselves can be sorted in only one order.

2. Non-Clustered Index: Contains a sorted list of index keys and pointers to the data rows. Multiple non-clustered indexes can be created on a single table.

3. Unique Index: Ensures that the values in the index key are unique.

4. Composite Index: An index on multiple columns.

5. Columnstore Index: It is a database storage format that organizes data by columns instead of rows, optimizing query performance and data compression for large-scale analytical queries.
   

How Indexes Optimize Query Performance?

1. Speeding Up Searches: Indexes reduce the amount of data that SQL Server needs to scan to find the rows that match a query’s criteria. Instead of scanning the entire table, SQL Server can quickly locate the index keys and retrieve the corresponding rows.

2. Efficient Joins: When joining tables, indexes on the columns used in the join conditions can significantly speed up the join operation by quickly locating the matching rows in both tables.

3. Faster Sorting: Queries with ORDER BY clauses can benefit from indexes, as the index itself is already sorted. This reduces the need for additional sorting operations.

4. Quick Access to Aggregate Functions: Aggregate functions like COUNT, SUM, AVG, etc., can be performed more efficiently with indexes, especially when used with GROUP BY clauses.

5. Reduced Disk I/O: By narrowing down the amount of data that needs to be read from the disk, indexes help reduce disk I/O operations, leading to faster query execution.

Let’s see how adding indexes improves the query performance:

Scenario 1: Before Creating indexes

Here Queries are executed without creating Indexes, it is shown statistically in the Fig 1.1, where we can see the time taken to run the query is 2917 milli seconds

Fig 1.1

Scenario 2: After creating Indexes

Here Queries are executed after creating Indexes, it is shown statistically in Fig 1.2, we can see this time with indexes it took only 1864 milliseconds, which is a 30 % reduction.

Fig 1.2

Note:

We used below queries to see CPU processing and Elapsed time in MS SQL:

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

<Your SQL QUERY >

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

Steps to create Indexes:

1. Identify Column for Indexing:

Identify the column which are frequently used by 

• WHERE clauses

• JOIN conditions

• ORDER BY clauses

• GROUP BY clauses

For the given sample query, the key columns are ‘SalesOrderID’, ‘ProductID’, ‘SpecialOfferID’, and ‘ModifiedDate’. We are creating Non-Clustered index for each of them.

Fig 2.1

2. Choose the types of Indexing:

Based on the columns and the query patterns, decide the type of index:

• Clustered Index: Typically on primary key columns.

• Non-Clustered Index: On columns used in WHERE, JOIN, ORDER BY, and GROUP BY clauses.

• Composite Index: On multiple columns if queries frequently use those columns together.
  

Create Index:

By Using ‘CREATE INDEX’ Statement, we can create an index

For e.g.

CREATE <Types_of_Indexes> <Index_Name> ON [Table_name].(Column_name);

Let’s take a reference from the sample in Fig 2.1:

• Types_of_Indexes is “Non-Clustered Index”

• Index_Name is “idx_SalesOrderDetail_SalesOrderID”

• Table_ name is “[Sales].[SalesOrderDetail]”

• Column_name is “SalesOrderID”

3. Execute the Query

4. Verify Index Creation:

Check whether the indexes are created or not, use the system catalog views:

Note :

Syntax to verify all the indexes on a table:

SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('Table_name');

5. Re-run the Query and Analyse Performance:

Re-run the original query with SET STATISTICS TIME and SET STATISTICS IO to compare performance metrics before and after creating indexes.

We have used it in our example, you can refer to the image above Fig 1.1 and Fig 1.2.