Parent, SQL Exec Stats, Copyright 2006-2018 Elemental Inc. All rights reserved.
Read Me, Intro, Main, General, Databases, File IO, Tables, Indexes, ExecStats, StoredProcs, DistStats, IxOpStats, Appendix

update in progress 2015-Feb

Index Space and Usage

The Index Usage view includes index space, and index usage system view. The first set of columns include dbid, table (or view) index name, object_id, index_id, object type. The next columns are Reserved, Used and Leaf allocation in KB, followed by row count. The next colums is a code indicating the type of type index. Pk stands for Primary Key, Cl for clustered, U for unique and N for nonclustered.


The next column is for the index key columns. The number in parenthesis is the column type. There is a file type.txt in the txt directory listing the system types. Type 56 is a four byte integer, 127 is bigint, 40 is the new date only type, 61 is the original datetime, and 167 is varchar.

The second set of columns are included columns list, and then the user seeks, scans, lookups and updates from the index usage dmv.

Index 2

The third set are: five columns indicating the number of execution plans from the top dm_exec_query_stats that reference the index in a seek, scan, lookup, insert/update or delete, and five more columns indicating the numerical rank of the referencing SQL.

Index 3

Followed by columns for the time of the last user reference by nature, then system acesses, and last system access time.

Index 3

The next set of columns are boolean columns from sys.indexes.

Index 3


Index SQL

The queries for index space and usage is as follows: (needs to be updated)

--Index Space SQL:

DECLARE @dbid int SELECT @dbid = DB_ID()
SELECT @dbid dbid, [Schema], [Object], ISNULL(, '') [Index]
, i.object_id, i.index_id, o.type otype
, p.reserved, p.used, p.leaf, p.row_count
, i.type itype, data_space_id dsid, p.Partitions
, CASE CONVERT(tinyint,is_unique) WHEN 0 THEN NULL ELSE 1 END is_unique
, CASE CONVERT(tinyint,ignore_dup_key) WHEN 0 THEN NULL ELSE 1 END ignore_dup_key
, CASE CONVERT(tinyint,is_primary_key) WHEN 0 THEN NULL ELSE 1 END is_primary_key
, CASE CONVERT(tinyint,is_unique_constraint) WHEN 0 THEN NULL ELSE 1 END is_unique_constraint
, CASE CONVERT(tinyint,is_disabled) WHEN 0 THEN NULL ELSE 1 END is_disabled
, CASE CONVERT(tinyint,is_hypothetical) WHEN 0 THEN NULL ELSE 1 END is_hypothetical
, CONVERT(tinyint, allow_row_locks) allow_row_locks
, CONVERT(tinyint, allow_page_locks) allow_page_locks
, Compress = CASE compress WHEN 0 THEN NULL ELSE 1 END
, CASE CONVERT(tinyint,has_filter) WHEN 0 THEN NULL ELSE 1 END has_filter
, i.filter_definition
FROM sys.indexes i WITH (NOLOCK)
INNER JOIN sys.objects o WITH (NOLOCK) ON o.object_id = i.object_id
INNER JOIN sys.schemas u WITH (NOLOCK) ON u.schema_id = o.schema_id
 SELECT d.object_id, d.index_id, COUNT(*) AS Partitions
 , reserved = 8* SUM(reserved_page_count)
 , used = 8* SUM(used_page_count)
 , leaf = 8* SUM(in_row_data_page_count
  + lob_used_page_count + row_overflow_used_page_count)
 , row_count = SUM(row_count)
 , compress = SUM(data_compression)
 FROM sys.dm_db_partition_stats d WITH (NOLOCK)
 INNER JOIN sys.partitions q WITH (NOLOCK) ON q.partition_id = d.partition_id
 GROUP BY d.object_id, d.index_id
) p ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE o.type NOT IN ('S', 'IT','TF')
ORDER BY,, i.index_id


--Index Usage SQL:
DECLARE @dbid int SELECT @dbid = DB_ID()
SELECT s.database_id, s.object_id, s.index_id
, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
, s.last_user_seek, s.last_user_scan, s.last_user_lookup, s.last_user_update
, s.system_seeks s_seeks, s.system_scans s_scans
, s.system_lookups s_lookups, s.system_updates s_updates
, s.last_system_seek ls_seek, s.last_system_scan ls_scan
, s.last_system_lookup ls_lookup, s.last_system_update ls_update
FROM sys.dm_db_index_usage_stats s

There is also an option for index operational stats, more to follow.


Prev        Next