Untitled UI logotext
Solutions
WebsitesEcommerceMobile AppsWeb AppsProduction Support & Maintenance
Our work
Company
About usBlogPodcastContact us
Book a free consultation

How do I find index fragmentation in a SQL Server database?

Olivia Rhye
How do I find index fragmentation in a SQL Server database?

I'm running into index issues on SQL Server and wanted to figure out index fragmentation to see if we could improve it. Here's a way to find index fragmentation:

But, I was running into an issue because my database is running in SQL 80 mode (SQL Server 2000). The following fixes this issue.

It seems that in SQL 80 mode, you can't use the output of functions directly, so storing it in a variable first does the trick.

The output of this method shows internal and external fragmentation levels:

External Fragmentation
External fragmentation occurs when on disk, the physical storage of contents is non-contiguous. This result represents the average amount of fragmentation on an index. The lower this number is, the better. Any result over 10% should be dealt with.

Internal Fragmentation
Internal fragmentation occurs when records are stored non-contiguously inside the page. In other words, there is space between records in the index. This can happen as a table is modified (INSERT, UPDATE, DELETE) without rebuilding the index. This result represents the average amount of page storage consumed in a given index. The higher the result, the better.

Ready to start a project?

Book a free consultation
Untitled UI logotext
Our work
About us
Blog
Careers
Submit a ticket
Agency Partnerships
Contact
© 2024 fjorge. All rights reserved.
Privacy