Reindexing the entire database in sql server

Re-indexing the entire database:

Usually, if the database is too slow. DBA's were advised to re-index the tables. For some cases, we need to rebuild the indexes availables in the entire database.

For this scenario, we can rebuild the index by taking each table and re-index all the indexes associated with each table.

Fill factor:

Amount or compactness of data in the leaf level is defined by the term fill factor. Based on the operations on the database DBA's will decide the fill factor. If the insert/update/delete are very high in that case we will have very less fill factor (Around 60 to 70). If there is very less insert/update/delete, in that case we will have very high fill factor (Around 90). On an average, we will give 80 -90 %.



Below is the script to achieve this,

----------------------------------------------------------------------------
DECLARE @DatabaseTable VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT name AS DatabaseTable
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @DatabaseTable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'DBCC DBREINDEX('+ @DatabaseTable + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @DatabaseTable
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

-------------------------------------------------------------

How abt re-organsing the indexes (Rebuild index vs reorganise index):

1. Re-built can't be done on the production run time (Due to its high impact on rearranging the data and recreate of index) where as re-organise index can be done.

2. Indexes were recreated in case of re-building the index.

3. Re-built index is very effective when compared to the other.

To find the free space available in the log files:

Below is the command used to identify the amount the space available or percentage used.

DBCC SQLPERF (LOGSPACE)