I have been working on building visualizations for various kinds of analysis that I perform for my customers. One such useful visualization was the use of Power View for analyzing the data available in the SQL Server Default Trace. The query below lets you retrieve all the information in the default traces. This same query is used to populate the Power Pivot table in the Excel file.
declare @enable int
-- Check to find out if Default Server Side traces are running
select top 1 @enable = convert(int,value_in_use) from sys.configurations where name = 'default trace enabled'
if @enable = 1 --default trace is enabled
declare @d1 datetime;
declare @diff int;
declare @curr_tracefilename varchar(500);
declare @base_tracefilename varchar(500);
declare @indx int ;
select @curr_tracefilename = path from sys.traces where is_default = 1 ;
set @curr_tracefilename = reverse(@curr_tracefilename)
select @indx = PATINDEX('%\%', @curr_tracefilename)
set @curr_tracefilename = reverse(@curr_tracefilename)
set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc';
select EventCat.name as Category, EventID.name as EventName, Events.*
from ::fn_trace_gettable( @base_tracefilename, default ) Events
inner join sys.trace_events EventID
on Events.EventClass = EventID.trace_event_id
inner join sys.trace_categories EventCat
on EventID.category_id = EventCat.category_id
Once I have all the trace data available in my Power Pivot table, I created calculated columns for Day, Hour and Minute. Now that I have all the data readily available for me, I went about creating the main dashboard which provides a view of all the events that occurred along with a time line view. All this took me less than 5 minutes after I had finished writing the query! Pretty quick. Now I have an interactive report that I can use for performing various kinds of analysis.
The screenshot below will show that there was only one event raised for the Server event category and the actual time of occurrence is shown in the line graph. A simple mouse over on the point will give you the exact details. Now isn’t that a simple way to track down events!
I will provide a final version of the Excel sheet once I have completed the other dashboards and sanitized the information available in the Power Pivot table.
Previous posts in this series:
PowerView and System Health Session–CPU health
PowerView and System Health Session–Scheduler Health
PowerView and System Health Session–SQL Memory Health
PowerView and System Health Session– IO Health
In the last post for this series, I had explained how to retrieve the I/O statistics from the System Health Session data. In this post, I will describe how to build a dashboard using the SYSTEM component of the sp_server_diagnostics output. This view will help DBAs track various errors which can get their blood pressure shooting to abnormal levels. The SYSTEM component tracks various errors like non-yielding conditions, latch related warnings, inconsistent pages detected and access violations for the SQL Server instance.
Armed with this information in a Power Pivot table, I created two calculated columns for DAY and HOUR on the time the event was reported. After that I created KPIs on the maximum number of non-yielding conditions, latch related warnings, inconsistent pages and access violations reported.
Now that I have my Power Pivot data, I created a new Power View sheet which tracks the created KPIs for each day and hour. The screenshot below shows the final view.
The first half is a 100% Stacked Bar graph showing the various errors that were reported each day. There is a slicer for Day available which allows to filter the data quickly.
The second half of the report is a matrix which shows the KPI status for which day with a drill-down capability for hour.
The third half of the report shows a card view with the actual number of issues reported for each event against a particular time.
As usual the Excel sheet is available on SkyDrive at: http://sdrv.ms/10O0udO
The query to fetch the data required to build this report is available below.
SET NOCOUNT ON
-- Fetch data for only SQL Server 2012 instances
IF (SUBSTRING(CAST(SERVERPROPERTY ('ProductVersion') AS varchar(50)),1,CHARINDEX('.',CAST(SERVERPROPERTY ('ProductVersion') AS varchar(50)))-1) >= 11)
-- Get UTC time difference for reporting event times local to server time
DECLARE @UTCDateDiff int = DATEDIFF(mi,GETUTCDATE(),GETDATE());
-- Store XML data retrieved in temp table
SELECT TOP 1 CAST(xet.target_data AS XML) AS XMLDATA
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'
AND xet.target_name = 'ring_buffer';
-- Parse XML data and provide required values in the form of a table
;WITH CTE_HealthSession (EventXML) AS
SELECT C.query('.') EventXML
FROM #SystemHealthSessionData a
CROSS APPLY a.XMLDATA.nodes('/RingBufferTarget/event') as T(C)
DATEADD(mi,@UTCDateDiff,EventXML.value('(/event/@timestamp)','datetime')) as [Event Time],
EventXML.value('(/event/data/text)','varchar(255)') as Component,
EventXML.value('(/event/data/value/system/@latchWarnings)','bigint') as [Latch Warnings],
EventXML.value('(/event/data/value/system/@isAccessViolationOccurred)','bigint') as [Access Violations],
EventXML.value('(/event/data/value/system/@nonYieldingTasksReported)','bigint') as [Non Yields Reported],
EventXML.value('(/event/data/value/system/@BadPagesDetected)','bigint') as [Bad Pages Detected],
EventXML.value('(/event/data/value/system/@BadPagesFixed)','bigint') as [Bad Pages Fixed]
WHERE EventXML.value('(/event/@name)', 'varchar(255)') = 'sp_server_diagnostics_component_result'
AND EventXML.value('(/event/data/text)','varchar(255)') = 'SYSTEM'
ORDER BY [Event Time];
DROP TABLE #SystemHealthSessionData
A screencast showing the CPU usage statistics of a SQL Server 2012 instance retrieved using Power Pivot. The visualization has been built using Power View in Excel 2013. The nuts and bolts of how the visualization was created is available in the following blog post: PowerView and System Health Session–CPU health
TroubleshootingSQL–CPU usage analysis with Excel 2013