Some pre-requisites before starting this hands-on are -
- To download and install SQL Server 2019 Developer edition.
- 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.
Log in to SSMS and enable the ‘Include Actual Execution Plan’ option.
Connect to the ‘WideWorldImporters’ database.
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.
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 –
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)
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.
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.