Forwarded Records, effective way to fix them

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.dm_db_index_physical_stats, sys.indexes and 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" fo

SQL Server does not keep a "statistic" around for the number of forwarded records. To return the number it has to access the physical table pages. If the table is large, that can take a while.

That said, you can query sys.dm_db_index_physical_stats in SAMPLED mode. This will give you an estimate for the number of forwarded records based on a 1% sample of the base table.

Alternatively, you could correlate the number of updates (see sys.dm_db_index_usage_stats) with the number of forwarded records on each table. Say if every 10th update causes a forward, then you can check if the table has crossed your threshold by looking if the number of executed updates is 10 times higher then your threshold. Querying sys.dm_db_index_usage_stats is instant. However, it accumulates starting fresh with each server restart.

I would still regularly (once a month maybe) run the sys.dm_db_index_physical_stats, to make sure that the ratio is still accurate.

To actually get rid of the forwarded records you can use ALTER TABLE dbo.test REBUILD WITH(ONLINE=ON); though this will also rebuild all nonclustered indexes on a heap table. That should be a lot faster than creating and dropping a clustered index, which rebuilds all nonclustered indexes twice. (The WITH(ONLINE=ON) clause requires Enterprise Edition.)

Now, the bigger question you have to ask yourself is, if you really should have 4000 heaps in a database. In general a well chosen clustered index will improve performance of the system as a whole. The storage structure "heap" is usually only recommended for tables with a high number of inserts, and very few selects, updates or deletes.

In a typical OLTP environment that would be only true for log tables. Try to figure out if you could add clustered indexes to your tables, and your forwarding pain will go away. (Then however you need to deal with fragmentation, but there are some good (and free) solutions on the market already; e.g. Ola Hallengren's scripts).

StackExchange Database Administrators Q#47047, answer score: 3