I really like database monitoring. My email inbox have a constant flow of different types of alerts coming from our production servers with all kinds of information, sometimes more useful and sometimes less useful. Usually database alerts look really simple, it’s usually a plain text email saying “Prod1 Database data file on Server X is 80% used. You’d better grow it manually before some query triggers
the AutoGrowth process”.
Imagine you could have received email like
the one below. In addition to
the alert description it could have also included
the the database file growth chart over
the past 6 months. Wouldn’t it give you much more information whether
the data growth is natural or extreme? That’s truly what data visualization is for.
Believe it or not, I have sent
the graph below from SQL Server stored procedure without buying any additional data monitoring/visualization tool.
Would you like to visualize your database alerts like I do? Then like myself, you’d love
the Google Charts.
All you need to know is a little HTML and have a mail profile configured on your SQL Server instance regardless of
the SQL Server version.
First of all, I hope you know that
the sp_send_dbmail procedure has a great parameter @body_format = ‘HTML’, which allows us to send rich and colorful messages instead of boring black and white ones. All that we need is to dynamically create HTML code.
This is how, for instance, you can create a table and populate it with some data:
DECLARE @html varchar(max)
SET @html = '<html>' + '<H3><font id="Text" style='color: Green;'>Top Databases: </H3>' + '<table border="1" bordercolor="#3300FF" style='background-color:#DDF8CC' width='70%' cellpadding='3' cellspacing='3'>' + '<tr><font color="Green"><th>Database Name</th><th>Size</th><th>Physical Name</th></tr>' +
CAST( (SELECT TOP 10 td = name,'', td = size * 8/1024 ,'', td = physical_name FROM sys.master_files ORDER BY size DESC FOR XML PATH ('tr'),TYPE ) AS VARCHAR(MAX)) + '</table>'
EXEC msdb.dbo.sp_send_dbmail @recipients = '
[email protected]', @subject ='Top databases', @body = @html, @body_format = 'HTML'
This is
the result:
If you want to add more visualization effects, you can use Google Charts Tools https://google-developers.appspot.com/chart/interactive/docs/index which is a free and rich library of data visualization charts, they’re also easy to populate and embed.
There are two versions of
the Google Charts
Image based charts: https://google-developers.appspot.com/chart/image/docs/gallery/chart_gall
This is an old version, it’s officially deprecated although it will be up for a next few years or so. I really enjoy using this one because it can be viewed within
the email body. For mobile devices you need to change
the “Load remote images” property in your email application configuration.
Charts based on JavaScript classes: https://google-developers.appspot.com/chart/interactive/docs/gallery
This API is newer, with rich and highly interactive charts, and it’s much more easier to understand and configure.
The only downside of it is that they cannot be viewed within
the email body. Outlook, Gmail and many other email clients, as part of their security policy, do not run any JavaScript that’s placed within
the email body. However, you can still enjoy this API by sending
the report as an email attachment.
Here is an example of
the old version of Google Charts API, sending
the same top databases report as in
the previous example but instead of a simple table, this script is using a pie chart right from
the T-SQL code
DECLARE @html varchar(8000)
DECLARE @Series varchar(800),@Labels varchar(8000),@Legend varchar(8000); SET @Series = ''; SET @Labels = ''; SET @Legend = '';
SELECT TOP 5 @Series = @Series + CAST(size * 8/1024 as varchar) + ',', @Labels = @Labels +CAST(size * 8/1024 as varchar) + 'MB'+'|', @Legend = @Legend + name + '|' FROM sys.master_files ORDER BY size DESC
SELECT @Series = SUBSTRING(@Series,1,LEN(@Series)-1), @Labels = SUBSTRING(@Labels,1,LEN(@Labels)-1), @Legend = SUBSTRING(@Legend,1,LEN(@Legend)-1)
SET @html = '<H3><font color="Green"> '+@@ServerName+' top 5 databases : </H3>'+ '<br>'+ '<img src="http://chart.apis.google.com/chart?'+ 'chf=bg,s,DDF8CC&'+ 'cht=p&'+ 'chs=400x200&'+ 'chco=3072F3|7777CC|FF9900|FF0000|4A8C26&'+ 'chd=t:'+@Series+'&'+ 'chl='+@Labels+'&'+ 'chma=0,0,0,0&'+ 'chdl='+@Legend+'&'+ 'chdlp=b"'+ 'alt="'+@@ServerName+' top 5 databases" />' EXEC msdb.dbo.sp_send_dbmail @recipients = '
[email protected]', @subject = 'Top databases', @body = @html, @body_format = 'HTML'
This is what you get. Isn’t it great?
Chart parameters reference:
chf Gradient fill bg - backgroud ; s- solid cht chart type ( p - pie) chs chart size width/height chco series colors chd chart data string 1,2,3,2 chl pir chart labels a|b|c|d chma chart margins chdl chart legend a|b|c|d chdlp chart legend text b - bottom of chart
Line graph implementation is also really easy and powerful
DECLARE @html varchar(max) DECLARE @Series varchar(max) DECLARE @HourList varchar(max)
SET @Series = ''; SET @HourList = '';
SELECT @HourList = @HourList + SUBSTRING(CONVERT(varchar(13),last_execution_time,121), 12,2) + '|' , @Series = @Series + CAST( COUNT(1) as varchar) + ',' FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text(plan_handle) t WHERE last_execution_time > = getdate()-1 GROUP BY CONVERT(varchar(13),last_execution_time,121) ORDER BY CONVERT(varchar(13),last_execution_time,121)
SET @Series = SUBSTRING(@Series,1,LEN(@Series)-1) SET @html = '<img src="http://chart.apis.google.com/chart?'+ 'chco=CA3D05,87CEEB&'+ 'chd=t:'+@Series+'&'+ 'chds=1,350&'+ 'chdl= Proc executions from cache&'+ 'chf=bg,s,1F1D1D|c,lg,0,363433,1.0,2E2B2A,0.0&'+ 'chg=25.0,25.0,3,2&'+ 'chls=3|3&'+ 'chm=d,CA3D05,0,-1,12,0|d,FFFFFF,0,-1,8,0|d,87CEEB,1,-1,12,0|d,FFFFFF,1,-1,8,0&'+ 'chs=600x450&'+ 'cht=lc&'+ 'chts=FFFFFF,14&'+ 'chtt=Executions for from' +(SELECT CONVERT(varchar(16),min(last_execution_time),121) FROM sys.dm_exec_query_stats WHERE last_execution_time > = getdate()-1) +' till '+ +(SELECT CONVERT(varchar(16),max(last_execution_time),121) FROM sys.dm_exec_query_stats) + '&'+ 'chxp=1,50.0|4,50.0&'+ 'chxs=0,FFFFFF,12,0|1,FFFFFF,12,0|2,FFFFFF,12,0|3,FFFFFF,12,0|4,FFFFFF,14,0&'+ 'chxt=y,y,x,x,x&'+ 'chxl=0:|1|350|1:|N|2:|'+@HourList+'3:|Hour&'+ 'chma=55,120,0,0" alt="" />'
EXEC msdb.dbo.sp_send_dbmail @recipients = '
[email protected]', @subject ='Daily number of executions', @body = @html, @body_format = 'HTML'
Chart parameters reference:
chco series colors chd series data chds scale format chdl chart legend chf background fills chg grid line chls line style chm line fill chs chart size cht chart type chts chart style chtt chart title chxp axis label positions chxs axis label styles chxt axis tick mark styles chxl axis labels chma chart margins
If you don’t mind to get your charts as an email attachment, you can enjoy
the Java based Google Charts which are even easier to configure, and have much more advanced graphics. In
the example below,
the sp_send_email procedure uses
the parameter @query which will be executed at
the time that sp_send_dbemail is executed and
the HTML result of this execution will be attached to
the email.
DECLARE @html varchar(max),@query varchar(max) DECLARE @SeriesDBusers varchar(800); SET @SeriesDBusers = '';
SELECT @SeriesDBusers = @SeriesDBusers + ' ["'+DB_NAME(r.database_id) +'", ' +cast(count(1) as varchar)+'],' FROM sys.dm_exec_requests r GROUP BY DB_NAME(database_id) ORDER BY count(1) desc;
SET @SeriesDBusers = SUBSTRING(@SeriesDBusers,1,LEN(@SeriesDBusers)-1)
SET @query = ' PRINT '' <html> <head> <script type="text/javascript" src="https://www.google.com/jsapi"></script> <script type="text/javascript"> google.load("visualization", "1", {packages:["corechart"]}); google.setOnLoadCallback(drawChart); function drawChart() { var data = google.visualization.arrayToDataTable([ ["Database Name", "Active users"], '+@SeriesDBusers+' ]); var options = { title: "Active users", pieSliceText: "value" }; var chart = new google.visualization.PieChart(document.getElementById("chart_div")); chart.draw(data, options); }; </script> </head> <body> <table> <tr><td> <div id="chart_div" style='width: 800px; height: 300px;'></div> </td></tr> </table> </body> </html> '''
EXEC msdb.dbo.sp_send_dbmail @recipients = '
[email protected]', @subject ='Active users', @body = @html, @body_format = 'HTML', @query = @Query, @attach_query_result_as_file = 1, @query_attachment_filename = 'Results.htm'
After opening
the email attachment in
the browser you are getting this kind of report:
In fact,
the above is not only for database alerts. It can be used for applicative reports if you need high levels of customization that you cannot achieve using standard methods like SSRS.
If you need more information on how to customize
the charts, you can try
the following:
Image Based Charts wizard https://google-developers.appspot.com/chart/image/docs/chart_wizard
Live Image Charts Playground https://google-developers.appspot.com/chart/image/docs/chart_playground
Image Based Charts Parameters List https://google-developers.appspot.com/chart/image/docs/chart_params
Java Script Charts Playground https://code.google.com/apis/ajax/playground/?type=visualization
Use
the above examples as a starting point for your procedures and I’d be more than happy to hear of your implementations of
the above techniques.
Yours,
Maria
