Tracking and Alerting on Forwarded Records With SQL Sentry

A forwarded record occurs when a row within a heap, a database table without a clustered index, has been moved from its original page to a new page. This leaves behind a forwarding pointer at the original location that points to the new page.

This happens when a column within the table is updated, the size increases, and it cannot fit on the page any longer. That data has to go somewhere since it no longer fits in its original location, leaving behind a pointer to where the rest of the data is located.

Why Are Forwarded Records an Issue?

Forwarded records can cause performance problems since data required to fulfill a request is spread across multiple data pages. This requires the database engine to spend more time and resources using forwarding pointers to search for required data.

The best practice is to create a clustered index on every table; however, sometimes there are cases when a clustered index might not be desired. If that’s your scenario, you should be aware of heap tables in your data estate and ensure you’re monitoring the number of forwarded records regularly.

Alerting With SQL Sentry

SQL Sentry collects and tracks the Forwarded records/sec performance counter, which allows you to see the number of records per second fetched through forwarded record pointers. This number should be as close to zero as possible.

Forwarded recs/sec Performance Analysis Dashboard metric

I’ve created an Advisory Condition in SQL Sentry to alerts me when the Forwarded records/sec value exceeds zero. Note, you can adjust the threshold based on your specific needs. For more information about creating an Advisory Condition in SQL Sentry, check out the “Building an Advisory Condition” article in the SentryOne documentation.

Advisory Condition creation

Once you’ve created or imported the Advisory Condition provided at the end of this blog post (with the Conditions List open, navigate to File > Import Condition), ensure you add it to your Conditions with your chosen action:

Advisory Condition action selection

With SQL Sentry Advisory Conditions, you can choose to execute a SQL script in response to an alert and tie it to a query capturing counts for forwarded records. I would use this script with caution depending on the size of your environment, number of heap tables, and the size of your heap tables, but this information can further assist you with your investigation.

A sample query would look like the following:

SELECT

OBJECT_NAME(IPS.object_id) as TableName,

IPS.index_type_desc,

IPS.avg_fragmentation_in_percent,

IPS.forwarded_record_count,

IPS.page_count

FROM sys.dm_db_index_physical_stats (DB_ID(), DEFAULT, DEFAULT, DEFAULT, ‘DETAILED’) AS IPS

WHERE index_type_desc = ‘heap’ and forwarded_record_count is NOT NULL

For more information about customizing email messages with a query result, see the blog post, “Using the Execute SQL Action to Customize a SQL Sentry Alert Message.”

Reporting on Forwarded Records With SQL Sentry

In addition to alerting on forwarded records, you have the ability in SQL Sentry to generate a report displaying the forwarded records value over time. To do so, navigate to Reports > Performance Analysis > Performance > Performance Counter History.

Performance Counter History report parameters

Note, this report can include the selected counter(s), Forwarded Records/sec in this case, from multiple servers in one report if needed.

Common Solutions

Now that you have an idea of how to alert and report on forwarded records, you’ll need to resolve the underlying issue that’s causing forwarded records to be elevated. Here are a few common solutions to issues related to forwarded records.

• Use a fixed-length data type: Consider using fixed-length data types to avoid a large number of logical reads.
• Add a clustered index: Adding a clustered index to your heap table will prevent the forwarded records issue for existing and newly updated data.
• Rebuild heap tables: If you can’t use fixed-length data types or a clustered index, you’ll need to use the REBUILD command, shown below, to rebuild the heap and resolve the forwarded records problem.

ALTER TABLE TableName REBUILD;

To learn more about these solutions, check out Brent Ozar’s blog post, “How To Fix Forwarded Records.”

Putting It All Together

Forwarded records can create performance issues within your environment under the right circumstances if left unchecked. With SQL Sentry, you can begin to understand and track forwarded records and any performance issues they might cause. Not all solutions are right for every environment, so you’ll want to carefully consider whether clustered indexes or increased maintenance on heap tables, where forwarded records are a problem, would be appropriate for your specific scenario.

Download the Code Covered in This Blog Post

• Forwarded Records.condition

Dexter Cox

Dexter is a Customer Success Engineer with a passion for helping customers navigate through SentryOne.
With several years of experience on the Support team, Dexter makes it a priority to resolve any issues customers might be facing.
Since moving to Customer Success in November 2019, he has begun to take charge in ensuring customers have the knowledge to leverage SentryOne
to resolve SQL Server related issues through Tips and Tricks, alert optimization, and blog posts.