Q: I developed a clinical progam in SQl server and vb which I’m using it for the last 5 years.At times there is some delay in executing the search commands esp the name and address text boxes.I overcome this problem by backing up the database or transferring it to another computer.Would like to know a permanent remedy for this problem

Kevin Kline says: The problem is that you are not updating index statistics regularly. (You do have indexes on the tables in your database, right?) Index statistics become stale over time and are ove no value when stale. There are two ways to refresh index statistics. The very slow and resource intensive way is to drop and recreate the indexes. The fast and less resource intensive way is to run the UPDATE STATISTICS command against each table in the database.

For my production databases, I create a job that runs every night. The job simply executes UPDATE STATISTICS against all of the user databases in the database. I also create a weekly job that runs during the weekends, when few if any users are on the system, that drops and recreates the clustered index of each table to restore the table fill factor to my preferred value of 75%. I recommend that you do the same.