T-SQL Optimization: Nonclustered Index and GROUP BY


In this example I play with the performance of a query that groups the data.

Clone database.

Create source table.

Space Used 1

Turn on the statistics and the execution plan to know what is going on under the hood.
Turn them off while testing manually the execution time.

Test query.

Turn off the statistics and the actual execution plan and run the tests multiple times to observe the execution time.

Test 1: Heap
Primary key column = No, Indexes = No.
(79433 row(s) affected)
Execution Time (sec): 7, 7, 7, 7, 7

Test 1 Execution Plan

Test 1 Statistics

Test 2: Primary Key (clustered index)
Add column ID – identity, primary key
(79433 row(s) affected)
Execution Time (sec): 7, 7, 7, 7, 7

Test 2 Execution Plan

Test 2 Statistics

Test 3: PK = Yes; Nonclustered Index = Yes
Key columns are the columns that will be used for grouping.
(79433 row(s) affected)
Execution Time (sec): 7, 7, 7, 7, 7

Test 3 Execution Plan

Test 3 Statistics

Test 4: PK = Yes; Nonclustered Indes with Included Columns = Yes
Delete the previously created index and create a new one.
Key columns are the columns that will be used for grouping.
Included columns are the ones that will be aggregated.
(79433 row(s) affected)
Execution Time (sec): 4, 3, 3, 3, 3

Test 4 Execution Plan

Test 4 Statistics

Test 5: PK = No; Nonclustered Indes with Included Columns = Yes

Delete column ID
(79433 row(s) affected)
Execution Time (sec): 3, 3, 3, 3, 3

Test 5 Execution Plan

Test 5 Statistics

Space used by the nonclustered index is The Price I Pay.

Space Used 2

Delete database.

Delete C:\Temp\Backups\PL_AdventureWorks2008R2_Data.bak

Conclusion:

  • Adding a noncluster index with key columns the grouped columns and included the aggregated columns is dividing the execution time by 2.33.
  • The primary key (Column ID) is not adding a value to the performance.
  • In this example the nonclustered index doubles the disk space used.

Keep it simple :-)

Leave a comment

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