Solutions to fix Index Fragmentation:
Reindex/Rebuild vs Reorganize/Defrag:
Rebuild:
Syntax: ALTER INDEX...REBUILD (replaces DBCC REINDEX)
- Offline unless Enterprise Edition
- Updates Statistics also
- SQL Server Standard Edition index rebuilds are always an offline, single-threaded operation.
- SQL Server Enterprise Edition you can specify an "Online" rebuild — unless the index contains large object types
Reorganize:
Syntax: ALTER INDEX...REORGANIZE (replaces DBCC INDEXDEFRAG)
- Does not Update Statistics
- Always an online operation regardless of edition.
- Does not require a schema mod lock so provides better concurrency.
- Reorganizing only defrags the leaf level of the index. (Logical)
- Large tbls can take longer vs a rebuild. But it’s nice that you can reorganize for a while and then pause with out facing a massive rollback.
Rebuilding an index drops & re-creates the index, removes fragmentation & reclaims disk space by compacting the pages based on the specified
fillfactor and reorders the index rows in contiguous pages. If "ALL" is included, all indexes on the table are dropped and rebuilt in a single transaction. You can apply a new fillfactor when you rebuild an index. If you cancel a rebuild operation midstream, it must roll back (and if it’s being done offline, that can take a while).
Reorganizing an index uses minimal system resources(more lightweight). It defrags the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value. This operation is always online, and if you cancel it then it’s able to just stop where it is (it doesn’t have to issue rollback).
SQL SERVER 2014 has some new options to consider: WAIT_AT_LOW_PRIORITY, MAX_DURATION, AND ABORT_AFTER_WAIT
Index Maintenance Guidance from Microsoft:
"Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)"
MSDN - Clustered and Nonclustered Indexes Described
Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key
...Generally, searching the index is much faster than searching the table because unlike a table, an index frequently contains very few columns per row and the rows are in sorted order.
Clustered Index Structures
"...In a clustered index, the leaf nodes contain the data pages of the underlying table."
MSDN Blogs - Notes - SQL Server Index Fragmentation, Types and Solutions
Internal Fragmentation - Inside a page; page has unused space
Logical Fragmentation - Indexes have pages in which the logical ordering of pages, which is based on the key value, does not match the physical ordering inside the data file.
Extent Fragmentation - Extent fragmentation occurs when the extents of a table or index are not contiguous.
sys.dm_db_index_physical_stats:
Avg_fragmentation_in_percent – depicts logical fragmentation
Avg_page_space_used_in_percent – depicts internal fragmentation
How to detect Index Fragmentation:
Use the sys.dm_db_index_physical_stats DMF, that replaces the deprecated DBCC SHOWCONTIG command.
sys.dm_db_index_physical_stats (Transact-SQL)
"... Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes
The following example automatically reorganizes or rebuilds all partitions in a database that have an average fragmentation over 10 percent."
First - analyze all indexes that have avg_fragmentation_in_percent > 1 AND page count > 1000
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, indexstats.page_count,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent, *
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 1
and indexstats.page_count > 10000
ORDER BY indexstats.avg_fragmentation_in_percent DESC
Second - Confirm Fragmentation:
DBCC ShowContig ('LOG_WEBSERVICE_REQUESTS') WITH ALL_INDEXES (this will be retired in future SQL Server versions)
or
Use: sys.dm_db_index_physical_stats (replaces DBCC ShowContig)
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'DB');
SET @object_id = OBJECT_ID(N'DB.dbo.LOG_WEBSERVICE_REQUESTS');
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
Script to Rebuild a single Index with 90% fillfactor
Note: DBREINDEX will be phased out in future MSSQL Editions.
DBCC DBREINDEX(TableName,IndexName ,90)
Script to Rebuild(using DBREINDEX) all tables in a database or a selection of tables
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
and
(table_name = 'LOG_WEBSERVICE_REQUESTS'
)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @TableName
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Scripts:
--Index Reorganization:
USE [DB]
GO
ALTER INDEX [IX_LISTING_TBL_MAP_PRICE] ON [dbo].[LISTING_TBL] REORGANIZE WITH ( LOB_COMPACTION = ON )
GO
--Index Rebuild:
USE [DB]
GO
ALTER INDEX [IX_LISTING_TBL_MAP_PRICE] ON [dbo].[LISTING_TBL]
REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
GO
--To test if Online Index REBUILD is available for your MSSQL Edition just change ONLINE = OFF to
ONLINE = ON
Ex.
USE [DB]
GO
ALTER INDEX [IX_LISTING_TBL_MAP_PRICE] ON [dbo].[LISTING_TBL]
REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
GO
Error:
Msg 1712, Level 16, State 1, Line 3
Online index operations can only be performed in Enterprise edition of SQL Server.
Resources:
Why, when and how to rebuild and reorganize SQL Server indexes
SQL Server In depth: What can Cause Index to be Still Fragmented After Rebuild
Rebuilding SQL Server indexes using the ONLINE option
SQL Server Fragmentation How to address it
Custom SQL Server Index Defrag and Rebuild Procedures
Reorganize and Rebuild Indexes
DBCC DBREINDEX (Transact-SQL)
Microsoft SQL Server 2000 Index Defragmentation Best Practices
Stop Worrying About SQL Server Fragmentation
Does a Clustered Index really physically store the rows in key order?
How Much is Offline During an Index Rebuild?
...If you’ve got Enterprise Edition, you want to remember to always create your nonclustered indexes with the “ONLINE” option if you need to avoid blocking on the table.
Rebuilding SQL Server indexes using the ONLINE option
Rebuild or Reorganize: SQL Server Index Maintenance
...SQL SERVER 2014: WAIT_AT_LOW_PRIORITY, MAX_DURATION, AND ABORT_AFTER_WAIT!!!!!
|
|
|
|
|
|