Dynamic Management Views and Dynamic Management Functions - Microsoft SQL Server 2012 Performance Tuning - page 153

Like DTA, DMVs also may recommend wide indexes with many INCLUDE
columns. It does not mean that you should create every index that DMVs
recommend. Practically, creating many indexes with many INCLUDE columns
also put overhead on your DML statements, such as INSERT , UPDATE , and
DELETE statements.

Then we executed a query that provides index usage statistics. It gives the number of seek
and scan operations performed on a particular index, and the time when seek or scan
operation was last performed on a particular index. To retrieve the index name and object
name, we joined the output of sys.dm_db_index_usage_stats with sys.indexes
and sys.objects catalog views respectively.

Then we retrieved the fragmentation details with the query that uses the sys.dm_db_
index_physical_stats() function. This DMF accepts the following parameters:

Parameter name Description

This parameter specifies the ID of the database for which the index
details are to be returned. If the value of this parameter is NULL , 0 ,
or DEFAULT , then the index details for all databases is returned.

database_id

object_id This parameter specifies the ID of the object for which the index

details are to be returned. If the value of this parameter is NULL ,
0 , or DEFAULT , then the index details for all objects for a given
database is returned.

This parameter specifies the ID of the index for which the index
details are to be returned. If the value of this parameter is NULL ,
0 , or DEFAULT , then the index details for all indexes for a given
object is returned.

index_id

partition_number This parameter specifies the partition number for which the index

details are to be returned. If the value of this parameter is NULL ,
0 , or DEFAULT , then the index details for all partitions for a given
index is returned.

mode This parameter specifies the mode of scan level that is used to

gather the statistics. DEFAULT , NULL , LIMITED , SAMPLED , and
DETAILED are the possible values that can be specified for this
parameter. The default is LIMITED . The LIMITED mode scans a
smaller number of pages to collect statistics. The SAMPLED mode
scans one percent of all pages. The DETAILED mode scans all
the pages and is the heavier operation.