Reindexing and Reorganized in SQL Server 2008

Today I discuss Reindexing and Reorganized in SQL Server 2008. It does improve the performance of all tables in database. This feature is best for database tuning. And provide script for rebuild and Reorganize of all Indexes in all the Tables for a specify database.

The SQL Server Database Engine automatically maintains indexes when we used to DML operations on tables. In Index fragmentation is a phenomenon where the index contents are scattered. Normally the contents are in contiguous fashion which helps in fast recovery of the underlying data. When the indexes are fragmented the data access becomes time consuming because of the scattered data that needs to be searched and read. How to detect fragmentation of tables.

Basically REINDEXING is drops the existing Index and Recreates the index given below query.

Syntax

ALTER INDEX ALL ON table_name REBUILD

It will automatically rebuild all index-column statistics.

Reorganizes doesn't drop indexes, it physically reorganizes the leaf nodes of the index given below query.

Syntax: ALTER INDEX ALL ON table_name REORGANIZE

It will not automatically reorganize of all index-column statistics we use given below query.

Syntax

UPDATE STATISTICS table_name

If index average fragmentation is less than 30% index will be reorganized, if index average fragmentation is greater than 30% index will be rebuilt. The following query we can find average fragmentation which is below.

USE [testnew]
GO
DECLARE @DBName VARCHAR(50)='testnew'
DECLARE @dbid BIGINT-- Database DB Id
SET @dbid=DB_ID(@DBName)

SELECT DISTINCT @DBName
AS DATABASENAME,
SCHEMA_NAME(schema_id) AS SchemaName,
o.name AS TableName,
i.index_id,
i.name AS IndexName,
a.Index_type_desc,
a.avg_fragmentation_in_percent FRAG_VAL
FROM sys.indexes i
JOIN sys.objects o
ON i.object_id = o.object_id
JOIN sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, NULL) AS a
ON a.object_id = i.object_id
WHERE o.[type] = 'U'
AND a.index_id = i.index_id
ORDER BY o.[name],
i.[name]

Output

I am providing script for rebuild and Reorganize of all Indexes in all the Tables for a specify database on Top. You can download attached file.