I'm dealing with an issue in SQL Server databases of an application. This application is used for Healthcare and I only maintain the databases, I do not create the application.
Clients are facing the problem of forwarded records on their tables after a period of time. I've created a stored procedure to fix these forwarded records by creating a clustered index and dropping it again, effectivly reorganizing the table and fixing the forwarded records.
The way my current script works is as follows:
Fill a table (
#Forwarded_Records) with information from
sys.columns. This will fill in the table with tables which contain forwarded records and information about their indexes including offline/online availability.
Once the table is filled, the stored procedure will check if there is still time in the window to do other jobs. If there is it will start with the first record from the forwarded_records table and start creating/dropping an clustered index.
Once all the records have been fixed, it will start to refill the forwarded_records table and search for new tables containing forwarded records. This will start the cycle again.
I'm facing the following problems:
- Healthcare only gives small maintenance windows per day and the database must be performing good/always available 24/7
- The filling of the forwarded_records table can take up to 45 minutes(way too long). This is also the case if there are no forwarded records to be found.
- The databases can consist out of 4000+ tables. Some having over millions rows. I prefer first looking for forwarded records instead of applying the job to all tables(including ones which don't contain any forwarded records)
Now what is my question?
How can I improve this process so it will be more effective. Currently it is very effective in its first cycle and fixes all the forwarded records. But starting it's second cycle it will just spend more time on "finding" forwarded records than fixing them(it takes 45 minutes to find them, and only 2 minutes to fix them).
Are there any better methods for my case? Should I take the 45 minutes per day to find them for granted or am I better off doing all tables instead of searching for tables which actually need fixing.
Thank you for your help.
(First post on StackExchange, tried to be as constructive as possible. Please help if this question needs editing)