SQL Server Performance Tuning – Index Scan vs. Index Seek

SQL Server performance tuning has always been a tricky task. It is especially true when you're using indexes. With clustered and non-clustered indexes, you need to be careful how you use each Index. In this blog, we'll compare Index scans vs. Index seeks.

An index is a way to speed up SQL Server query performance. It is a B-tree structure defined on a table with a set of columns known as index key values. SQL Server can find out the data based on the index key effectively and speedily.

You can create the clustered or non-clustered Indeindexx in SQL Server. SQL Server might decide to do Index Scan or Index Seek for satisfying query needs.

Index Scan Vs. Index Seek in SQL Server

Let's first understand the difference between Index Scan Vs. Index Seek.

The Index scan retrieves all rows from the specified table. Therefore, it might be suitable for small tables, but retrieving all rows from that table is not optimal for SQL Server if it has a massive number of records.

The Index Seek refers only to the qualified rows and pages, i.e., it is selective in nature. Therefore, the Index seek is faster compared to Index scans.

Let's understand the Index Scan Vs. Index Seek difference using examples.

Usually, we create indexes on the where clause predicates to convert the index scans into Index seek.

For example, let's run the following query in the AdventureWorks database. It does not have a where clause. Therefore, SQL Server performs the Index scan and retrieves all data.

Set statistics IO, Time ON
     SELECT [SalesOrderID],[RevisionNumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
      ,[Status]
      ,[OnlineOrderFlag]
     ,[SalesOrderNumber]
     ,[PurchaseOrderNumber]
      ,[AccountNumber]
      ,[CustomerID]
      ,[SalesPersonID]
      ,[TerritoryID]
     ,[BillToAddressID]
     ,[ShipToAddressID]
      ,[ShipMethodID]
      ,[CreditCardID]
     ,[CreditCardApprovalCode]
       ,[SubTotal]
      ,[TaxAmt]
      ,[Freight]
      ,[TotalDue]
     FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]

If we look at the message tab, it shows that SQL Server performed 689 logical reads with an elapsed time of 1863 ms.

Suppose we want to retrieve data for a specific SalesOrderID. In this case, we can specify the condition in where clause and filter the data.

SELECT [SalesOrderID],[RevisionNumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
      ,[Status]
     ,[OnlineOrderFlag]
     ,[SalesOrderNumber]
     ,[PurchaseOrderNumber]
      ,[AccountNumber]
      ,[CustomerID]
      ,[SalesPersonID]
      ,[TerritoryID]
     ,[BillToAddressID]
     ,[ShipToAddressID]
      ,[ShipMethodID]
      ,[CreditCardID]
     ,[CreditCardApprovalCode]
       ,[SubTotal]
      ,[TaxAmt]
      ,[Freight]
      ,[TotalDue]
     FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
  where SalesOrderID=1111

SQL Server used Clustered Index seek to filter required data from the [SalesOrderHeader] table.

If an index does not exist on where clause predicate column, it might force SQL Server optimizer to use Index scan compared to Index seek. For example, in the following query, we filter records for the specific [AccountNumber] in the where clause, but it does not have an index. Therefore, optimized chose to perform an Index scan.

set statistics IO, Time ON
SELECT [SalesOrderID],[RevisionNumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
      ,[Status]
     ,[OnlineOrderFlag]
     ,[SalesOrderNumber]
     ,[PurchaseOrderNumber]
      ,[AccountNumber]
      ,[CustomerID]
      ,[SalesPersonID]
      ,[TerritoryID]
     ,[BillToAddressID]
     ,[ShipToAddressID]
      ,[ShipMethodID]
      ,[CreditCardID]
      ,[CreditCardApprovalCode]
       ,[SubTotal]
      ,[TaxAmt]
      ,[Freight]
      ,[TotalDue]
     FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
  where [AccountNumber]='1111'

The query optimizer is also reporting a missing index that might improve the query performance by 99.317%.

Let's create the missing Non-clustered Index on the [AccountNumber] column with the following script.

CREATE NONCLUSTERED INDEX [IX_Accountnumber_SalesOrderHeader]
ON [Sales].[SalesOrderHeader](AccountNumber)
Include (SalesOrderID)

Rerun the select statement, and as shown below, it converted Index Scan into the Index Seek operation.

Let's use another example where we want to filter records for the [ShipDate] column, and we already have a Non-Clustered index on it.

Create Index IX_NCShipDate ON [AdventureWorks2019].[Sales].[SalesOrderHeader] (Shipdate) Include (SalesOrderID)

Let’s run the following query and look at its actual execution plan.

set statistics IO, Time ON
SELECT [SalesOrderID],[RevisionNumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
      ,[Status]
     ,[OnlineOrderFlag]
     ,[SalesOrderNumber]
     ,[PurchaseOrderNumber]
      ,[AccountNumber]
      ,[CustomerID]
      ,[SalesPersonID]
      ,[TerritoryID]
     ,[BillToAddressID]
     ,[ShipToAddressID]
      ,[ShipMethodID]
      ,[CreditCardID]
     ,[CreditCardApprovalCode]
       ,[SubTotal]
      ,[TaxAmt]
      ,[Freight]
      ,[TotalDue]
     FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
  where Convert(Varchar(10),ShipDate,101)='09/22/2013'

We have index on [ShipDate] column but still SQL Server using clustered index scan as shown below.

The above select statement uses CONVERT() functions in the where clause. Due to the function, SQL Server has to do additional calculations while filtering the records. Therefore, it is not an optimized query plan.

The above execution plan used 689 logical reads and 276 ms elapsed time.

Let’s slightly change the select statement that changes SQL Server Index Scan into Index Seek.

set statistics IO, Time ON
SELECT [SalesOrderID]
     ,[RevisionNumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
      ,[Status]
     ,[OnlineOrderFlag]
     ,[SalesOrderNumber]
     ,[PurchaseOrderNumber]
      ,[AccountNumber]
      ,[CustomerID]
      ,[SalesPersonID]
      ,[TerritoryID]
     ,[BillToAddressID]
     ,[ShipToAddressID]
      ,[ShipMethodID]
      ,[CreditCardID]
     ,[CreditCardApprovalCode]
       ,[SubTotal]
      ,[TaxAmt]
      ,[Freight]
      ,[TotalDue]
     FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
  where ShipDate>=2011/06/12
   and ShipDate<2011/06/13

We modified the where clause predicate to give the same query output, but SQL Server was able to create an optimized execution plan that uses Index seek.

Due to a minor change in the T-SQL statement, the logical reads were reduced from 689 to 2 and elapsed time reduced to 59 ms from 276 ms.

We hope you enjoyed this post on Index scan Vs. Index seek in SQL Server performance tuning and how you can use Index seek for optimized performance. We love to learn and share knowledge, so feel free to share this post!