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, 1940🔥, 0💬
Popular Posts:
How To Update Multiple Rows with One UPDATE Statement in SQL Server? If the WHERE clause in an UPDAT...
How To Fix the INSERT Command Denied Error in MySQL? The reason for getting the "1142: INSERT comman...
How to download and install SQL Server 2005 Sample Scripts in SQL Server? If you want to learn from ...
How To Verify a User name with SQLCMD Tool in SQL Server? The quickest way to verify a user name in ...
How to download Microsoft SQL Server 2005 Express Edition in SQL Server? Microsoft SQL Server 2005 E...