Hi,
I'm analyzing the usage of indexes using the index statistics (_IndexStat) VST. Does _IndexStat contain information for the ABL clients only, or also for the SQL-92 clients?
We're on OpenEdge 11.5.1.
- Martin
Statistics for both 4gl and SQL clients are tracked.
The most common issue is that -tablerangesize and -indexrangesize are not set adequately to cover the tables and indexes in use. By default only 50 of each are tracked. (And it is not the "top 50", the tracking is based on table id number and index id number. So that is the first 50 tables in your db and the first 50 indexes.)
Statistics for both 4gl and SQL clients are tracked.
The most common issue is that -tablerangesize and -indexrangesize are not set adequately to cover the tables and indexes in use. By default only 50 of each are tracked. (And it is not the "top 50", the tracking is based on table id number and index id number. So that is the first 50 tables in your db and the first 50 indexes.)
Ah, that's great. I have some large volatile tables (> 1.000.000.000 records, with 500.000.000 inserts per year), with indexes that I never see being used. Now that we need to do D&L, I can deactivate these indexes. Will help for idxbuild time, db size and general perf.
I'm aware of the -tablerangesize and -indexrangesize settings; as I see creates and splits on those indexes, I assume the 0 reads on the indexes really mean they are not used.
- martin
If the indexes fall within the range and the reads are 0 then, yes, they are unused and candidates for removal. However... keep in mind that some indexes may only be used occasionally. They might, perhaps, only be used for a month-end or even year-end process and thus look like they are unused because your measurement period did not cover those times.
You might also want to consider whether or not you have been keeping statistics up to date for the query optimizer. If you have not then it could that they are unused simply because the query optimizer doesn't have data suggesting that they would help.
Hi Just a little warnng On
"Now that we need to do D&L, I can deactivate these indexes. Will help for idxbuild time, db size and general perf."
If you do an Idxbuild all on your DB , it wil reactivate the deactivate index …
Exemple :
i deactivate 2 index on table item (Category2ItemName and CategoryItemName) of sports2000 .
DBANALYS :
PUB.Item
CatDescription 18 1 2 3 4.1K 34.5 2.3
Category2ItemName(inactive) 19 2 1 1 958.0B 23.5 1.0
CategoryItemName(inactive) 20 2 1 1 1.0K 26.1 1.0
ItemName 21 1 1 1 934.0B 23.0 1.0
ItemNum 17 1 1 1 503.0B 12.4 1.0
I did "proutil sports2000 -C idxbuild all"
DBANALYS :
PUB.Item
CatDescription 18 1 2 3 4.1K 34.6 2.3
Category2ItemName 19 2 1 1 958.0B 23.5 1.0
CategoryItemName 20 2 1 1 1.0K 26.1 1.0
ItemName 21 1 1 1 934.0B 23.0 1.0
ItemNum 17 1 1 1 503.0B 12.4 1.0
The deactivate index on item are "ACTIVE" ...
Patrice