Query Store: Showing the Impact of Indexes on Inserts

Introduction

It is common knowledge in database circles that indexes improve query performance either by satisfying the required result set entirely (Covering Indexes) or acting as lookups which easily direct the Query Engine to the exact location of the required data set. However, as experienced DBAs know, one should not be too enthusiastic about creating indexes in OLTP environments without understanding the nature of the workload. Using Query Store in SQL Server 2019 instance (Query Store was introduced in SQL Server 2016), it is quite easy to show the effect of an index on inserts.

Insert Without Index

We start by restoring the WideWorldImporters Sample database and then creating a copy of the Sales. Invoices table using the script in Listing 1. Note that the sample database has Query Store enabled in read-write mode already.

Notice that there are no indexes at all in the table we’ve just created. All we have is the table structure. Once done, we perform inserts in the new table using the data from its parent as shown in Listing 2.

During this operation, Query Store captures the execution plan of the query. Figure 1 shows briefly what is happening under the hood. Reading from left to right, we see that SQL Server executes the inserts using Plan ID 563 – an Index Scan on the source table’s Primary Key to fetch the data and then a Table Insert on the destination table. (Reading from left to right). Observe that in this case, the bulk of the cost is on the Table Insert – 99% of the query cost.

query cost

Fig. 1 Execution Plan 563

Table insert

Fig. 2 Table Insert on Destination

Clustered index scan

Fig. 3 Clustered Index Scan on Source Table

Insert With Index

We then create an index on the destination table using the DDL in Listing 3. When we repeat the statement in Listing 2 after truncating the destination table, we see a slightly different execution plan (Plan ID 593 shown in Fig 4). We still see the Table Insert but it contributes only 58% to the cost of the query. The execution dynamics are skewed a little with the introduction of a sort and an Index Insert. Essentially what is happening is that SQL Server must introduce corresponding rows on the index as new records are introduced in the table.

Execution plan

Fig. 4 Execution Plan 593

Looking Deeper

We can examine the details of both plans and see how these new factors escalate the execution time of the statement. Plan 593 adds an additional 300ms or so to the Average Duration of the statement. Under heavy workload in a production environment, this difference could be significant.

Turning STATISTICS IO on when executing the insert statement just once in both cases – with Index on destination table and without an index on Destination table – also shows that more working is done in terms of logical IO when inserting rows in a table with indexes.

Details of execution plan

Fig. 5 Details of Execution Plan 563

Details of execution plan

Fig. 4 Details of Execution Plan 593

No Index: Output with STATISTICS IO Turned on:

Table ‘Invoices1’. Scan count 0, logical reads 78372, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Invoices’. Scan count 1, logical reads 11400, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(70510 rows affected)

Index: Output with STATISTICS IO Turned on:

Table ‘Invoices1’. Scan count 0, logical reads 81119, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Invoices’. Scan count 1, logical reads 11400, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(70510 rows affected)

Additional Information

Microsoft and other sources provide scripts to examine indexes production environment and identify such situations as:

  1. Redundant Indexes – Indexes that are duplicated
  2. Missing Indexes – Indexes that could improve performance based on workload
  3. Heaps – Tables without Clustered Indexes
  4. Over-indexed Tables – Tables with more indexes than columns
  5. Index Usage – Count of seeks, scans and lookups on indexes

Items 2, 3, and 5 are more related to performance impact with respect to reads, while items 1 and 4 are related to performance impact with respect to writes. Listings 4 and 5 are two examples of these publicly available queries.

Conclusion

We have shown, using Query Store, that additional workload with an index can introduce in the execution plan of a sample insert statement. In production, excessive and redundant indexes can have a negative impact on performance, particularly in databases meant for OLTP workloads. It is important to use available scripts and tools to examine indexes and determine whether they are actually helping or hurting performance.

 

Useful tool:

dbForge Index Manager – handy SSMS add-in for analyzing the status of SQL indexes and fixing issues with index fragmentation.

Kenneth Igiri

Kenneth Igiri is a Database Administrator with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over eight years' experience with SQL Server and Oracle databases as well as related technologies. His interests include database performance, HADR, and recently, Cloud.
Also, Kenneth teaches at Children's Church and writes fiction. You can connect with Kenneth via his blog or social network account.
Kenneth Igiri

Latest posts by Kenneth Igiri (see all)


Leave a Reply

Your email address will not be published. Required fields are marked *