Rebuild all the Indexes of a SQL Database in one go

Very handy article by Vinay Pugalia from SQL Server Central:

http://www.sqlservercentral.com/blogs/juggling_with_sql/archive/2011/6/20/rebuild-all-the-indexes-of-a-sql-database-in-one-go.aspx

Just in case the Blog ever disappears, here it is.......

What is the best value for Fill Factor in SQL Server?

Introduction

When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.

The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0, which means that the leaf-level pages are filled to capacity. Fill-factor values 0 and 100 are the same in all respects. The fill-factor setting applies only when the index is created, or rebuilt.

How does it help?

The fill-factor option is provided for fine-tuning index data storage and performance. In an insert-intensive environment, the index pages will eventually split to accommodate additional entries. To avoid or reduce the frequency of page splits, the index should be rebuilt using an appropriate fill factor.

What is the ideal value?

It depends on the ratio of reads to writes that your application makes to your SQL Server tables. As a rule of thumb, we might follow these guidelines:

•          Low Update Tables (100-1 read to write ratio): 100% fill factor
•         High Update Tables (where writes exceed reads): 50%-70% fill factor
•          Everything In-Between: 80%-90% fill factor.

Implementation

USE DBName

GO

DECLARE @tsql NVARCHAR(MAX) 

DECLARE @fillfactor INT

SET @fillfactor = 70

SELECT @tsql =

  STUFF(( SELECT DISTINCT

           ';' + 'ALTER INDEX ALL ON ' + o.name + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

          FROM

           sysobjects o

          INNER JOIN sysindexes i

           ON o.id = i.id

          WHERE

           o.xtype IN ('U','V')

           AND i.name IS NOT NULL

          FOR XML PATH('')), 1,1,'')

--PRINT @tsql         

EXEC sp_executesql @tsql 

Conclusion
This way, we can easily rebuild all the existing indexes on the Tables as well as the Indexed Views of the selected Database with an option to set the fillfactor as well. Hope, this script will prove to be handy.