Understanding how Indexes improve query performance

Subha Ganapathi
Apr 19 · 3 min read

Some pre-requisites before starting this hands-on are -

  1. To download and install SQL Server 2019 Developer edition.
  2. Load the educational database ‘WideWorldImporters’ from https://docs.microsoft.com/en-us/sql/samples/wide-world-importers-dw-install-configure?view=sql-server-ver15 into the installed SQL Server developer edition.

Let’s get started.

Step 1:

Log in to SSMS and enable the ‘Include Actual Execution Plan’ option.

Step 2:

Connect to the ‘WideWorldImporters’ database.

Step 3:

Right-click each of the tables — Application.Cities and Application.StateProvinces and see if they have indexes implemented on them. If they have indexes already, you will need to make a copy of the tables.

Note that when copies of a table are done, the corresponding indexes do not get copied. Hence, the resulting copies do not have indexes applied to them.

Step 4:

Execute the following query –

Navigate to the ‘Execution Tab’ tab in the Results pane (shown below) –

As you can see above, this is a table scan.

Now, click on each table scan block to see the performance statistics.

Click on the 2nd Table Scan Block –

Step 5:

1st set of Inferences

The stats for Application.Cities_copy (Table 1) shows that a Table Scan was done. The ‘Estimated number of rows to be read’ and the ‘Number of rows read’ are the same.) The same applies for Application.StateProvinces_copy (Table 2) as well.

Now, let’s do some indexing on these tables.

Applying a clustered index on Application.Cities_copy -

Applying a clustered index on Application.StateProvinces_copy –

Applying a non-clustered index on Application.StateProvinces_copy on the StateProvinceName as that is frequently looked up –

CREATE NONCLUSTERED INDEX state_province_name_idx ON Application.StateProvinces_copy (Stateprovincename)

Step 6:

Re-run the query

Check the ‘Execution Plan’ now –

Clicking the block ‘Index Seek’,

Running the second block ‘Key Lookup’ –

Running the third block ‘Clustered_Index_Scan’ –

Final Set of Inferences:

Before Indexing, the estimated operator cost was 62% (Table 2) for table ‘Application.StateProvinces_copy’.

After applying clustered indexing, the estimated operator cost has reduced to 1% (Table 4) for table ‘Application.StateProvinces_copy’.

After applying non-clustered indexing, the estimated operator cost is again 1% (Table 3).

For table Application.City_Copy, the cost was 5% (Table 1) for Table scan. The cost has increased to 65%.

In that case, make copies of the tables using ‘Select * into’ statements.

Concluding Remarks

In this hands-on exercise, we see some minor benefits due to indexing. This is because the database used for the hands-on is an educational database that has only a limited number or rows. The benefits of indexing are visibly significant in production database tables that have millions of rows being loaded into the tables from source systems that may be ERP systems or OLTP systems.

From Confusion to Clarification

Nerd For Tech
Subha Ganapathi

Written by

An avid learner and a passionate worker.

Nerd For Tech

NFT is an Educational Media House. Our mission is to bring the invaluable knowledge and experiences of experts from all over the world to the novice. To stay up to date on other topics, follow us on LinkedIn. https://www.linkedin.com/company/nerdfortech

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store