Database Index Usage Statistics and Missing Index Detection
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational database systems face inefficiencies due to the lack of effective characterization and maintenance of indexes, leading to unnecessary storage overhead and performance bottlenecks, as frequently used and infrequently used indexes are not accurately assessed for their utility and maintenance needs.
Innovation Solution
A technique is implemented to generate statistics on index usage through counters that track multiple simultaneous usages, detect missing indexes, and calculate the cost of improving query plan execution by creating missing indexes, utilizing an Index Statistics Manager to manage contention and maintain accurate usage records.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If all permutations of indexes are generated with a database table, then data access speed is improved, but storage requirements and maintenance overhead increase precipitously
Solution Approach 1:
The system implements feedback mechanisms by monitoring index usage statistics and using this information to dynamically adjust which indexes are maintained. The SQL Server uses query execution statistics to identify which indexes are actually being used, and automatically drops indexes that are not being used, thereby reducing storage requirements while maintaining data access speed for frequently accessed data.
Solution Approach 2:
The system changes the parameter of index existence from a static state to a dynamic state based on usage statistics. Indexes are created, maintained, or dropped based on their usage frequency and effectiveness, allowing the database to adapt to changing access patterns while optimizing the balance between storage requirements and data access speed.
2Speed
If all permutations of indexes are generated with a database table, then data access speed is improved, but maintenance overhead increases to unacceptable levels
Solution Approach 1:
The system uses feedback from query execution statistics to automatically determine which indexes need maintenance. By monitoring which indexes are actually used in queries, the system can focus maintenance resources only on those indexes, avoiding the unacceptable overhead of maintaining all possible index permutations.
Solution Approach 2:
The database system performs self-service by automatically managing index creation and dropping based on usage statistics. This eliminates the need for manual intervention to manage index permutations, reducing maintenance overhead while still providing fast data access when appropriate indexes are available.
3Reliability
If indexes are maintained to ensure accurate data lookup, then query accuracy is improved, but the overhead of updating indexes with every data change increases
Solution Approach 1:
The system uses feedback from actual query execution patterns to determine which indexes need to be maintained with high accuracy. By analyzing which indexes are actually used in queries, the system can maintain those indexes with high fidelity while allowing less-used indexes to have lower maintenance priority, thus reducing overall maintenance overhead while preserving query accuracy for commonly executed queries.
4Stability of the object's composition
If the database structure remains static, then system stability is improved, but the system cannot adapt to changing usage patterns
Solution Approach 1:
The system introduces dynamics by allowing the index structure to change over time based on usage patterns. While the core database structure remains stable, the index configuration is dynamic, with indexes being created, maintained, or dropped automatically based on monitored usage statistics, enabling the system to adapt to changing usage patterns while maintaining overall stability.
Data Source
AI summary
A method of accumulating and aggregating statistics concerning the use of database table indexes includes using a plurality of counters to register index use. The plurality of counters allow multiple instances of index usage to be recorded simultaneously. A missing index may be detected using an optimal index in a query plan. The optimal index is compared against the available indexes to determine if an index is missing. Statistics may be gathered against the used and missing indexes.