What Causes Index Fragmentation?
Submitted by: AdministratorIndex 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 "ggl_link_indexed" with 100000 rows caused the index fragmented 84%:
USE GlobalGuideLineDatabase;
GO
SELECT COUNT(*) FROM ggl_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'GlobalGuideLineDatabase'),
OBJECT_ID(N'ggl_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 ggl_links_url 0
3 ggl_links_counts 0
UPDATE ggl_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'GlobalGuideLineDatabase'),
OBJECT_ID(N'ggl_links_indexed'),
DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i
WHERE s.object_id = i.object_id
AND s.index_id = i.index_id;
GO
Submitted by: Administrator
This tutorial exercise shows you how update statements of 50000 rows on the table "ggl_link_indexed" with 100000 rows caused the index fragmented 84%:
USE GlobalGuideLineDatabase;
GO
SELECT COUNT(*) FROM ggl_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'GlobalGuideLineDatabase'),
OBJECT_ID(N'ggl_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 ggl_links_url 0
3 ggl_links_counts 0
UPDATE ggl_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'GlobalGuideLineDatabase'),
OBJECT_ID(N'ggl_links_indexed'),
DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i
WHERE s.object_id = i.object_id
AND s.index_id = i.index_id;
GO
Submitted by: Administrator
<pre>0 NULL 0.574712643678161
2 ggl_links_url 84.053862508859
3 ggl_links_counts 0.448430493273543</pre>
Note that only the index on "url" fragmented. Index on "counts" did not affected, because the update statement only touched the "url" column.
Submitted by: Administrator
2 ggl_links_url 84.053862508859
3 ggl_links_counts 0.448430493273543</pre>
Note that only the index on "url" fragmented. Index on "counts" did not affected, because the update statement only touched the "url" column.
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | How To Start SQL Server Browser Service? |
☺ | PHP MSSQL - How To Delete Existing Rows in a Table? |
☺ | How To Execute the Cursor Queries with "OPEN" Statements? |
☺ | What Happens If Strings Are Casted into Wrong Code Pages in MS SQL Server? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |