Maintaining Indexes - Microsoft SQL Server 2012 Performance Tuning - page 233

F To rebuild one index of a table with Fill Factor, use the following query:
--rebuilding idx_refno index of ordDemo table
--with Fill Factor 90
DBCC DBREINDEX ('ordDemo','idx_refno',90)
GO
The DBCC DBREINDEX command will be deprecated from the future version
of SQL Server, so it is best practice to avoid that command and use the
alternate commands given in the previous list.
How it works...
Rebuilding an index is nothing more than dropping the existing index and creating a new one.
While creating a new index, rebuilding locks your object (if the rebuild mode is not Online) and
it will not be available for access until the process is finished. Rebuilding removes blank or
unused pages, creates new ones, splits pages if they do not meet Fill Factor criteria, and sorts
data pages to match up the logical sorting order in the index B-Tree.
There's more...
Based on personal experience, I recommend rebuilding an index of "large" tables with
bulk-logged recovery mode or simple recovery mode rather than full recovery mode, to
avoid excessive log file size. As soon as we finish with rebuilding an index in a large table,
we can move to the simple recovery mode.
A word of caution here: If you change your recovery mode from full to anything
else, you will break your database backup chain, if there is any. So you will
have to take a full backup again, after changing recovery mode back to full,
which may not be possible in every production environment.
Because it is mentioned that a large table's index might take few hours or may be even a
day, don't loose your patience and don't stop rebuilding the index in between. It could be
dangerous and the database may fall into recovery mode.
The user should be the owner of the table or a member of the sysadmin fixed server role, the
db_owner fixed database role, or the db_ddladmin fixed database role, in order to perform
REBUILD or DBCC DBREINDEX .