8

This is a bit of an embarrassing question, and I can't believe I've missed this for so many years.

I have a vendor third-party database that has 401 heap tables. I've recently got into using Brent Ozar's scripts and setup sp_BlitzFirst to run every 15 minutes to gather wait stats, etc.

What it uncovered was every time it ran across a 24 hour period it was telling me to fix Forwarded Records. What will probably shock some readers that I ran a query across the DMVs and got back some tables with over 150,000 forwarded record values.

I understand to fix this is to have a clustered index across the table, or as a temporary work-around to run ALTER TABLE [tablename] REBUILD.

What I haven't been able to find however is whether this takes the table offline, and whether there are any other problems I should be aware of before running this command.

I am using the Enterprise Edition of 2008 R2, and I wonder whether running it this way will remove the need for an outage?

ALTER TABLE [tablename] REBUILD WITH (ONLINE = ON);  

Does anyone have any experience with this?

9

Good news: 150,000 forwarded records isn't actually that bad, depending on what kind of time span we're talking about. Forwarded records are tracked as long as the server is up (with some gotchas around a bug in specific 2012/2014 builds.)

Even when it does work online, your users can notice it depending on your IO throughput, size of the table, number of nonclustered indexes, and your workloads.

Here's how I tackle it:

  • List the tables with the forwarded records warnings from smallest to largest table size (not number of forwarded records)
  • Rebuild those tables in order, by hand, a few at a time. Don't automate this - you want to get a feel for how your system responds to rebuilding small tables. You'll learn fairly quickly if your server can keep up.
  • Keep an eye on your transaction log usage. If your log file is 10GB, and you're trying to rebuild a 50GB heap, you can run into problems. You can mitigate these issues by growing the log out ahead of time during maintenance windows and by doing frequent log backups while you're doing this - but I'll be honest, at 150k forwarded records across the span of a few days, I wouldn't bother going through this much work.
  • Going forward, work with the vendor to put a clustered key on those tables. Yes, there are times when heaps are appropriate, but if you're running into the forwarded record problem, you're probably not in one of those heap-friendly situations.
|improve this answer|||||

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.