Getting Performance Metrics

To get the information on File level fragmentation - Use following commands
Following command will analyze the file level fragmentation -

Defrag -a

Same defrag command can be used to defragment files.

To find missing statistics run below query - SELECT O.name AS [table name], I.rowmodctr AS [Rows Modified] FROM SYSOBJECTS o JOIN SYSINDEXES I ON O.id = I.id WHERE I.rowmodctr > 0 and O.xtype = 'U' ORDER BY I.rowmodctr DESC

Following the query to get report for Index defrag scan percentage from 10 to 99% for various indexes.

use
GO
Select [Object_id] AS ObjectId,
index_id AS IndexId,
avg_fragmentation_in_percent AS FragPercent,
partition_number AS PartitionNumber,
index_type_desc AS IndexType,
alloc_unit_type_desc AS AllocUnitType,
0 AS Online
FROM sys.dm_db_index_physical_stats (db_id(''), NULL, NULL, NULL, 'LIMITED')
WHERE
avg_fragmentation_in_percent > 10.0 AND
index_id > 0 and page_count > 32
ORDER BY ObjectId