Indexes | Haygrays

Scan vs. Seek

A scan involves looking at all the data in a table, what in Oracle we would call a Full Table Scan. A seek is where you use an index to access the data.

The usual rules apply. If you are expecting to visit most of the rows in a table, a scan is more efficient than a seek. If you are expecting to interact with a small number of rows a seek is more efficient than a scan.

Table Indexes

You can check if there are any indexes on a table using the following command.

USE MyDatabase
GO

sp_helpindex 'MyTable'

If there aren’t any indexes, you are not going to be able to use them.

Displaying Execution Plans

The toolbar associated with a query tab contains the following buttons.

These buttons do the following.

Display Estimated Execution Plan : As the name suggests, this button displays the estimated execution plan for the current statement. The estimate will be based on the current statistics, so if the statistics are bad, the estimate may not match the actual execution plan.
Query Options : Allows you to alter the way the query is processed. There are lots of different options available, so a discussion of them is beyond the scope of this article.
Intellisence Enabled : This is not a performance feature. It’s just used to control whether code completion and tooltips are shown within the query window.
Include Actual Execution Plan : When highlighted, running a query will result in the display of an “Execution Plan” tab containing the actual execution plan used by the query. The text at the top of this tab may also include missing index report.
Include Client Statistics : When highlighted, running a query will produce an additional tab displaying the client statistics associated with the query.

These suggestions in the missing index report do not include any reference to existing indexes, so it may be better to and an extra column to an existing index, or alter column order, rather than creating a new index. Regardless, this can provide helpful suggestions! To use the missing index report, simply right-click on it and select the “Missing Index Details” menu option. This will produce a commented out script to create the index.

Bookmark Lookups (RID or Key)

If all the data required by the query is not present in the index, you will use the index seek to find the relevant rows in the table, then lookup each row to get the remaining data. This type of lookup is called a bookmark lookup, which can be based on a “RID Lookup” (rowid lookup) or a “Key Lookup”.

You may be able to reduce the number of bookmark lookups by adding columns into the index (a covering index), or including their data into the leaf block if they don’t form part of the lookup key. The following example created a non-clustered index on the MyTable.Name column, but include the Description and CreatedDate values in the leaf block, so you don’t need a bookmark lookup to retrieve them.

CREATE NONCLUSTERED INDEX IX_MyTable_Name  
ON dbo.MyTable(Name)  
INCLUDE (Description, CreatedDate);  
GO

You need to be careful about including too many column into the index as this will increase the overhead of index maintenance, as well as greatly increasing its size.

SET STATISTICS IO

You may find it useful to issue the following statement before issuing a query.

SET STATISTICS IO ON

This will display statistics in the “Messages” tab.

For more information see:

Index Fragmentation

Fragmentation can happen within a page (Internal) or between pages (External). SQL Server DBAs seem obsessed by rebuilding indexes.

You will probably detect and fix index fragmentation as part of your maintenance plan, but this article focuses on manually identifying and fixing index fragmentation.

Detecting Index Fragmentation

Note. Small indexes will often show large amounts of fragmentation, which might actually get worse after index rebuilds. Indexes with a page_count of less than ~1000 pages are unlikely to suffer from performance issues caused by fragmentation. Microsoft recommend avoiding rebuilds on indexes on indexes smaller than 1000 pages (here). Keep this in mind when looking at the information below.

The following query gives you the statistics for all indexes in every database. One of the columns produced is called avg_fragmentation_in_percent, which gives the information we are looking for.

SELECT *
FROM   sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL)
ORDER BY avg_fragmentation_in_percent DESC

You can narrow this down to a specific database using the first parameter. We have also returned the object name associated with the object_id.

USE MyDatabase
GO

SELECT object_name(object_id) AS object_name, *
FROM   sys.dm_db_index_physical_stats(DB_ID('MyDatabase'), NULL, NULL, NULL, NULL)
ORDER BY avg_fragmentation_in_percent DESC

We can now see which indexes are suffering from fragmentation.

Fixing Index Fragmentation

The following commands can be used to fix index fragmentation for all indexes on a specified table. A rebuild removes both internal and external fragmentation.

USE MyDatabase
GO

ALTER INDEX ALL ON dbo.MyTable
  REBUILD WITH (ONLINE = ON)

The WITH option of “ONLINE = ON” means transactions can continue against the object while the processing happens. Without it, the table would be locked for the duration of the operation.

A rebuild could result in the index being bigger as the index is rebuilt using the fill factor. If the fill factor is the default (100%), no space is left for future changes, which could result in a page split on the next insert. If you specify a fill factor, leaving some space for future updates you can reduce this fragmentation, but the index will be bigger. The necessity for a non-default fill factor depends on the nature of the work done against the table. The following example does a rebuild, specifying an 80% fill factor.

USE MyDatabase
GO

ALTER INDEX ALL ON dbo.MyTable
  REBUILD WITH (ONLINE = ON, FILLFACTOR = 80)

An alternative is to reorg the index, which shuffles the pages, only fixing external page fragmentation.

USE MyDatabase
GO

ALTER INDEX ALL ON dbo.MyTable
  REORG WITH (ONLINE = ON)