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 Forwarded Records (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.
Interested in learning more about SQL Sentry? Check out the interactive demo today.