patternsqlMinor
Is fragmention limited to indexes in sql server?
Viewed 0 times
sqlindexesfragmentionlimitedserver
Problem
As far as I can see with research the only thing that fragments is indexes no other objects in SQL can fragment.
I have to keep track of fragmentation within multiple databases, would it be correct to understand that I only check index fragmentation?
Can any other object fragment?
Does it need to be checked?
I have to keep track of fragmentation within multiple databases, would it be correct to understand that I only check index fragmentation?
Can any other object fragment?
Does it need to be checked?
Solution
While this probably fits better on dba stackexchange, it's kind of a grey area/borderline.
The short answer is that indexes get fragmented and need to be periodically checked and reorganized or rebuilt based on how much they have fragmented.
Going at it we find the fragmentation levels primarily through querying:
However this query also returns the index_types. Where we see the expected clustered and non clustered indexes. But also the 'HEAP' Index type, which, isn't actually an index (the object_ID also doesn't have any kind of name in the sys.indexes view).
These are for tables with no indexes at all, and thus also no primary keys (which automatically creates an index). These too can get fragmented. Because they represent simply how spread out the data is across the disk.
You should also check and 'amend' these, either simply by actually giving them say a primary key. Or, if it's desired that they have no indexes/PK. Simply by rebuilding the table. This reorganizes the data within the table as well.
A quick example of code:
There are many options that these statements can have, like padding factor. One thing to note is that REORGANIZE allows the table to stay online and other than using resources don't block users. The other options do lock users from accessing the table during the process.
The short answer is that indexes get fragmented and need to be periodically checked and reorganized or rebuilt based on how much they have fragmented.
Going at it we find the fragmentation levels primarily through querying:
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'LIMITED')However this query also returns the index_types. Where we see the expected clustered and non clustered indexes. But also the 'HEAP' Index type, which, isn't actually an index (the object_ID also doesn't have any kind of name in the sys.indexes view).
These are for tables with no indexes at all, and thus also no primary keys (which automatically creates an index). These too can get fragmented. Because they represent simply how spread out the data is across the disk.
You should also check and 'amend' these, either simply by actually giving them say a primary key. Or, if it's desired that they have no indexes/PK. Simply by rebuilding the table. This reorganizes the data within the table as well.
A quick example of code:
ALTER INDEX [PK_Foo] ON [dbo].[Foo] REORGANIZE --Generally used on low fragmentation count
ALTER INDEX [PK_Foo] ON [dbo].[Foo] REBUILD --Used for high fragmentation count
ALTER TABLE [dbo].[Bar] REBUILD --Used for HEAP indexes with medium-high fragmentationThere are many options that these statements can have, like padding factor. One thing to note is that REORGANIZE allows the table to stay online and other than using resources don't block users. The other options do lock users from accessing the table during the process.
Code Snippets
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'LIMITED')ALTER INDEX [PK_Foo] ON [dbo].[Foo] REORGANIZE --Generally used on low fragmentation count
ALTER INDEX [PK_Foo] ON [dbo].[Foo] REBUILD --Used for high fragmentation count
ALTER TABLE [dbo].[Bar] REBUILD --Used for HEAP indexes with medium-high fragmentationContext
StackExchange Database Administrators Q#251890, answer score: 4
Revisions (0)
No revisions yet.