This will return the level of fragmentation for every index in the current database:
SELECT b.NAME, [a].*
FROM sys.dm_db_index_physical_stats (DB_ID(), 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
WHERE [a].index_id <> 0
ORDER BY avg_fragmentation_in_percent desc
Connect to:
\\[ServerName]\root\MicrosoftIISv2
In the connection dialog:
If you are logged in as a user who has admin rights on the server then check ‘Login as current user’, otherwise uncheck it and enter the credentials of a user who does.
Click ‘Options>>’ button:
Set Impersonation level to ‘Impersonate’
Set Authentication level to ‘Packet Privacy’
Check ‘Enable all privileges’
Click ‘OK’ and hey presto!
