Index usage statistics (all databases)

Job details

Name:	Index usage statistics (all databases)
Platform:	Sqlserver
Category:	Performance
Description:	This procedure collects statistics from sys.dm_db_index_usage_stats performance view which gives information on how an index (or a table – heap) has been used to resolve queries.
Long description:	The procedure checks objects for all databases.
Version:	1.92
Default schedule:	12 1,4,7,10,13,16,19,22 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name	Default value	Description
max objects	10	The maximum number of objects (indexes, tables) which will be inserted into the history table. Only objects with highest values of user_seeks, user_scans and user_lookups are inserted.
history threshold	7	The number of days the statistics (user_seeks, user_scans and user_lookups) will be stored in the history table.
min size	1024	Minimum size (in KB) an index (or a table – heap) must have to be analyzed for usage to resolve queries.
collect record count stat	NO	If “YES“, the performance view sys.dm_db_index_physical_stats is queried in DETAILED mode (to collect information from the record_count column).
max elap time	5	The maximum number of minutes before the dbWatch task stops checking the remaining statistics.
exclude databases		List of databases (separated by comma) which will be excluded. You can use % (percent sign) to represent wild card characters.
include databases	%	List of databases (separated by comma) which will be included (if %, all databases will be included). You can use % (percent sign) to represent wild card characters. This parameter works in combination with “exclude databases” parameter.
max objects per graph	5	The maximum number of objects visualized in the report graphs.

Job Summary

Purpose: The purpose of this job is to collect and analyze index usage statistics across all databases on a SQL Server instance, focusing on index operational metrics like seeks, scans, lookups, and updates.
Why: This job is important to better understand the index usage patterns which can help in optimizing the indexes by:
- Identifying unused or rarely used indexes that might be adding overhead during database write operations.
- Detecting frequently scanned indexes which could be candidates for optimization to improve query performance.
Manual checking: You can check the index usage statistics manually by querying the “sys.dm_db_index_usage_stats” and “sys.dm_db_index_physical_stats” views:

SELECT * FROM sys.dm_db_index_usage_stats

SELECT * FROM sys.dm_db_index_physical_stats WHERE database_id = DB_ID() AND object_id = OBJECT_ID('yourTableName')

Tables and Procedures Created

“INDEX_USAGE_TAB“
“INDEX_USAGE_TAB_HISTR“
“INDEX_USAGE_CHECK_OBJ“
“INDEX_USAGE_proc_exec_histr”
“INDEX_USAGE_db_order_to_check”
“INDEX_USAGE_TAB_RANK“
Procedure: “proc_index_usage_stat”

Key Data Collected

The job collects the following data for each index in each database:

User scans
User seeks
User lookups
User updates
Object size
Row count

This data is collected and then aggregated to understand the overall usage and effectiveness of each index.

Job Execution Details

The procedure “proc_index_usage_stat” loops through all databases, examining each index.
For each index, it checks if it meets certain size and row count thresholds before including it in the final analysis to ensure only significant indexes are considered.
Data is periodically cleaned from “INDEX_USAGE_TAB“ and “INDEX_USAGE_TAB_HISTR“ to keep the dataset manageable and up to date.
Execution history is tracked in the “INDEX_USAGE_proc_exec_histr” table, which logs details on the number of databases and objects checked, the number of objects collected, execution time, etc.

Usage of Collected Data

Data visualization tools are used to plot the collected data, allowing DBAs to quickly see trends and outliers.
Reports generated from this data can help in making decisions about index maintenance – e.g., removing unused indexes, optimizing heavily used indexes, etc.

Importance of This Job

By automating the collection of index usage statistics, this job provides valuable insights into the operational characteristics of database indexes, helping maintain optimal performance of SQL Server databases. It reduces manual work and potential for human error in periodically reviewing index effectiveness and ensures database tuning efforts are data-driven.