SSMS Test Index Fragmentation

According to microsoft "An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view" which holds keys taken from one or more columns of the table or view. Indexes can be seen as a list of sorts and when insert, update or delete functions are run against the underlying table the index can become fragmented (leaving gaps in sections of the list) which can cause performance issues when querying the table if it's big enough so it's a good idea to keep on top of your index fragmentation.

Some Code

The below code snippet is a query that allows you to specify a database and table/view to look at the indexes of, if these parameters were set to Null then every database and table attached to your instance will be queried for the relevant information, which can take a long time..

-- Frag Test
SELECT DB_NAME(a.[database_id]) AS [DatabaseName] ,OBJECT_NAME(a.[object_id]) AS [TableName] ,a.index_id
, b.name, avg_fragmentation_in_percent, SUM(p.rows) rows
FROM sys.dm_db_index_physical_stats 
(DB_ID(N'SystemsDB'),OBJECT_ID(N'dbo.Person'), NULL, NULL, NULL) AS a
--(DB_ID(N'SystemsDB'), Null, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
--join sys.objects o on o.object_id = b.object_id
join sys.partitions p on p.object_id = b.object_id
group by DB_NAME(a.[database_id]) ,OBJECT_NAME(a.[object_id])  ,a.index_id
, b.name, avg_fragmentation_in_percent

Ok.. So what now?

now that you've got some information on some of the indexes on the tables and views in your database it's probably worth taking a look at the larger tables by rowcount or most fragmented indexes.