|rebuild_heaps - rebuild all fragmented heaps|
About me and Nucleus
Articles, tips etc.
Courses and training
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
This script was written and tested on SQL Server 2012. It should also work on 2008 and 2008 R2.
See comment block in procedure source code for version history of the procedure.
If and how a SELECT will be executed to report fragmentation level for each heap. Values allowed are:
Whether to print the ALTER TABLE commands
Whether to execute the ALTER TABLE commands (0 basically means "report only")
Do not rebuild if table is smaller than this value (Mb)
Do not rebuild if table is bigger than this (Mb)
Rebuild if fragmentation in percent is higher than this value
Rebuild if free space is higher than this value
You find the code here.