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, 1917🔥, 0💬
Popular Posts:
Where to find answers to frequently asked questions on CREATE, ALTER and DROP Statements in MySQL? H...
What To Do If the StartDB.bat Failed to Start the XE Instance in Oracle? If StartDB.bat failed to st...
How To Change the Password for Your Own User Account in MySQL? If you want to change the password of...
How To Query Tables and Loop through the Returning Rows in MySQL? The best way to query tables and l...
Where to find answers to frequently asked questions on CREATE, ALTER and DROP Statements in MySQL? H...