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.
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.
Followed by columns for the time of the last user reference by nature, then system acesses, and last system access time.
The next set of columns are boolean columns from sys.indexes.
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,
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
,
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
LEFT JOIN (
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
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
There is also an option for index operational stats, more to follow.