Skip to content
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

Open
firebird-automations opened this issue May 26, 2009 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: K. A. (parshua)

JIRA mangles the Example table and I can not make it right!

@firebird-automations
Copy link
Collaborator Author

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:
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.

=>

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Could you please provide the MSSQL 2005 BOL section name this feature is documented in?

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 3.0 Initial [ 10301 ] =>

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant