As we keep on adding more and more records or keep deleting records from tables we can cause fragmentation and thus in turn affect query performance.
Fragmentation can be of two kinds: internal and external
Internal Fragmentation: It is caused whenever there is a lot of empty space in the index pages. This can be controlled by fill factor which estimates as to how much of the page has to be filled with data.
External Fragmentation: It is caused whenever a new page is created in an extent because of massive addition of records and when the current extent is full, a new extent will be allocatted and the page gets assigned to that extent. But this page is not in the same extent as previous extent and thus causes more extent swtiches with additional reads in non-sequential manner.
1. DBCC Command:
DBCC SHOWCONTIG – it gives fragmentation of a particular table or an index. It can be used in conjunction with TABLERESULTS to out the results to a table. It gives a very important information about avg. page density, scan density and logical scan fragmentation.
[cc lang=”sql”]
Use adventureworks
go
DBCC SHOWCONTIG (‘Person.contact’)
go
[/cc]
If avg_fragmentation_in_percent is more than 30% it is highly fragmented and so rebuild indexes. (alter index <index name> on <table name> rebuild)
if less than 30% means moderately fragmented and so reorganize indexes. (alter index <index name> on <table name> reorganize)
If avg_page_space_used_in_percent is < 75% it means internal fragmentation
2. DMV:
[cc lang=”sql”]
–sys.dm_db_index_physical_stats – takes 3 params (databaseid, tableid, indexid)
SELECT *
FROM
sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, NULL) a
WHERE
index_id > 0
— avoid heapsORDER BY A.avg_fragmentation_in_percent DESC
–JOIN WITH sys.objects, sys.indexes, sys.partitions, index_id > 0 –TO GET actual INDEX name.
[/cc]
Another way to capture the results in SQL is
[cc lang=”sql”]
SELECT *
INTO #tbl_Fragmentation
FROM sys.dm_db_index_physical_stats (DatabaseID, TableId, IndexId, NULL, DETAILED);
[/cc]
