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)
This DMV sys.dm_db_index_physical_stats offers lots of options.
For more details visit - MSDN
Have a nice day.
