Minion.IndexPhysicalStats 



Stores the raw data from sys.dm_db_index_physical_stats. You can optionally save index size and fragmentation information to Minion.IndexPhysicalStats for use in investigating issues, as needed.  

To turn on IndexPhysicalStats logging, set the LogIndexPhysicalStats field to 1 for a database or table (in Minion.IndexSettingsDB or Minion.IndexSettingsTable, respectively). Data will be saved to Minion.IndexPhysicalStats for each index maintenance run thereafter.

WARNING: LogIndexPhysicalStats is turned off by default because it can generate large amounts of data, and the table is currently not part of the log retention cleanup process. We recommend you use this feature only as needed.



NOTE: Even if LogIndexPhysicalStats is enabled, this table will not store data for any table or database that is excluded from index maintenance, because the index process does not gather fragmentation stats for excluded tables\databases.

Name

Type

Description

ExecutionDateTime

Datetime

The execution date and time, common to the entire run of a database index maintenance event.

BatchDateTime

datetime

Date and time the index physical stats data was gathered.

IndexScanMode

varchar

Scan level that is used to obtain statistics. This is equivalent to the ‘mode’ input for sys.dm_index_physical_stats. 

DBName

Varchar

Database name. 

SchemaName

varchar

Schema name. 

TableName

varchar

Table name.

IndexName

varchar

Index name.

database_id

smallint

Database ID. See http://msdn.microsoft.com/en-us/library/ms188917.aspx

object_id

int

Object ID. See http://msdn.microsoft.com/en-us/library/ms188917.aspx

index_id

int

Index ID. See http://msdn.microsoft.com/en-us/library/ms188917.aspx

partition_number

int

Partition ID. See http://msdn.microsoft.com/en-us/library/ms188917.aspx

index_type_desc

nvarchar

Description of index type, e.g. HEAP, CLUSTERED, NONCLUSTERED, etc. 


See http://msdn.microsoft.com/en-us/library/ms188917.aspx

alloc_unit_type_desc

nvarchar

Allocation type unit, e.g. IN_ROW_DATA, LOB_DATA, ROW_OVERFLOW_DATA. *


See http://msdn.microsoft.com/en-us/library/ms188917.aspx

index_depth

Tinyint

Number of index levels. Note that 1 means the table is a HEAP. 


See http://msdn.microsoft.com/en-us/library/ms188917.aspx

index_level

tinyint

See http://msdn.microsoft.com/en-us/library/ms188917.aspx

avg_fragmentation_in_percent

float

See http://msdn.microsoft.com/en-us/library/ms188917.aspx

fragment_count

bigint

See http://msdn.microsoft.com/en-us/library/ms188917.aspx

avg_fragment_size_in_pages

float

See http://msdn.microsoft.com/en-us/library/ms188917.aspx

page_count

bigint

See http://msdn.microsoft.com/en-us/library/ms188917.aspx

avg_page_space_used_in_percent

float

See http://msdn.microsoft.com/en-us/library/ms188917.aspx

record_count

bigint

See http://msdn.microsoft.com/en-us/library/ms188917.aspx

ghost_record_count

bigint

See http://msdn.microsoft.com/en-us/library/ms188917.aspx

version_ghost_record_count

bigint

See http://msdn.microsoft.com/en-us/library/ms188917.aspx

min_record_size_in_bytes

int

See http://msdn.microsoft.com/en-us/library/ms188917.aspx

max_record_size_in_bytes

int

See http://msdn.microsoft.com/en-us/library/ms188917.aspx

avg_record_size_in_bytes

float

See http://msdn.microsoft.com/en-us/library/ms188917.aspx

forwarded_record_count

bigint

See http://msdn.microsoft.com/en-us/library/ms188917.aspx

compressed_page_count

bigint

See http://msdn.microsoft.com/en-us/library/ms188917.aspx