Recently, during a pre-conference seminar that I presented, the group and I had a long discussion about why there would be a need to add a nonclustered index that consisted of the primary key as the key column.  This topic also came up in a client training session I presented and in passing with a few peers.  Given the topic and the overall consensus that this was a bad practice, I wanted to discuss it in a post and show a reason why creating a nonclustered index with a key column only being the primary key is actually a great optimization for some plans.

I’d like to focus on what would be the largest aspect to why you would use an index strategy of creating nonclustered indexes with only the primary key as the key columns: pages pulled into the buffer.

Let’s go through an example

Using AdventureWork2012, create a table from Sales.SalesOrderHeader that can be manipulated to fit the following tuning steps.

SELECT * INTO dbo.IndexPageCount FROM Sales.SalesOrderHeader
GO
ALTER TABLE dbo.IndexPageCount 
ADD CONSTRAINT PK_SalesOrderID PRIMARY KEY (SalesOrderID) 
GO

The above statement will create dbo.IndexPageCount and make the SalesOrderID the primary key column.  At this point, no other indexing has been done.  If a query was executed that relied on a predicate of the SalesOrderID, technically, further indexing may not be needed.  For example, review the following query and execution plan generated from how the table and indexing is setup on IndexPageCount.

SET STATISTICS IO ON

SELECT 
	hdr.DueDate
	,hdr.ShipDate
	,SUM(SubTotal) AS SubTotals
FROM
dbo.IndexPageCount hdr 
WHERE hdr.SalesOrderID < 50000
GROUP BY 
	 hdr.DueDate
	,hdr.ShipDate

IO results

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

 

And the plan generated from this query

In all respects, this plan is optimized fairly well.  The one key aspect to the query and plan is, the clustered index is the table and will pull all columns along with it, which equates to more pages that are required to come into the buffer.   We can look at the page count required for the exact table by utilizing dm_os_buffer_descriptors, allocation_unit, sys.indexes and sys.partitions DMOs and catalog views.

Query referenced from: http://www.sqlteam.com/article/what-data-is-in-sql-server-memory

SELECT TOP 25 
	obj.[name],
	i.[name],
	i.[type_desc],
	count(*)AS Buffered_Page_Count ,
	count(*) * 8192 / (1024 * 1024) as Buffer_MB
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_name(object_id) AS name 
            ,index_id ,allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name   
            ,index_id, allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
WHERE database_id = db_id() AND obj.[name] = 'IndexPageCount'
GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]
ORDER BY Buffered_Page_Count DESC
```<div class="image_block">
  <a href="/wp-content/uploads/blogs/DataMgmt/-161.png?mtime=1349983070"><img alt="" src="/wp-content/uploads/blogs/DataMgmt/-161.png?mtime=1349983070" width="516" height="73" /></a>
</div>

As shown, the resulting buffered page count is 172 at this point.  This page count is where we want to optimize the query and see how the indexing strategy can assist in lowering the overall page count and buffer utilization.

**Optimizing by lowering buffered page count**

To lower the page count, we essentially need to lessen the need to pull the pages that come along with the seek operation on the clustered index.  This can be done by indexing the primary key and effectively covering the query by including in the nonkey columns, the resulting columns in the query, or better known as, a covering index.

Clear the buffer so we are sure we  look at the new index and compare the page count in the buffer to the previous results.

```sql
DBCC DROPCLEANBUFFERS

Next, run the script below to create the nonclustered index or, covering index that consists of the key column being the primary key column of SalesOrderID and INCLUDE the DueDate, ShipDate and SunTotal columns.

CREATE INDEX IDX_SalesOrderID_COVER_ASC ON dbo.IndexPageCount (SalesOrderID) INCLUDE (DueDate,ShipDate,SubTotal)

Execute the previously used query

SELECT 
	hdr.DueDate
	,hdr.ShipDate
	,SUM(SubTotal) AS SubTotals
FROM
dbo.IndexPageCount hdr 
WHERE hdr.SalesOrderID < 50000
GROUP BY 
	 hdr.DueDate
	,hdr.ShipDate

After reviewing the statistics IO from the second execution, the new covering index was utilized and showed an improvement on logical reads.

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

Looking at the execution plan, we can see the new nonclustered index is effectively being utilized as well.

Of course, we’ve only replaced an index seek for another index seek.  To see the real optimization, the IO and then equating that to review of the pages pulled into the buffer should be checked.  Execute the query to review the pages in the buffer that was used previously.

As shown, the page count in the buffer is drastically lower than the previous count of 172.  Based on this, we’ve optimized the execution of the query and plan but, more importantly, optimized the resources that are being used by the execution of the query.