Pro Members, SQL Server, Standard Members

System Health Session Dashboard













System Health Session Dashboard

I had written a series on how to create reports based on the system health session for SQL Server 2008 and above instances in the recent past. I got a lot of positive feedback regarding the reports which got me thinking on how to make this available using the new features of SQL Server 2008 R2 Reporting Services. Since the existing set of reports which I had published could be viewed from SQL Server Management Studio using the custom reports option, the reports had to conform to SQL Server 2005 Report schema. This in turn meant that I couldn’t make use of the new controls or cascading parameters features for these reports. This is the entire reason why I am starting a new series wherein you can use the solution mentioned to deploy to a SQL Server 2008 R2 Reporting Services instance and view the system health session reports for all the SQL Server 2008 and above instances in your environment.

The way the dashboard main report works is that it uses a variable (vServerName) which will store the list of server names that you want to monitor with the help of this solution. A screen shot of the parameter properties is shown below.


The dataset used in the report uses a dynamic connection string where the server name is determined based on the parameter value of vServerName. The connection string is as follows:

="Data Source=" + Parameters!vServerName.Value.ToString() + ";Initial Catalog=master"

A screenshot of the dashboard report is shown below for a default instance:



The event name column and the pies in the pie chart have drill-through options. I have added a calculation in the event time fields so that timestamp shown reflects the time when the event was reported on the server and not the UTC time when the event was reported.

The system health session uses a ring buffer target so the information stored about the events tracked by this session don’t stick around forever much like the SQL Server default trace. Note that if you have an event session setup for a ring_buffer target and the data you feed the target exceeds 4Mb, you may not be able to retrieve all XML nodes from the target data. Bob Ward mentioned about this in detail in his blog post. So the System_Health session also suffers from this drawback. Jonathan (Blog | Twitter) dropped me an email stating that the above script could fall prey to incorrect timestamp issue. This issue is already filed as a Connect item. Jonathan described on a viable workaround for this solution in his blog post.

Remember that the target of the extended events session is a ring buffer. If your server instance have been running for a long period of time and events are continuously being populated into the ring buffer storing the system health session data, then data will be overwritten.

Query used for generating the above report:

SET NOCOUNT ON

IF (SUBSTRING(CAST(SERVERPROPERTY (‘ProductVersion’) AS varchar(50)),1,CHARINDEX(‘.’,CAST(SERVERPROPERTY (‘ProductVersion’) AS varchar(50)))-1) = 10)

BEGIN

DECLARE @UTDDateDiff int

SET @UTDDateDiff = DATEDIFF(mi,GETUTCDATE(),GETDATE())

— Store the XML data in a temporary table

SELECT CAST(xet.target_data as xml) as XMLDATA

INTO #SystemHealthSessionData

FROM sys.dm_xe_session_targets xet

JOIN sys.dm_xe_sessions xe

ON (xe.address = xet.event_session_address)

WHERE xe.name = ‘system_health’

— Get the different events reported in a grouped manner

;WITH CTE_HealthSession AS

(

SELECT C.query(‘.’).value(‘(/event/@name)[1]’, ‘varchar(255)’) as EventName,

C.query(‘.’).value(‘(/event/@timestamp)[1]’, ‘datetime’) as EventTime

FROM #SystemHealthSessionData a

CROSS APPLY a.XMLDATA.nodes(‘/RingBufferTarget/event’) as T(C))

SELECT EventName,

COUNT(*) as Occurrences,

DATEADD(mi,@UTDDateDiff,MAX(EventTime)) as LastReportedEventTime,

DATEADD(mi,@UTDDateDiff,MIN(EventTime)) as OldestRecordedEventTime

FROM CTE_HealthSession

GROUP BY EventName

ORDER BY 2 DESC

— Drop the temporary table

DROP TABLE #SystemHealthSessionData

END

SET NOCOUNT OFF

The UTC Time Difference was a calculated using the T-SQL query below:

DECLARE @UTDDateDiff int

SET @UTDDateDiff = DATEDIFF(mi,GETUTCDATE(),GETDATE())

IF (@UTDDateDiff > 0)

SELECT (‘UTC Time Difference: ‘ + CAST((@UTDDateDiff/60) AS char(3)) + ‘ hours ‘ + CAST((@UTDDateDiff%60) AS char(3)) + ‘ minutes’) AS UTCString

ELSE

SELECT (‘UTC Time Difference: -‘ + SUBSTRING(CAST((@UTDDateDiff/60) AS char(3)),2,3) + ‘ hours ‘ + SUBSTRING(CAST((@UTDDateDiff%60) AS char(3)),2,3) + ‘ minutes’) AS UTCString

This is just the tip of the iceberg. I shall discuss in the next few posts on how to build a complete solution along with the report files for this solution. Stay tuned!

System Health Session
http://msdn.microsoft.com/en-us/library/ff877955(sql.110).aspx
Jonathan’s post on System Health Session
http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/13/an-xevent-a-day-13-of-31-the-system-health-session.aspx

Facebooktwittergoogle_plusredditpinterestlinkedinmail