Goal:

Rebuild indexes in SQL server. This can be done one at a time or with the example script below to rebuild all index for a specified table or for all tables in a given database.

Why?
The data in indexes gets fragmented over time. That means that as the index grows, the newly added rows to the index are physically stored in other sections of the allocated database storage space. Kind of like when you load your Christmas shopping into the trunk of your car and it is full you continue to load some on the back seat, in the same way some storage buffer is created for your index but once that runs out the data is then stored in other storage space and your data in your index is no longer stored in contiguous physical pages. To access the index the database manager has to "string together" disparate fragments to create the full-index and create one contiguous set of pages for that index. Defragmentation fixes that.

What does the fragmentation affect?
Depending of course on how large the table is and how fragmented the data is, can cause SQL Server to perform unnecessary data reads, slowing down SQL Server’s performance.

Which index to rebuild?
As a rule consider that when reorganize a table's clustered index, all other non-clustered indexes on that same table will automatically be rebuilt. A table can only have one clustered index.

How to rebuild all the index for one table:
The DBCC DBREINDEX command will not automatically rebuild all of the indexes on a given table in a database

How to rebuild all indexes for all tables in a given database:

USE [myDB]    -- enter your database name here

DECLARE @tableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@tableName,' ',90)     --a fill factor of 90%
FETCH NEXT FROM TableCursor INTO @tableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

What does this script do?
Reindexes all indexes in all tables of the given database. Each index is filled with a fill factor of 90%. While the command DBCC DBREINDEX runs and rebuilds the indexes, that the table becomes unavailable for use by your users temporarily until the rebuild has completed, so don't do this during production  hours as it will create a shared lock on the tables, although it will allow for read-only uncommitted data reads; i.e.e SELECT.

What is the fill factor?
Is the percentage of space on each index page for storing data when the index is created or rebuilt. It replaces the fill factor when the index was created, becoming the new default for the index and for any other nonclustered indexes rebuilt because a clustered index is rebuilt. When fillfactor is 0, DBCC DBREINDEX uses the fill factor value last specified for the index. This value is stored in the sys.indexes catalog view.

If fillfactor is specified, table_name and index_name must be specified. If fillfactor is not specified, the default fill factor, 100, is used.

How do I determine the level of fragmentation?
Run the DBCC SHOWCONTIG command. However this requires you to specify the ID of both the table and index being. To make it a lot easier by only requiring you to specify the table name and/or index you can run this script:

DECLARE
@ID int,
@IndexID int,
@IndexName varchar(128)

--Specify the table and index names
SELECT @IndexName = ‘index_name’    --name of the index
SET @ID = OBJECT_ID(‘table_name’)  -- name of the table

SELECT @IndexID = IndID
FROM sysindexes
WHERE id = @ID AND name = @IndexName

--Show the level of fragmentation
DBCC SHOWCONTIG (@id, @IndexID)

Here is an example:

DBCC SHOWCONTIG scanning 'Tickets' table...
Table: 'Tickets' (1829581556); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned................................: 915
- Extents Scanned..............................: 119
- Extent Switches..............................: 281
- Avg. Pages per Extent........................: 7.7
- Scan Density [Best Count:Actual Count].......: 40.78% [115:282]
- Logical Scan Fragmentation ..................: 16.28%
- Extent Scan Fragmentation ...................: 99.16%
- Avg. Bytes Free per Page.....................: 2457.0
- Avg. Page Density (full).....................: 69.64%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

What's important here?
The Scan Density; Ideally it should be 100%. As time goes by it drops as fragmentation occurs. When the level drops below 75%, you should consider re-indexing.

Here are the results of the same table and clustered index after running the script:

DBCC SHOWCONTIG scanning 'Tickets' table...
Table: 'Tickets' (1829581556); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned................................: 692
- Extents Scanned..............................: 87
- Extent Switches..............................: 86
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [87:87]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 22.99%
- Avg. Bytes Free per Page.....................: 639.8
- Avg. Page Density (full).....................: 92.10%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

What's different?
The Scan Density has increased from 40.78% to 100%; no fragmentation on the clustered index. Note that since we rebuilt the clustered index, all other index were also rebuilt.