Reindexing and Reorganized in SQL Server 2008

Om Agarwal
11y
5.1k
0
2

Blog

Reindexing.zip

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]