More On Column Order Indexes – Part 2

,

In Part 1, we looked at a base query from AdventureWorks2012Big and varied column order in non-clustered indexes to determine effect on overall performance.  In this session we are going to examine how a re-definition of the clustered index may also achieve performance gains – especially when there are multiple SELECT queries having a common factor in their WHERE clause.

Consider the following report based queries from AdventureWorks2012Big (as described and modified in Part 1 of this series) with its original indexes (TSQL script attached):

F

Figure 1:  Base Report Query (from Part 1)

and

Figure 2:  Query 2

Running the first query yields this query plan:

Figure 3:  Query 1 Plan, Original Indexes

with 30,311 logical reads and cpu time of 173ms.  The Query Plan is a Clustered Index Scan on SalesOrderHeaderBig where the Primary Key and Clustered Index are defined on SalesOrderID.  Query cost is 22.9503.  Note the suggested index is

Figure 4:  Suggested Index for Query 1

Now, running the 2nd query gives us

Figure 5:  Query 2 Plan, Original Indexes

with 3655 logical reads (SalesOrderHeaderBig) and cpu = 1328ms.  Note there are two nonclustered index operators: IX_SalesOrderHeaderBig_OrderDate and IX_SalesOrderHeaderBig_CustomerID.  Interesting that there is an index seek on OrderDate and an index scan on CustomerID and that the suggested index contains both columns found in the individual index definitions.  The suggested index is

Figure 6:  Suggested Covering Index for Query 2

Both of these queries use OrderDate within the WHERE clause and the second query also used SalesOrderID for the JOIN to the Customer table and the SalesOrderDetailBig table.

If we look at the size of the indexes for Sales.SalesOrderHeaderBig we get

Schema

Table

Index

Used in KB

Reserved In KB

Rows

Sales SalesOrderHeaderBig AK_SalesOrderHeaderBig_rowguid

51,216

51,272

1,290,065

Sales SalesOrderHeaderBig AK_SalesOrderHeaderBig_SalesOrderNumber

50,272

50,312

1,290,065

Sales SalesOrderHeaderBig IX_SalesOrderHeaderBig_CustomerID

26,600

26,632

1,290,065

Sales SalesOrderHeaderBig IX_SalesOrderHeaderBig_OrderDate

21,928

23,112

1,290,065

Sales SalesOrderHeaderBig IX_SalesOrderHeaderBig_SalesPersonID

18,136

18,184

1,290,065

Sales SalesOrderHeaderBig PK_SalesOrderHeaderBig_SalesOrderID

239,080

239,240

1,290,065

Sales SalesOrderHeaderBig

Total:

407,232

408,752

1,290,065

Figure 7:  Original Index size for SalesOrderHeaderBig

Now applying the recommended indexes and looking at their sizes:

Schema

Table

Index

Used in KB

Reserved In KB

Rows

Sales SalesOrderHeaderBig AK_SalesOrderHeaderBig_rowguid

51,216

51,272

1,290,065

Sales SalesOrderHeaderBig AK_SalesOrderHeaderBig_SalesOrderNumber

50,272

50,312

1,290,065

Sales SalesOrderHeaderBig IX_SalesOrderHeaderBig_CustomerID

26,600

26,632

1,290,065

Sales SalesOrderHeaderBig IX_SalesOrderHeaderBig_OrderDate

23,168

23,944

1,290,065

Sales SalesOrderHeaderBig IX_SalesOrderHeaderBig_SalesPersonID

18,136

18,184

1,290,065

Sales SalesOrderHeaderBig IX_SalesOrderHeaderBig_Status

39,800

40,392

1,290,065

Sales SalesOrderHeaderBig PK_SalesOrderHeaderBig_SalesOrderID

239,080

239,240

1,290,065

Sales SalesOrderHeaderBig

Subtotal:

448,272

449,976

1,290,065

Figure 8:  SalesOrderHeaderBig with suggested covering indexes

IX_SalesOrderHeaderBig_OrderDate gained 2mb with the INCLUDE clause and the new index added another almost 40mb.  Now running the original 2 queries again, the first query yields

Figure 9: Query 1 using suggested covering index

with 19* logical reads and cpu = 0ms with a query cost of 0.553303.  * - Part 1 was 12 logical reads because data_compression = row was applied then.

Now if we run the second query against the recommended index we get

Figure 10: Query 2 With Suggested Covering Index

with logical reads = 346 and cpu = 640ms for SalesOrderHeaderBig and a total query cost of 80.9818.

So, it’s well known that covering indexes can really help out a single query, but sometimes it pays to look elsewhere for overall performance as well as possibly saving disk space.

The original combined Primary Key and Clustered Index was defined as

Figure 11: Original Primary Key and Clustered Index

Let’s return SalesOrderHeaderBig back to its original indexes with

Figure 12: Restore Indexes back to Original

Now, suppose we redefine the Primary Key and Clustered Index as separate entities (idea from my Austin Local User SQL Server Group (CACTUSS) presentation (https://cactuss.pass.org/?EventID=12496).  This type of definition lets you to retain the original tables Foreign Key references and also may help query performance as discussed below:

Resources

Rate

Share

Share

Rate