| 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.72 |
| Default schedule: | 12 1,4,7,10,13,16,19,22 * * |
| Requires engine install: | Yes |
| Compatibility tag: | [CDATA[.[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’) |
- About dbWatch Control Center
- Getting Started
- Product Overview
- Installing and using premium packages
- SQL Performance package
- Security and Compliance package
- Automated maintenance package
- Using Control Center
- The modules of Control Center
- Disabling and Enabling Views
- Instance management
- Domain Configuration
- Monitoring
- Tree navigation
- Instance details
- Pause
- Job details
- Job menus
- Job concepts
- Control Center Jobs
- Jobs grouped by platform
- Jobs for Oracle
- Alert log check
- Alert log check (10g)
- Alert log check (9i)
- Alert log check (8i)
- Alert log check (java)
- Alert log check (java for 10g)
- Archive status check
- Backup log check (old style 10g)
- Backup log check (old style 9i)
- Backup log check (old style 8i)
- Dataguard Archive Status
- Data Guard Archive Status Check (10g & 9i)
- Database link check
- Instance alert log
- Database uptime
- DBA jobs check
- DBMS uptime
- Export log Check (‘old style’ backups)
- File status check
- Index status check
- Invalid objects check
- Job scheduling check
- Listener log check
- Listener log check (10g)
- Listener log check (11g)
- Listener log check (9i)
- Listener log check (8i)
- Listener log check (java)
- Listener log check (java 10g)
- Listener status check
- Max datafiles check
- Database mount state
- Datafile status
- Max datafiles
- RMAN backup status (noschema)
- Password expire
- PDB status
- RMAN backup status (10.1)
- RMAN backup status (10.2)
- RMAN backup status (11)
- RMAN backup status (8)
- RMAN backup status (9)
- RMAN archivelog backup status
- RMAN backup status
- RMAN incremental backup status
- Test alert db
- Test alert
- TNSping check
- ASM disk statistics
- ASM diskgroup space
- Aud$ table size check
- Autoextensible data files
- Disk space check
- Flash Recovery Area Usage
- Flash Recovery Area Usage (10g)
- Free extents
- Max processes
- ASM diskgroup space (noschema)
- Extent fragmentation
- Flash recovery space usage (noschema)
- Max processes (noschema)
- SYS.AUD size
- Tablespace free space (noschema)
- Segment size collector (all segments -- aggregate)
- Segment size collector (all segments -- aggregate 9i)
- Segment size collector (large segments -- detail)
- Segment size status for old style tablespaces
- Temporary tablespace free space check
- Tablespace free space check
- Blocking detector for RAC
- Memory session load for RAC
- MV Refresh Group(s)
- Dataguard apply time
- Dataguard archive sequence apply lag
- Dataguard standby archivelog gap
- Dataguard standby archivelog gap
- Dataguard startup time
- Dataguard transport time
- RAC instances status
- Session load RAC
- Snapshot Log(s) rows count
- Snapshot Log(s) size
- Data Guard role switch
- Restore uptodate status
- Framework
- Analyze tables
- Delete SYS.AUD$ data
- Rebuild indexes
- Statistics migation
- Blocking detector
- Buffer cache statistics
- CPU load
- Database network statistics (SQL*NET)
- Test DML performance
- File IO statistics
- Instance memory statistics
- Latch statistics
- Long running queries
- Open cursors
- Disk read statistics
- Redo statistics
- Redo statistics (8i)
- Session load
- Session load (8i and 9i)
- SQL waits
- Table statistics check
- Top user memory usage
- Undo statistics
- User memory statistics
- Wait statistics
- SQL statistics
- SQL statistics (RDS)
- RMAN recovery area backup status
- Tablespace in BEGIN BACKUP mode
- Applied archive log gap status
- SQL statistics (CDB)
- SQL statistics (PDB)
- Agresso reports queue
- Agresso CON$ system table
- Agresso help and temp tables
- RMAN ARCHIVELOG backup status alert
- Tablespace free space (8, 9 10)
- Index status check (8i)
- Index fragmentation
- Table fragmentation (10g)
- Table fragmentation
- Jobs for MS SQL Server
- Agent Jobs Check
- Agent Jobs Check (MS2000)
- SQL Server Agent status
- Database backup (MS2000)
- Database backup (system dbs)
- Database backup
- Database backup (SIMPLE)
- Database backup (system databases)
- Database log backup
- Collation check
- Check database recovery mode
- Database status
- Database status (MS2000)
- Database server uptime
- Database Server uptime (MS2000)
- Database server uptime (MS2005)
- Instance error log
- Instance error log (AWS)
- Instance status
- Database backup (noschema)
- Database log backup (noschema)
- Database status (noschema)
- Missing database backup (noschema)
- Missing database log backup (noschema)
- Program status
- Report Server status
- Restricted Enterprise edition features
- Restricted Enterprise edition features (MS2000)
- Restricted Enterprise edition features (MS2005)
- Auto growth event collector
- Data file size check
- Data file size check (MS2000)
- Database growth rate (aggregated)
- Database growth rate (aggregated) (MS2000)
- Database growth rate (detailed)
- Database growth rate (detailed) (MS2000)
- Database disk capacity
- Database disk space usage
- Databases NOT IN USE collector
- Disk space check
- Instance error log file size check
- Filegroups growth rate
- Filegroups growth rate (MS2000)
- Data file size check (noschema)
- Database disk capacity (noschema)
- Disk space check (noschema)
- Transaction log space usage (noschema)
- Object size collector (all databases)
- Temporary database space usage
- Transaction log size check
- Transaction log size check (MS2000)
- Transaction log space usage
- Transaction log space usage (MS2000 / MS2005 / MS2008)
- Truncate transaction log
- Version store space usage (tempdb)
- Database mirroring
- Failover cluster host switch
- Health state
- Log shipping monitor (primary)
- Log shipping monitor (secondary)
- Member state
- AlwaysOn database backup alert
- AlwaysOn transaction log backup alert
- Replica states
- Replication status
- MS SQL Server patch status
- Database statistics
- dbWatch engine alert
- dbWatch Server activity alert
- Autogrow settings
- Framework
- Framework (MS2000)
- Backup All databases
- Backup All transaction logs
- Check database and server principal mapping
- Cleanup MSDB history tables
- Cycle error log
- DBCC CHECKDB
- DBCC UPDATEUSAGE
- External fragmentation (all databases)
- Internal fragmentation check
- SQL Server performance counters
- Rebuild indexes
- Rebuild indexes in table
- Reorganize indexes
- Reorganize indexes in table
- Shrink transaction logs
- Suspect pages
- Update index statistics
- Update statistics
- Statistics migation
- Blocking detector
- Blocking statistics
- Cached query statistics
- Data cache memory usage
- Data cache memory usage (MS2005 / MS2008)
- Data hit ratio
- Database session load
- Test DML-DDL performance
- File IO statistics
- High activity monitor
- High activity monitor (MS2005)
- Index usage statistics (all databases)
- Instance memory check
- Instance memory usage
- Instance memory usage (MS2005 / MS2008)
- Lazy writer and checkpoint statistics
- Memory objects statistics
- Blocking detector (noschema)
- Instance memory check (noschema)
- Server memory statistics
- Sessions per database
- Session load
- Transactions log flushed bytes load
- Transactions log flushed bytes load (MS2000)
- Transactions load
- Wait statistics
- SQL statistics
- Query store status
- Windows BUILTIN groups
- Trustworthy
- Symmetric Key encryption
- Windows LOCAL groups
- Renamed sa account
- Remote admin connections
- Remote access
- Password expiration (sysadmin)
- Scan for startup procs
- Public server role
- Orphaned database users
- Login audit setting
- Hide Instance
- Full-Text service account
- Disabled sa account
- Database Mail XPs
- Contained database credentials
- CLR Enabled
- Auto close database
- Ad Hoc Distributed Queries
- Page verify option
- AlwaysOn differential database backup alert
- Transaction log space usage (2008-2016)
- Test alert
- Database Log backup (2005-2012)
- Agent error log
- Agresso – not registered indexes
- Agresso blob size collector
- Agresso framework
- Agresso – missing system indexes
- Agresso report queue
- Agresso – missing user indexes
- Agresso help and temp tables
- Autoshrink settings
- Table size collector
- Deadlocks event collector
- Database backup (2005-2012)
- Instance Authentication Mode
- Guest database users
- Number of ERRORLOG files
- Default trace enabled
- Cross DB Ownership Chaining
- Command shell setting
- Asymmetric Key size
- Database compatibility
- AG synchronization lag
- Jobs for PostgreSQL
- Backup check – pg_dump
- Backup check – WAL
- DBMS uptime
- dbWatch engine alert
- Log size statistics
- Schema growth collector
- Vacuum alert
- Schema growth and information
- Schema growth statistics
- Tablespace growth and information
- BDR Replication lag
- Replication delay alert
- Replication slave client alert
- Backup job – pg_dump_all (ssh)
- Framework
- Analyze tables
- Vacuum tables
- Buffer cache reads per database
- Buffer cache statistics
- Disk block hitrate
- Disk block reads per database
- Test DML performance
- Index block hitrate
- Locks held and statistics
- Table and index scan collector
- Session load
- Table and index scan statistics
- Transaction statistics
- SQL statistics
- SQL statistics
- Jobs for MySQL
- DBMS uptime
- MySQL aborted connects
- MySQL max connections
- MySQL mysqld process
- Database growth rate (aggregated)
- Database growth rate (detailed)
- Innodb buffer pool check
- Innodb cluster status
- NDB data memory usage check
- NDB data node status
- Innodb cluster switch
- Replica count
- Replica delay
- Replica state
- Binlog cache check
- Key buffer check
- Database load
- Lock statistics
- Memory setup
- Network traffic
- Query cache hitrate
- Session load
- Thread cache hitrate
- Temporary table check
- Jobs for MariaDB
- DBMS uptime
- DBMS uptime (old)
- Binlog cache check
- Binlog cache check (old)
- Database growth rate (aggregated)
- Database growth rate (detailed)
- MariaDB Index Redundancy Checker
- Innodb buffer pool check
- Innodb buffer pool check (old)
- Key buffer check
- Key buffer check (old)
- Database load
- Database load (old)
- Lock statistics
- Lock statistics (old)
- Memory setup
- Memory setup (old)
- NDB data memory usage check
- NDB data node status
- Network traffic
- Network traffic (old)
- Aborted connects alert
- Maximum connections alert
- MySQLd process alert
- Query cache hitrate
- Query cache hitrate (old)
- Replica count
- Replica delay
- Replica state
- Session load
- Session load (old)
- MariaDB Space Optimizer
- Thread cache hitrate
- Thread cache hitrate (old)
- Temporary table check
- Temporary table check (old)
- Jobs for Sybase
- Blocking detector
- Database backup
- Database space check
- Database space check (12)
- Database status
- Data cache monitor
- Data cache statistics
- Database growth rate (detailed)
- DBMS uptime
- Disk activity monitor
- Engine CPU monitor
- Memory statistics
- Procedure cache check
- Procedure cache check (12)
- Session load
- System monitor collector
- Temp cache statistics
- Test DML-DDL performance
- Transaction log space check
- User connections check
- User connections check (12)
- Jobs for Oracle
- Jobs grouped by category
- Jobs grouped by platform
- Management
- Worksheet
- Autodiscover
- Reporting
- Customization
- Advanced Topics
- FDL – Farm Data Language
- Functions
- add function
- sub function
- abs function
- until function
- groupby function
- count function
- sum function
- avg function
- max function
- min function
- upper function
- lower function
- trim function
- concat function
- capitalize function
- distinct function
- mkstring function
- nicedb function
- union function
- intersect function
- difference function
- round function
- div function
- mult function
- replace function
- orderby function
- todate function
- empty function
- nonempty function
- tomillis function
- toperiod function
- containsall function
- containsnone function
- containssome function
- substring function
- hash function
- indexof function
- length function
- Available properties
- Functions
- Application Modules
- The FString format
- Product Security
- Auditing
- Control Center Commandline
- Exporting configuration from 12 to CC
- Internal Control Center firewall
- FDL – Farm Data Language
- Troubleshooting Guide
- Additional Resources
- Licensing terms
Post your comment on this topic.