Advertisement

Check SQL Server Index Fragmentation

Share the blog:

What Is Index Fragmentation?

Fragmentation is a common problem in SQL Server databases, as it can lead to slow query performance, long transaction times, and an increase in data maintenance. Understanding and managing index fragmentation is an important part of maintaining your database. This article will discuss how to find index fragmentation and how to measure and monitor fragmentation, and how to defragment indexes.

Generally, to monitor for fragmentation, we should use the sys.dm_db_index_physical_stats DMV (Dynamic Management View).

Here are few simple queries using the above-mentioned DMV and some additional conditions that will list every index on every table in your database, ordered by the percentage of index fragmentation. This query will work on SQL2K5 or newer.

The output will look like this:

Source: myadventuresincoding.wordpress.com

===== Or ======

The same above-mentioned DMV and some additional conditions will list every index on every table in your database about the fragmentation level. Depending on the size of the database it may take some time to execute.

The output will look like this:

Advertisement

With the above-mentioned DMV and some additional conditions, we can find only the fragmentation level of a table, we may also use the below query.

The output will look like this:

10 SQL Server Index Fragmentation Best Practices

Facebook Comments Box
Share the blog:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Advertisement