Consolidate MLD in sybase

[Bambina]

I am facing a big database in Sybase, and I have to redo the database model by removing the tables and stored procedures not used knowing that there is a daily treatment on the base. I heard about a tool that can allow me to see the use of statistical tables and procedures of the database or something like a house or other tools that could be launched.

Is there really a tool of this kind? Do you have another alternative to offer me?

[kelfro]

For tables, and provided that either 12.5.0.3 or later, you can use the table master .. monOpenObjectActivity (that is part of tables "MDA") to estimate the utilization rate. I stress the word "estimate" because the data in this table are were abandoning to zero when rebooting the server data, and can also be erased if the config variable "number of open objects" and "number of open indexes" are too small.

Its much more complicated. A tool like sqlbrowser can make a static analysis of all the code on the SQL server, which provides information on the dependencies between objects. Then it is possible using the master table .. monSysStatement (also a MDA table) to determine which statements are executed. Again - this use provides an estimate, and is not completely trivial to implement.

You can also use the auditing and audit all executions of stored proc.

[Bambina]

Thank you very much for your response, I'll check everything.

If I understand correctly, as the database is not restarted stats in master .. monOpenObjectActivity are correct?

I can locate "number of open objects" and "number of open indexes" to verify that their size is correct?

Regarding the auditing of stored proc, you talk of change all the procs in faisaint ensure they do an insert on a table that is used to log each proc that is executed?

[kelfro]

To check the value of "number of open ..." - Sp_monitorconfig 'all' and see the column "max used" and "num reuse.

For auditing - no - we can set up Sybase auditing (via the base sybsecurity) and then turn on the auditing of the exec stored procs (via sp_audit 'exec_procedure', 'all', 'all ',' on ').

But actually, if you have a relatively small number of procs that are suspected not to be used then we can also add logging to the same proc ...

[Bambina]

Thanks for the info. I've tested a few of your scripts in the tutorials. However I had the question: is there a query to find out when last started dataserver? Indeed I do not control what level of use of databases. Column OptSelectCount of monOpenObjectActivity gives me the number of times the index was read to the subject? So I can deduce that each use of "select" on the table must not pass the reading of the index associated?