SQL Server Index Fragmentation Monitoring

Hi,

This is the very simple monitoring command for sql server. Of course you must create a mail profile.


DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml = CAST(( SELECT Object_name(object_id) as 'td',s.name as 'td1'
,LTRIM(Str(avg_fragmentation_in_percent, 25, 3)) as 'td2'
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks2012'), NULL, NULL , NULL, 'LIMITED') d
join sysindexes s on d.object_id = s.id
and d.index_id = s.indid
and avg_fragmentation_in_percent > 30 and s.name is not null
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body =' < html >< body >< H 3>Index Fragmantetion Results< / H3 >
< table border = 1 >
< tr >
< th > Table Name < /th> Index Name < /th> Avg. Rate  < /tr >'    

SET @body = @body + @xml +'< /table>< /body>< /html>'
SET @body = REPLACE(@body,'td1','td')
SET @body = REPLACE(@body,'td2','td')

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'dbmail', -- you have to create this
    @recipients = 'mail@mail.com.tr',
@body = @body,
    @body_format ='HTML',
--@execute_query_database='AdventureWorks2012', --maybe necessary
    @subject = 'Index Monitoring' ;