How to check the Index Fragmentation in SQL Server?


Step 1: Launch SQL Server Management Studio.

Step 2: In the object explorer, right click on the database and select Reports -> Standard Reports -> Index Physical Statistics.

Step 3: SQL Server Management Studio will generate a report showing information about the Table Names, Index Names, Index Type, Number of Partitions and Operation Recommendations.

Step 4: Repeat the above steps to check the fragmentation of all user databases.

 

One key value that is provided in the report is the Operation Recommended field. Any value of Rebuild is an indication that the index is fragmented.

By expanding the # Partitions field, you can see the % of fragmentation for a given index.

 

Report looks like below.

Advertisements

3 thoughts on “How to check the Index Fragmentation in SQL Server?

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s