Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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:
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 fragmentation
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.
It depends on what you mean by "other objects".
Tables with clustered indexes: the clustered index is the table data so that can fragment as with any other index
Tables without a clustered index (heaps): the heap structure can also fragment, and is potentially prone to other issues too depending on use patterns (such as space/efficiency lost over time+use due to forward records, see https://www.brentozar.com/archive/2016/07/fix-forwarded-records/)
Stored procedures, views, etc: the definition text for these is stored as NVARCHAR(MAX) values so they are off-page and if longer than ~8kb (~4K characters, as they are stored two-bytes-per-char) they can potentially fragment, but this is not going to be significant.
I am going to assume you are a fairly new DBA. In addition to the two existing answers, there maybe a couple of related things you should look at.
For larger database performance the number of datafiles is appropriate for the number of processors you have. I go into some detail about it in my answer at Split .mdf datafile methods
You should only have one log file, but it is subdivided into VLF files, as a DBA you have some control over how this is handled. There is a fairly deep look at VLFs in the post Why are virtual log files not always allocated in order?
Heaps can be "fragmented" in two ways. All depends on how you define "fragmented":
Empty space on ages after deleting rows. And even pages not being deallocated even when empty. I had a case where a SELECT on a table with just 3000 rows took several minutes!
Forwarded records (aka forwarding pointers). I have some info on these here: https://karaszi.com/rebuild-all-fragmented-heaps
Note that you won't see this of you use sys.dm_db_index_physical_stats with NULL for the last parameter, since NULL means LIMITED. You need either DETAILED or SAMPLED.
