Nucleus Datakonsult rebuild_heaps - rebuild all fragmented heaps

Home

About me and Nucleus
Partners

Articles, tips etc.
My blog
Links
Microsoft articles
Forums

Courses and training




Microsoft Most Valuable Professional


Overview
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

Versions etc.
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.

Parameters:
@report_type varchar(20) = 'none'
If and how a SELECT will be executed to report fragmentation level for each heap. Values allowed are:
valuemeaning
'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


The code:
You find the code here.