-
Notifications
You must be signed in to change notification settings - Fork 12
Expand file tree
/
Copy pathMailFragmentation.sql
More file actions
95 lines (83 loc) · 3.35 KB
/
Copy pathMailFragmentation.sql
File metadata and controls
95 lines (83 loc) · 3.35 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
--###################################################################################################
-- Sending an mail with all indexes > THRESHOLD_PAGES pages and >= THRESHOLD_FRAGMENTATION % fragmentation
--
--###################################################################################################
DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
DECLARE @THRESHOLD_PAGES INT
DECLARE @THRESHOLD_FRAGMENTATION INT
DECLARE @EmailTo VARCHAR(500)
--###################################################################################################
--
-- Configuration Start
--
--###################################################################################################
SET @THRESHOLD_PAGES = 100
SET @THRESHOLD_FRAGMENTATION = 5
SET @EmailTo = 'John.Doe@xyz.com;Jennifer.Doe@xyz.com'
--###################################################################################################
--
-- Configuration End
--
--###################################################################################################
SET @subject = db_name() + ' indexes > ' + CAST(@THRESHOLD_PAGES AS varchar(10)) + ' pages and >= ' + CAST(@THRESHOLD_FRAGMENTATION AS varchar(3)) + '% fragmentation'
SET @tableHTML =
N'<style type="text/css">
h3
{
font-family: Helvetica !important;
font-size: 18px !important;
text-align: left;
}
table {
color: #333;
font-family: Helvetica, Arial, sans-serif;
border-collapse:
collapse; border-spacing: 0;
}
td, th { border: 1px solid #CCC; height: 25px; padding: 5px;}
th {
background: #F3F3F3;
font-weight: bold;
}
td {
background: #FAFAFA;
text-align: center;
padding: 2px;
}
tr:nth-child(even) td { background: #F1F1F1; }
tr:nth-child(odd) td { background: #FEFEFE; }
tr td:hover { background: #888; color: #FFF; }
</style>'+
N'<H3>' + db_name() + ' indexes > ' + CAST(@THRESHOLD_PAGES AS varchar(10)) + ' pages and >= ' + CAST(@THRESHOLD_FRAGMENTATION AS varchar(3)) + '% fragmentation' +
N' (time ' + CONVERT(char(19), GetDate(),121) + '):</H3>' +
N'<table id="box-table" >' +
N'<tr>
<th>Index</th>
<th>avg_fragmentation_in_percent</th>
<th>avg_fragment_size_in_pages</th>
<th>page_count</th>
<th>fill_factor</th>
</tr>' +
CAST ( (
SELECT td = CAST(dbschemas.name + '.' + dbtables.name + ' ' + ISNULL(dbindexes.name, 'HEAP') AS VARCHAR(100)),'',
td = CAST(indexstats.avg_fragmentation_in_percent AS VARCHAR(100)),'',
td = CAST(indexstats.avg_fragment_size_in_pages AS VARCHAR(100)),'',
td = indexstats.page_count,'',
td = dbindexes.fill_factor,''
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables ON dbtables.object_id = indexstats.object_id
INNER JOIN sys.schemas dbschemas ON dbtables.schema_id = dbschemas.schema_id
INNER JOIN sys.indexes AS dbindexes ON dbindexes.object_id = indexstats.object_id
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
AND indexstats.page_count > @THRESHOLD_PAGES AND indexstats.avg_fragmentation_in_percent >= @THRESHOLD_FRAGMENTATION
ORDER BY indexstats.avg_fragmentation_in_percent DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'
EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailTo,
@subject = @subject,
@body = @tableHTML,
@body_format = 'HTML' ;