In flashback
In my previous article, I am trying to discuss related to Index architecture, how clustered index works and the fragmentation of the index.
If you needed any references, you can find it from
Related to Clustered Index
Index fragmentation
Introductions
In this article I am trying to discuss related to the Index defragmentation. I have learned several article related to it and collects and gather the facts related to it.
Fragmentation of the Index can be de-fragmented by two ways; it depends on size of the table and the level of the defragmentation.
- Reorganize Index
- Rebuild Index
Reorganize Index
It does not take much more system resources. That means it can be done when the user access the table objects where the index exists. This process reorganizes the leaf nodes of the index physically to match it with logical order. If the physical order matches the logical order of the index it increases the performance.
To reorganize a single partition of a partitioned index, use the PARTITION clause of ALTER INDEX.
Reorganizing an index defragments the leaf level of clustered and non-clustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes. Having the pages in order improves index-scanning performance.
The index is reorganized within the existing pages allocated to it; no new pages are allocated. If an index spans more than one file, the files are reorganized one at a time. Pages do not migrate between files.
Reorganizing also compacts the index pages. Any empty pages created by this compaction are removed providing additional available disk space. Compaction is based on the fill factor value in the sys.indexes catalog view.
The reorganize process uses minimal system resources. Also, reorganizing is automatically performed online. The process does not hold long-term blocking locks; therefore, it will not block running queries or updates.
Reorganize an index when the index is not heavily fragmented. See the previous table for fragmentation guidelines. However, if the index is heavily fragmented, you will achieve better results by rebuilding the index.
The data types image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml are large object data types. Compacting this data can cause better disk space use.
Reorganizing a specified clustered index will compact all LOB columns that are contained in the leaf level (data rows) of the clustered index.
Reorganizing a non-clustered index will compact all LOB columns that are non-key (included) columns in the index.
When ALL is specified, all indexes associated with the specified table or views are reorganized and all LOB columns associated with the clustered index, underlying table, or non-clustered index with included columns are compacted.
The LOB_COMPACTION clause is ignored if LOB columns are not present.
Rebuild Index
Rebuild index means the dropping an existing index of the table and create a new index. The older index is gone with the drop and in the new index the logical order matches the physical order of the index and improves the performance.
In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages using the specified or existing fill factor setting, and the index rows are reordered in contiguous pages and allocating new pages as needed. This can improve disk performance by reducing the number of page reads required to obtain the requested data.
To rebuilds clustered and non-clustered indexes ALTER INDEX with the REBUILD clause. This statement replaces the DBCC DBREINDEX statement of previous version of the SQL Server and the CREATE INDEX with the DROP_EXISTING clause.
If we rebuilding is a cluster index, then our tables will be unavailable to the user during the rebuild process and if it is a non-clustered index only a shared lock will be placed on the table so users will be able to access the table, but would not be allowed to do modifications on it.
SQL Server 2005 the online option for index rebuilds is only available in Enterprise edition while in SQL Server 2008 it is available in Enterprise, Developer, and Evaluation editions.
When we are creating an index online, it does not drop the existing index rather it keeps the original index to be used by the users and creates an index parallel to it. During this process only the source index will be available for use by the users. Once the index rebuild operation is complete, the original index will be dropped and the new one will be updated and available for read and write operations.
Reorganize Index Example
-- Base Table Defination
CREATE TABLE emp_record
(empID INT IDENTITY(1,1) NOT NULL,
empNAME VARCHAR(50) NOT NULL,
empDEMP CHAR(1),
CONSTRAINT PK_emp_record PRIMARY KEY CLUSTERED(empID)
)
-- Reorganize Index
ALTER INDEX PK_emp_record ON emp_record REORGANIZE
-- Reorganize All Index
ALTER INDEX ALL ON emp_record REORGANIZE
Rebuild Index Example
-- Rebuild Index
ALTER INDEX PK_emp_record ON emp_record REBUILD
-- Rebuild Index with option specified
ALTER INDEX ALL ON emp_record
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
-- Rebuild Index online
ALTER INDEX PK_emp_record ON emp_record REBUILD WITH (ONLINE = ON)
ALTER INDEX ALL ON emp_record REBUILD WITH (ONLINE = ON)
Summarize the Differences
- Index Rebuild drops the existing Index and Recreates the index from scratch.
- Index Reorganize physically reorganizes the leaf nodes of the index.
- Rebuild the Index when an index is over 30% fragmented.
- Reorganize the Index when an index is between 10% and 30% fragmented.
- If fragmentation is below 10%, no action required.
Rebuilding takes more server resources and uses locks unless you use the ONLINE option available in 2005 Enterprise and Development editions.
Defrag Index Stored Procedure
This stored procedure defrags the index accordingly. This is prototype only. You can modify it …
IF OBJECT_ID('proc_DEFRAG') IS NOT NULL
BEGIN
DROP PROCEDURE proc_DEFRAG
END
GO
CREATE PROCEDURE proc_DEFRAG
(
@p_DBName VARCHAR(50) = NULL,
@p_SchemaNm VARCHAR(50) = NULL,
@p_TBLName VARCHAR(50) = NULL,
@p_IndxName VARCHAR(50) = NULL
)
AS
DECLARE @v_FrgReOrgLmt DECIMAL(10,2),
@v_FrgReBuildLmt DECIMAL(10,2),
@v_CurrentFreg DECIMAL(10,2),
@v_Flag DECIMAL(1),
@v_fillfactor INT,
@v_Str VARCHAR(MAX),
@v_Message VARCHAR(MAX)
BEGIN
SET @v_FrgReOrgLmt = 10.00
SET @v_FrgReBuildLmt = 30.00
SET @v_CurrentFreg = 0.00
SET @v_Flag = 0
SET @v_fillfactor = 80
IF ISNULL(@p_DBName, '')<>''
AND ISNULL(@p_TBLName, '')<>''
AND ISNULL(@p_IndxName, '')<>''
AND ISNULL(@p_SchemaNm, '')<>''
BEGIN
SELECT @v_CurrentFreg = avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(@p_DBName), NULL, NULL, NULL , NULL)a
INNER JOIN sys.tables b WITH (nolock) ON a.OBJECT_ID = b.OBJECT_ID
INNER JOIN sys.indexes c WITH (nolock) ON a.OBJECT_ID = c.OBJECT_ID
AND a.index_id = c.index_id
WHERE b.name = @p_TBLName
AND c.name = @p_IndxName
--Reorganizing the index
IF ((@v_CurrentFreg >= @v_FrgReOrgLmt)
AND (@v_CurrentFreg <= @v_FrgReBuildLmt))
BEGIN
SET @v_Str = 'ALTER INDEX ' + @p_IndxName +
' ON [' + RTRIM(LTRIM(@p_SchemaNm)) +
'].[' + RTRIM(LTRIM(@p_TBLName)) +
'] REORGANIZE'
SET @v_Flag = 1
SET @v_Message = 'Rebuild Index with REORGANIZE'
GOTO WAYOUT
END
--Rebuilding the index
IF (@v_CurrentFreg>@v_FrgReBuildLmt)
BEGIN
SET @v_Str = 'ALTER INDEX ' + @p_IndxName +
' ON [' + RTRIM(LTRIM(@p_SchemaNm)) +
'].[' + RTRIM(LTRIM(@p_TBLName)) +
'] REBUILD WITH (FILLFACTOR = ' +
CONVERT(VARCHAR(3),@v_fillfactor) +
', STATISTICS_NORECOMPUTE = OFF)'
SET @v_Flag = 1
SET @v_Message = 'Rebuild Index with REBGUILD'
END
WAYOUT:
IF @v_Flag = 1
BEGIN
EXEC (@v_Str)
END
ELSE
BEGIN
SET @v_Message = 'Parameter is NOT properly Supplied'
END
RAISERROR(@v_Message, 0, 1) WITH NOWAIT
RAISERROR('', 0, 1) WITH NOWAIT
END
END
GO
Hope you like it.
Posted by: MR. JOYDEEP DAS
Very good this article!
ReplyDeleteThanks "Luis"
Delete