Tuesday, 14 July 2015 00:00

rebuild_heaps - rebuild all fragmented heaps

Rate this item
(0 votes)

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(http://blogs.msdn.com/b/mssqlisv/archive/2006/12/01/knowing-about-forwarded-records-can-help-diagnose-hard-to-find-performance-issues.aspx)
Geek City: What's Worse Than a Table Scan?(http://sqlblog.com/blogs/kalen_delaney/archive/2008/05/25/whats-worse-than-a-table-scan.aspx)
The table scan from hell(http://sqlblog.com/blogs/hugo_kornelis/archive/2006/11/03/The-table-scan-from-hell.aspx)

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:
value meaning
'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 

Reference: http://www.karaszi.com/sqlserver/util_rebuild_heaps.asp

The code:
You find the code here (http://www.karaszi.com/sqlserver/code/rebuild_heaps.sql.txt)
Last modified on Friday, 17 July 2015 08:51
Data Recovery Expert

Viktor S., Ph.D. (Electrical/Computer Engineering), was hired by DataRecoup, the international data recovery corporation, in 2012. Promoted to Engineering Senior Manager in 2010 and then to his current position, as C.I.O. of DataRecoup, in 2014. Responsible for the management of critical, high-priority RAID data recovery cases and the application of his expert, comprehensive knowledge in database data retrieval. He is also responsible for planning and implementing SEO/SEM and other internet-based marketing strategies. Currently, Viktor S., Ph.D., is focusing on the further development and expansion of DataRecoup’s major internet marketing campaign for their already successful proprietary software application “Data Recovery for Windows” (an application which he developed).

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

Get Help Now

Thank you for contacting us.
Your Private Investigator will call you shortly.