New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Add a Monitoring Table to report Missed indexes (or INDEX MISS report) [CORE2474] #2887
Comments
Commented by: K. A. (parshua) JIRA mangles the Example table and I can not make it right! |
Modified by: K. A. (parshua)description: Database can report the fields on which if an index existed, could improve the performance of an statement. This would help us to pin point our main bottlenecks in huge and busy databases, where many different applications connect to, and through the evolution of the DB and applications, many queries are not optimized by the developers. Example: in the second row, ORDER_NO has an index, so it has not been reported. As far as I understand, this seems an easy feature to implement. => Database can report the fields on which if an index existed, could improve the performance of an statement. This would help us to pin point our main bottlenecks in huge and busy databases, where many different applications connect to, and through the evolution of the DB and applications, many queries are not optimized by the developers. Example: in the second row, ORDER_NO has an index, so it has not been reported. As far as I understand, this seems an easy feature to implement. |
Commented by: Sean Leyne (seanleyne) I have concerns about this request: 1 - Not every column specified in a WHERE clause should be indexed, the "MISS" table would contain a large amount of false reports 2 - One could argue that the second query really needed a compound index of both ORDER_NO and ORDER_DATE 3 - The analysis of index usage is really something that should be done through the evaluation of database queries issued over an extended period, to find the common search criteria and to build indexes which provide the greatest benefit. This type of longer term analysis is a SQL Trace functionality, not a monitoring task. The request seems "naive" in scope. |
Commented by: @dyemanov If some predicate is not indexed, it doesn't always imply that an index would surely improve the performance there. So the whole point of this request is somewhat questionable, IMHO, as an "index miss" does not necessarily mean a "bad thing". Also, by design, monitoring tables report the current activity, not something historical. In the real life a lot of complex statements may have non-indexed conditions, so the engine would have to keep the whole history of such statements to report them in MON$ by request. It smells like a terrible design to me. So far my conclusion is to reject this feature request. What you need would be better based on the audit / tracing facilities than on the monitoring tables. |
Commented by: K. A. (parshua) Firebird is an Enterprise Database. The people working on these kinds of databases will change in the course of time, the databases will evolve from Megabytes to Terabytes, and there is *NO* practical way to audit and optimize every single (complex, ancient) query scattered in many different applications. Even by tracing, how can I run thousands of queries and read plans in hope of finding a suspect case?! I didn't asked you to develop an AI expert system to find the best case for an index in a statement to run efficiently. I need the engine to *filter* index miss statements and let me decide how to take action. Please shift your attention from Optimizing queries and offering an effective report, to just reporting the index miss and letting the developer decide. Most of production databases are developed with small unrealistic data, and many bottlenecks which affect bigger databases escapes the initial developers. With this feature, we can inspect a *working* database and find anything that we have missed. Regarding the number of records in such a table, do whatever you do with either MON$STATEMENTS or tracing history. By the way, MS SQL 2005 has this feature, and it's worth a look. |
Commented by: @dyemanov Could you please provide the MSSQL 2005 BOL section name this feature is documented in? |
Commented by: K. A. (parshua) The following link will lead to the 2008 version; There's also a link for the 2005 version in the page. It also links to many other useful pages about the DMV feature in MS SQL Server. http://msdn.microsoft.com/en-us/library/ms345434.aspx Hope It is what you had in mind. |
Modified by: @dyemanovVersion: 3.0 Initial [ 10301 ] => |
Submitted by: K. A. (parshua)
Database can report the fields on which if an index existed, could improve the performance of an statement.
This would help us to pin point our main bottlenecks in huge and busy databases, where many different applications connect to, and through the evolution of the DB and applications, many queries are not optimized by the developers.
Example:
Select * from mon$INDEX_MISS;
RDB$TABLE_NAME / RDB$INDEX_MISS / RDB$STATEMENT
----------------------------------------------------------------------------------------------
TABLE_CUSTOMERS / F_NAME / select FLD1, FLD2 from TABLE_CUSTOMERS where F_NAME = ?
TABLE_OREDERS / ORDER_DATE / select * from TABLE_ORDERS where ORDER_NO > ? and ORDER_DATE = ?
in the second row, ORDER_NO has an index, so it has not been reported. As far as I understand, this seems an easy feature to implement.
The text was updated successfully, but these errors were encountered: