SQL Exec Stats, Copyright © 2006-2012 Elemental Inc. All rights reserved.

Read Me, Intro, Main, General, Databases, File IO, Tables, Indexes, Exec Stats, Stored Procs, Dist Stats, Appendix

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 colum 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 next four columns is user seeks, scans, lookups and updates from the index usage view. The four following columns indicate the number of execution plans that reference the index. The next four columns are the last user seek, scan, lookup and update from the index usage view.


The next eight columns are system seeks, scans, lookups, and updates, followed then by last system seek, scan, lookup and update.


The next set of columns are boolean columns from sys.indexes. The Plan Reference columns indicate the specific plans that reference each index in a specific manner.

The columns to the right indicated the number of execution plan references that include a scan, insert (or update) and delete, along with the specific plans that reference the operation.

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, u.name [Schema], o.name [Object], ISNULL(i.name, '') [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,
  CONVERT(tinyint, is_unique) is_unique,
  CONVERT(tinyint, ignore_dup_key) ignore_dup_key,
  CONVERT(tinyint, is_primary_key) is_primary_key,
  CONVERT(tinyint, is_unique_constraint) is_unique_constraint,
  CONVERT(tinyint, is_disabled) is_disabled,
  CONVERT(tinyint, is_hypothetical) is_hypothetical,
  CONVERT(tinyint, allow_row_locks) allow_row_locks,
  CONVERT(tinyint, allow_page_locks) allow_page_locks
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
LEFT JOIN (
 SELECT object_id, 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)
 FROM sys.dm_db_partition_stats WITH (NOLOCK)
 GROUP BY object_id, 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 u.name, o.name, 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