Collections:
"ALTER INDEX ... REORGANIZE" - Defragmenting Indexes in SQL Server
How To Defragment Indexes with ALTER INDEX ... REORGANIZE in SQL Server?
✍: FYIcenter.com
When an index is defragmented to a small percentage, like < 30%, you can use the "ALTER INDEX ... REORGANIZE" statement to defragment the index. Here is a tutorial exercise on defragmenting indexes:
USE FyiCenterData; GO SELECT i.index_id, i.name, s.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats ( DB_ID(N'FyiCenterData'), OBJECT_ID(N'fyi_links_indexed'), DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i WHERE s.object_id = i.object_id AND s.index_id = i.index_id; GO 0 NULL 0.574712643678161 2 fyi_links_url 84.053862508859 3 fyi_links_counts 0.448430493273543 ALTER INDEX fyi_links_url ON fyi_links_indexed REORGANIZE; GO SELECT i.index_id, i.name, s.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats ( DB_ID(N'FyiCenterData'), OBJECT_ID(N'fyi_links_indexed'), DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i WHERE s.object_id = i.object_id AND s.index_id = i.index_id; GO 0 NULL 0.574712643678161 2 fyi_links_url 1.87590187590188 3 fyi_links_counts 0.448430493273543
The fragmentation level has been reduced from 84.05% to 1.88%.
⇒ "ALTER INDEX ... REBUILD" - Defragmenting Indexes in SQL Server
⇐ Defragmenting Table Indexes in SQL Server
2016-11-08, 1916🔥, 0💬
Popular Posts:
How To Drop an Index in Oracle? If you don't need an existing index any more, you should delete it w...
What Is Program Global Area (PGA) in Oracle? A Program Global Area (PGA) is a memory buffer that is ...
How To Get Year, Month and Day Out of DATETIME Values in SQL Server Transact-SQL? You can use DATEPA...
How To Format DATETIME Values to Strings with the CONVERT() Function in SQL Server Transact-SQL? SQL...
How to download and install SQL Server 2005 Sample Scripts in SQL Server? If you want to learn from ...