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
![Img1.jpg]()
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.