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:

  1. 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.

  2. 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.

  3. 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)

  • 2
    Can you add a clustered index permanently? Even a random surrogate key? The problem is heaps, not forwarded records per se – gbn Jul 26 '13 at 9:13
  • We're dealing with multiple versions of SQL. 2005, 2008 R2 and 2012. All Enterprise. Wouldn't adding a clustered index permanently have a impact on the performance? These are high transactional databases with an average of 3000 connections at our big clients. – Thom Jul 26 '13 at 9:20
  • @Thom Yes, it would probably have a permanent positive impact on performance. Obviously test test test but it's more likely to do good than harm. – Mark Storey-Smith Jul 26 '13 at 9:44
  • What I can't seem to understand is why you would drop and create a clustered index on a table that already has a clustered index. This looks like a one time activity to me! @Thom – Storm Jun 18 '14 at 7:46

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).

| improve this answer | |
  • sys.dm_db_index_usage_stats could be very intresting. I'll take a look into it. I'll have to contact the publisher of the application to ask them for the reasons not to include clustered indexes on some of these tables. We're already dealing with indexes and statistics, which are running smoothly. Thanks for your input. I'll look a bit further into it! – Thom Jul 26 '13 at 9:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.