WOOT: Schema Changes History Report on Power View

Posted on October 19, 2013 by Amit Banerjee

The last post in this series talked about using Power View to analyze the data stored in the SQL Server’s default trace. I decided to take this a step further by creating the Schema Changes History report with the help of the data that I retrieved from the Default Traces. The advantage of a report created in Power View is that the interactivity which is missing in the standard report is available.

The way I created this report was to filter the data in the Power Pivot table using EventClass ID 46, 47 and 164 for only looking at the create, drop and alter commands which the default trace tracks. After that I created a table with a tile on the Database Name and a 100% Stacked Bar Chart to show the activity at a database level.

I also had to create linked tables for getting the Object Type and the Event Class Name that you see in the table below.

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 Post in the Series:

Default Trace Dashboard
https://troubleshootingsql.com/2013/09/26/woot-default-trace-and-power-view/

WOOT: Default Trace and Power View

Posted on September 26, 2013 by Amit Banerjee

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
begin

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

end

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! Smile

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.

Another day at the SQL Bangalore UG Meeting

Posted on August 25, 2013 by Amit Banerjee

Another Saturday and another Bangalore SQL UG meeting completed! The turnout on a Saturday morning was really good. This time around the event was a bit different as we had a session on Photography by Vijay [T: @msigeek | B: iClickD]. This week’s User Group meeting was dedicated to SQL Server Tips and Tricks with Balmukund Lakhani [T: @blakhani | B: sqlserver-help] and me presenting on Management Studio and Power Pivot and Power View in Excel 2013.

Balmukund’s session as always was a superb hit where he demonstrated more than a fair share of SSMS tips and tricks. I am sure every one at the event learnt atleast a new thing about SSMS. There are multiple hidden options under the Tools->Options view in SSMS.

Vijay delivered a great session on photography which was very well appreciated by the audience!!

My session was around the the usage of Power View and Power Pivot in Excel 2013. I demonstrated how easy it was to build visualizations with Excel 2013 for administrators. I also demoed the Excel sheet that I had created using the System Health Session data. All my blog posts on this topic are available below.

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

PowerView and System Health Session– System

The Excel file can be downloaded from Sky Drive using the link: http://sdrv.ms/10O0udO The presentation that I used for my session is available at SlideShare and also embedded below.

PowerView and System Health Session– IO Health

Posted on July 25, 2013 by Amit Banerjee

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

The SQL Server support team does get a lot of calls regarding slow performance which on analysis leads to a slow performing disk sub-system. The IO_SUBSYSTEM component of the sp_server_diagnostics output in SQL Server 2012 tracks I/O related latch timeouts and long duration I/Os reported along with the filename and the longest pending I/O duration. This information can be very useful when looking at the trends of slow I/O reported on the SQL Server database files on an instance.

As shown earlier in the series, I used this data captured by the sp_server_diagnostics output present in the System Health Session ring buffers to build visualizations using Power Pivot and Power View in Excel 2013. The query available at the bottom of this blog post allowed me to fetch the information from the System Health Session ring buffer into a Power Pivot table.

After that I created a two calculated fields for Hour and Day using the Event Time field in the table. Then, I created two calculated fields for tracking the maximum number of Long IOs and IO Latch Timeouts reported. Then I assigned KPIs to each of these calculated fields. After that I got down to designing the Powershell sheet which finally looked like the image in the screenshot!

The slider enables you to see the KPI status for each day on an hourly basis and the table on the right gives you insights into every snapshot captured by the sp_server_diagnostics output for the hour that you are interested in.

As usual the Excel sheet is available on SkyDrive at: http://sdrv.ms/10O0udO

Query to fetch the above data 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)

BEGIN

-- 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

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'

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)

)

SELECT

DATEADD(mi,@UTCDateDiff,EventXML.value('(/event/@timestamp)[1]','datetime')) as [Event Time],

EventXML.value('(/event/data/text)[1]','varchar(255)') as Component,

EventXML.value('(/event/data/value/ioSubsystem/@ioLatchTimeouts)[1]','bigint') as [IO Latch Timeouts],

EventXML.value('(/event/data/value/ioSubsystem/@totalLongIos)[1]','bigint') as [Total Long IOs],

EventXML.value('(/event/data/value/ioSubsystem/longestPendingRequests/pendingRequest/@filePath)[1]','varchar(8000)') as [Longest Pending Request File],

EventXML.value('(/event/data/value/ioSubsystem/longestPendingRequests/pendingRequest/@duration)[1]','bigint') as [Longest Pending IO Duration]

FROM CTE_HealthSession

WHERE EventXML.value('(/event/@name)[1]', 'varchar(255)') = 'sp_server_diagnostics_component_result'

AND EventXML.value('(/event/data/text)[1]','varchar(255)') = 'IO_SUBSYSTEM'

ORDER BY [Event Time];

DROP TABLE #SystemHealthSessionData

END

Twitter Hashtag analysis using Excel 2013

Posted on March 12, 2013 by Amit Banerjee

Recently I had written a blog post on my non-SQL Server blog on an event that was being organized world wide to raise awareness to help end violence against women. As with today’s events, the social media was used to garner support and spread the word. There was even a live twitter feed that was running for the #RingTheBell hashtag!

The campaign’s name is Bell Bajao, which in Hindi literally means Ring the Bell started with showing how domestic violence can be prevented by simply ringing the door bell.

The event at Delhi took place at the British council on 8th March, 2013. I used the Tweet Archivist service to start a Tweets archive so that I could do analysis of the tweets received after the event. The archive created can be downloaded as an Excel/CSV file.

This blog post is to show how the Power View option in Excel 2013 can be utilized for performing analysis of Tweets. Once I had the tweets exported to an Excel file, I used the Power View report option to create a new Power View report. See screenshot below.

I added a bar chart, a table, a line chart and a pie chart to create a dashboard of sorts in the design area with the following properties:

1. The bar chart shows all the tweets between 28th February, 2103 to 9th March, 2013
2. The line chart shows the tweets from 6th-7th March, 2013 with a 24-hour period
3. The table shows all the users who have tweeted using this hashtag and with a tweet count of over 100.
4. The pie chart shows the percentage of tweets by each user for the period being analyzer with a tweet count over 250.

As it is clear from the above, information present in each of the four components have a different set of filters applied to them.

What makes it interesting is that the above report has interactivity built into it. So if I click on any one of the charts or table, the rest of the table/charts also change to reflect the data for the selection made. This is evident from the video at the bottom of this post.

I wanted to take this a bit further, so I created a scatter graph with the Date column as the Play axis. The graph below shows me all twitter handles that have over 150 tweets over a period of three days! This allows me to see how the Twitter accounts were sharing updates/tweets prior to the event and during the event.

The video below shows the interactivity features of the Power View report in Excel. What you can do with such rich visualizations are endless. Just by looking at certain visual representations, it is easy to draw interesting conclusions. For example, the timeline shows me that the Bell_Bajao and PixelProject twitter handles were extremely active on the day of the event. There were others who were more silent in the days running upto the event and then started live tweeting/re-tweeting throughout the event. The TOP 5 contributors on the day of the event had a major pie of the tweets that were shared on 7th March. Within a very short time, I was able to decipher trends which would have taken me a while to dig out using traditional analysis methods!

*Note that the above times are based on the US timezone (Pacific time), which is why the 8th March event activity shows up as 7th March on the timeline.

I do know of a lot of individuals who use Twitter raw data for trend analysis. So here is a quick way to get that done with Power View and Excel 2013!