Rebuild all fragmented heaps

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

The code

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:

'none'no report
'all'all heaps
'fragmented_only'only fragmented heaps

@print_sql_commands tinyint = 1
Whether to print the ALTER TABLE commands

@exec_sql_commands tinyint = 0
Whether to execute the ALTER TABLE commands (0 basically means "report only")

@smallest_table_size_mb int = 10
Do not rebuild if table is smaller than this value (Mb)

@largest_table_size_mb bigint = 10000
Do not rebuild if table is bigger than this (Mb)

@fragmentation_level int = 15
Rebuild if fragmentation in percent is higher than this value

@free_space_level int = 30
Rebuild if free space is higher than this value

Usage example
EXEC rebuild_heaps
 @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