Monday, May 14, 2012

Implement Database Objects: Maintain indexes

(This is part of the Study Guide series, 70-464)

Microsoft’s Measured Skill description: This objective may include but is not limited to: inspect physical characteristics of indexes and perform index maintenance; identify fragmented indexes; identify unused indexes; implement indexes; defrag/rebuild indexes; set up a maintenance strategy for indexes and statistics; optimize indexes (full, filter index); statistics (full, filter) force or fix queue; when to rebuild vs. reorganize and index; create a tuning and maintenance strategy for proactive operations

What I see:
·         physical index characteristics
·         index maintenance
·         index usage
·         create indexes
·         optimize indexes

Physical index characteristics
                Like any other storage, indexes get fragmented over time due to DML and page splits.  The way to gather this information is through the sys.dm_db_index_physical_stats DMV.  A basic query for these stats would be:

use AdventureWorks2012;

       @page_count_min bigint = 1000;

       db_name(database_id) as database_name,
       object_name(object_id) as object_name,
from sys.dm_db_index_physical_stats
where page_count >= @page_count_min
order by avg_fragmentation_in_percent desc;

                Notice the data that this query returns.  The fragmentation, as well as the page count.  It is typically advised to ignore fragmentation on indexes that have a negligible page count (i.e. not such a huge impact from the fragmentation).  I have chosen a minimum of 1000 pages to consider.

Index maintenance
                Index maintenance includes cleaning up fragmented indexes that can be retrieved from the section above.  It’s a rule of thumb that any fragmentation between 5% and 30%, and index should be reorganized.  Greater than 30% should be rebuilt.  Again, this is just a rule of thumb and not to be taken as fact in all scenarios.  If your particular maintenance window is large enough, there’s no reason not to rebuild all fragmented indexes.  Likewise, if you are running Enterprise Edition you have the ability for an ONLINE index rebuild.  There are a lot of caveats to that particular method, but it does solve some index availability issues.

Index Rebuild Example
alter index PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
on Sales.SalesOrderDetail

Index ONLINE Rebuild Example
alter index PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
on Sales.SalesOrderDetail
       online = on

Index Reorganize Example
alter index PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
on Sales.SalesOrderDetail

Index usage
                Index usage can be found through querying the sys.dm_db_index_usage_stats DMV.  This DMV contains aggregated statistics of how indexes are utilized.  If an index is not contained in this DMV, it hasn’t been utilized since the last time SQL Server started or DMV stats have been cleared.  Using this DMV with a LEFT/RIGHT OUTER JOIN with the sys.indexes catalog view will yield unused indexes.  The below query shows how to get index usage statistics:

       db_name(us.database_id) as database_name,
       object_name(us.object_id) as object_name,,
from sys.dm_db_index_usage_stats us
inner join sys.indexes i
on us.object_id = i.object_id
and us.index_id = i.index_id
where us.database_id = db_id();

Create indexes
                Creating indexes can be a very simple operation, but like many things in SQL Server there is a large list of possible options that can be specified.  A basic CREATE INDEX command is shown below, but reference BOL for the full list of options.

use MeasureMySkills;

create clustered index IX_LockedTable_Id
on LockedTable(id)

Optimize indexes
                Indexes can be used to greatly optimize code and query performance.  Likewise, too many and useless indexes can cause a serious performance degradation with INSERT/UPDATE/DELETE statements.  But given the correct indexes, they can change a table scan to an index seek minimizing IO, CPU time, and overall duration.  The sys.dm_db_missing_index_* prefixed DMVs can be used as suggestions for indexes that can help.  Like DTA, these should be taken as suggestions not necessarily hard facts.

·         BOL reference on ALTER INDEX
·         BOL reference on CREATE INDEX

Version-Controlled Code (GitHub)

If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at

No comments:

Post a Comment