Email Alert

Posted by Barac in SQL Server, SQL Tips and Tricks on Feb 15th, 2018 | 1 comment

This is example of missing indexes of database within email alert, we already had example how you can use sp_send_dbmail stored procedures from msdb database to send backup size email daily, and this example is just to show use case with different scripts.

I used following arguments to achieve this

@profile_name (name of an existing Database Mail profile, type sysname, default NULL),

@recipients (Is a semicolon-delimited list of e-mail addresses to send the message to, varchar(max)),

@body (Is the body of the e-mail message, nvarchar(max)),

@subject (Is the subject of the e-mail message, nvarchar(255)),

@body_format (Is the format of the message body, varchar(20))

More information about this system stored procedure you can find here sp_send_dbmail (Transact-SQL)

DECLARE
@emailSubject varchar(100),
@textTitle varchar(100),
@tableHTML nvarchar(max),
@body nvarchar(max)
SET @emailSubject = 'DB MISSING INDEX SUGGESTION'
SET @textTitle = 'Missing Index Suggestion For DBName'
SET @tableHTML =
'<html>'+
'<head><style>'+
-- Data cells styles / font size etc
'td {border:1px solid #ddd;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:10pt}'+
'</style></head>'+
'<body>'+
-- TextTitle style
'<div style="margin-top:15px; margin-left:15px; margin-bottom:15px; font-weight:bold; font-size:13pt; font-family:calibri;">' + @textTitle +'</div>' +
-- Color and columns names
'<div style="font-family:Calibri; "><table>'+'<tr bgcolor=#00881d>'+
'<td align=left><font face="calibri" color=White><b>Impact</b></font></td>'+ -- Index Impact
'<td align=left><font face="calibri" color=White><b>Usecount</b></font></td>'+ -- Usecount
'<td align=left><font face="calibri" color=White><b>Table Name</b></font></td>'+ -- Table Name
'<td align=left><font face="calibri" color=White><b>Equality Columns</b></font></td>'+ -- Equality Columns
'<td align=left><font face="calibri" color=White><b>Inequality Columns</b></font></td>'+  -- Inequality Columns
'<td align=left><font face="calibri" color=White><b>Include Columns</b></font></td>'+ -- Include Columns
'</tr></div>'

----------------------------------------------------------
----- Querying the plan cache for missing indexes --------
----------------------------------------------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
MissingIndexesPerTable AS (
SELECT
cached_plans.usecounts,
n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS index_impact,
REPLACE(n.query('for $group in //ColumnGroup for $column in $group/Column where $group/@Usage="EQUALITY" return string($column/@Name)').value('.', 'varchar(max)'),'] [', '],[') AS equality_columns,
REPLACE(n.query('for $group in //ColumnGroup for $column in $group/Column where $group/@Usage="INEQUALITY" return string($column/@Name)').value('.', 'varchar(max)'),'] [', '],[') AS inequality_columns,
REPLACE(n.query('for $group in //ColumnGroup for $column in $group/Column where $group/@Usage="INCLUDE" return string($column/@Name)').value('.', 'varchar(max)'),'] [', '],[') AS include_columns,
n.value('(//MissingIndex/@Table)[1]', 'sysname') AS [table_name]
FROM sys.dm_exec_cached_plans cached_plans (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(cached_plans.plan_handle) query_plans
CROSS APPLY query_plan.nodes('//StmtSimple') AS nodes(n)
WHERE cached_plans.cacheobjtype = 'Compiled Plan'  and n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT')>30 and cached_plans.usecounts>50
AND query_plans.query_plan.exist('//MissingIndex')=1)
----------------------------------------------------------
----------------------------------------------------------

SELECT @body =(
SELECT DISTINCT td = CONVERT(DECIMAL(16,2), index_impact),
td = usecounts,
td = table_name,
td = equality_columns,
td = inequality_columns,
td = include_columns
FROM
MissingIndexesPerTable
ORDER BY CONVERT(DECIMAL(16,2), index_impact) desc
for XML raw('tr'), elements)

SET @body = REPLACE(@body, '<td>', '<td align=left><font face="calibri">')
SET @tableHTML = @tableHTML + @body + '</table></div></body></html>'
SET @tableHTML = '<div style="color:Black; font-size:8pt; font-family:Calibri; width:auto;">' + @tableHTML + '</div>'

exec msdb.dbo.sp_send_dbmail
@profile_name = 'DBEmailProfileName',
@recipients = 'name.surname@youremail.com',
@body = @tableHTML,
@subject = @emailSubject,
@body_format = 'HTML'

Your email result should be similar to

One Response to “ “SQL Server / Missing Indexes Query / Cached Plans / Email Alert”

Bt Unp says:

June 22, 2018 at 11:12 am

This helps. Cheers!

Reply

About Me

My name is Zoran, currently living in Auckland, New Zealand. I am a data specialist with more than 15 years of hands-on experience in database administration and optimisation. I am also, Certified Microsoft Trainer (MCT) and Microsoft Certified Solutions Expert (MCSE) with a Master‘s degree in Information Technology. Sharing knowledge and contributing to the SQL Server community is my passion. I am also an organiser of the Auckland SQL User Meetup Group. As well I am active blogger and speaker at different SQL events such as SQL Saturdays, Meetups etc.

SQL Server / Missing Indexes Query / Cached Plans / Email Alert

Similar Posts:

One Response to “ “SQL Server / Missing Indexes Query / Cached Plans / Email Alert”

Leave a Reply Cancel reply

About Me

Email Subscription

Index Usage Report

Auckland SQL Group

MSDN PROFILE

Archives

SQL SERVER UPDATES

Recent Posts

Tag Cloud