How to identify Index Fragmentation?

If you want to find out fragmentation of index your database you have several options. In my case is the best Dynamic Management View "sys.dm_db_index_physical_stats". This system view returns informations about fragmantation in percent, types of index, number of data pages and etc. This DMV can help to build a regular index maintenance.

Syntax is very simple, just use select from the system view:

The result looks like this:

(click on the picture for max size)

dm_db_index_physical_stats

This DMV sys.dm_db_index_physical_stats offers lots of options.

For more details visit - MSDN

Have a nice day.