Re: Indexes and Stored Commands/Procedures

Sammers, It is always good to re-index periodically, because that will remove all the holes in the pages and also update the statistics page(clustered index only). In some cases the holes in the pages could also aid in good performance because the server does'nt have to do frequent page splits. But in general depending on the space availability in the database, you need to re-index or bcp out and in with index on. Updating statistics on tables with large number of inserts/deletes will aid in consistent performance. This is specially true for stored procedures which could create wrong plans due to bad statistics. If the procs are growing at a rapid rate, then in order to avoid any failures due to the 64K limit you will have to drop and re-create the stored procedures. That is the only reason I see why procs have to be re-created on a regular basis. You could always use sp_recompile on a table to recompile all the dependent stored procedures, and triggers to pick up any new indexes etc. - Prasad sammers@pop03.ca.us.ibm.net (crunch) wrote: > >General question on indexes. > > IMHO tables (with indexes) that have high volumes of insert and >delete operations need to be re-index periodically, else 'bad' things >will happen. Please comment. > With some databases (e.g. SQLBase 5.x), stored commands/procedures >need to be dropped and re-stored if you re-index a table. What are the >exceptions, if any? > I would appreciate any information on these subjects whether in >support or in contradiction of what I believe to be true. I have been >unsuccessful in trying to dig up literature that provides explicit >information on these matters which I need to build a case upon. Some of the >folks I work believe that re-indexing is strictly an option, not a >necessity and I have no way of proving otherwise. > Thanks much. > > >Joe Yong >Software Alliance (M) Sdn. Bhd. -- /***********************************************************************/ H.S. Prasad Standard Disclaimer Email: prasad_hs@jpmorgan.com Phone: (302)634-8398 Fax : (302)634-8563 /***********************************************************************/