Forwarded records can be bad for performance, but few are actually doing anything about it.
Lots of free space on your pages means more pages to scan, bigger backups etc.
Both forwarded records and free space on pages can be considered a type of fragmentation for a heap. This stored procedure rebuilds all fragmented heaps on a SQL Server.
Here are a couple of blog posts on the topic:
Knowing about 'Forwarded Records' can help diagnose hard to find performance issues
Geek City: What's Worse Than a Table Scan?
The table scan from hell
SQL Server 2008 and later. See comment block in procedure source code for version history of the procedure.
@report_type varchar(20) = 'none'
If and how a SELECT will be executed to report fragmentation level for each heap. Values allowed are:
|'fragmented_only'||only fragmented heaps|
@print_sql_commands tinyint = 1
@exec_sql_commands tinyint = 0
@smallest_table_size_mb int = 10
@largest_table_size_mb bigint = 10000
@fragmentation_level int = 15
@free_space_level int = 30
@report_type = 'fragmented_only'
,@print_sql_commands = 0
,@exec_sql_commands = 1
,@smallest_table_size_mb = 10
,@largest_table_size_mb = 10000
,@fragmentation_level = 10
,@free_space_level = 30