Re: Index clustering and performance

Stefan replies:- > Land, Todd wrote: > > > > I remember a thread from long ago . . . can you refresh my memory? > > > > My database performance is declining, and my DBA wants to dump and > > reload > > the database, hoping that will help. > > > > Isn't there something I can do to my indices (re-cluster?) to regain > > performance? > > > > The OnLine Administrator's Guide (admitedly an older copy) doesn't say > > anything about perfomance except for initial tuning parameters. Help! > > (We're using OnLine 7.x) > > Hi, > > sometimes, when you have a lot of small extents for a table, it's a > good idea to unload the table, drop it and reload the table. The > same procedure will be performed internally when you create a clustured > index. But if all the tables inside a specific dbspace have a lot of > small extents it is a good idea to unload all the tables, drop the > dbspace and create it again. Afterwards you can reload all the tables, > one by one. You can avoid this by creating a well calculated first > extent for each table. If you don't have RAID 5 disks, try to detach > the indexes from the table ( create index ... in anotherdbspace ). > Monitor the growth of your tables. Compare the number of pages used > to the number of pages allocated ( after each UPDATE STATISTICS you > can select this information from systables ). Collect this information > in a separate table and store the current date. ( It's best done by > using a stored procedure ). > At least nobody can tell if two or more extents will result in a lack > of performace, but if you have only one extent it should be good enough. > > Bye > > Stefan. > > stefan@weideneder.de I think a note of caution might be advisable. All multi-user systens will suffer performance degradation from time to time. It even happens to Windows! :-) However, the solution has not always been to drop and reload tables. In one well documented case this actually resulted in yet more degradation. My personal recommendation to poeple experiencing these type of problems is to study the application carefully and try to understand the table access relationships of the main parts of the system. Look at an oncheck output and use that to decide which tables should or should not be re-loaded. But above all - check what the users are doing. I have found many cases where user activities were causing some of the performance problems. Malcolm Weallans Online Database Consultancy Phone 01628-72154 Fax 01628-37463 CIX - onlinedbc