Collections:
What Causes Index Fragmentation in SQL Server
What Causes Index Fragmentation in SQL Server?
✍: FYIcenter.com
Index fragmentation is usually caused by deleting of existing rows or updating existing values of the indexed column. Inserting new rows should not cause any index fragmentation.
This tutorial exercise shows you how update statements of 50000 rows on the table "fyi_link_indexed" with 100000 rows caused the index fragmented 84%:
USE FyiCenterData;
GO
SELECT COUNT(*) FROM fyi_links_indexed;
GO
100000
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 0
3 fyi_links_counts 0
UPDATE fyi_links_indexed SET url = REVERSE(url)
WHERE id <=50000;
GO
(50000 row(s) affected)
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
Note that only the index on "url" fragmented. Index on "counts" did not affected, because the update statement only touched the "url" column.
⇒ Defragmenting Table Indexes in SQL Server
⇐ What Is Index Fragmentation in SQL Server
2016-11-08, 4010🔥, 0💬
Popular Posts:
How to set database to be READ_ONLY in SQL Server? Databases in SQL Server have two update options: ...
How To Convert Numeric Values to Integers in SQL Server Transact-SQL? Sometimes you need to round a ...
What Happens to the Current Transaction If a DDL Statement Is Executed in Oracle? If a DDL statement...
What is SQL Server Transact-SQL (T-SQL)? SQL Server Transact-SQL, also called T-SQL, is an extension...
How to check if two JSON values have overlaps using the JSON_OVERLAPS() function? JSON_OVERLAPS(json...