Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

i-catching solutions
Browse by Tags · View All
SQL Server 26
Script toolbox 16
T-SQL 12
SQL 12
Geniiius 8
SQL Server 2012 7
Performance optimization 6
DMV 5
debugging 5
Debugging 5

Archive · View All
November 2011 6
May 2012 4
April 2012 4
January 2012 4
February 2012 3
March 2012 3
October 2011 2
December 2011 2
September 2012 1
June 2012 1

Geniiius's Blog

Visualize Wait stats with Microsoft Excel

Apr 18 2012 12:00AM by Geniiius   

In our blog post last week: Instance Wide Wait Stats we promised to show You how to visualize the collected wait stats with Microsoft Excel, so this is what this blog post will be about.

The blog post from last week will be the basic setup for this one, so the steps to create the table and to collect the data will not be repeated in this one. If you need them you’ll have to go back and get it.

Today I’ll encapsulate the TSQL logic in a view, this is not necessary, but it makes it much easier from Excel to connect to a predefined view rather than having to write the TSQL. I will call the view for V_WaitStats, and here is the code to create it:

CREATE VIEW V_WaitStats
AS

WITH cte AS
(
SELECT
    id, wait_type, wait_time_ms, SampleTime,
    ROW_NUMBER() OVER(PARTITION BY wait_type ORDER BY SampleTime) as rn
FROM InstanceWideWaitStats
)
SELECT
 t1.wait_type,
 t2.wait_time_ms - t1.wait_time_ms as WaitTimeInMS,
 t2.SampleTime
FROM cte t1
INNER JOIN cte t2 on (t1.wait_type = t2.wait_type) and (t1.rn = t2.rn -1)
WHERE t2.wait_time_ms - t1.wait_time_ms <> 0

 

What I want to do next is connect to my SQL Server View from Excel, I am no way an Excel Guru, but this is so easy, and such a powerful tool at visualize data like wait stats. Here is some screenshots from the process.

image

First step is to enter the name of the SQL Server where the data is stored

image

Then is it time to select database and the View that we created earlier in this blog post. And the in the end it is just a matter of choosing the right option.

image

And then just hit the OK button. Then it is time to show some Pivot Table magic, Excel gives us so many opportunities to play around with the Wait Stats data, further down you’ll find some examples that shows some of them. Selecting a subset of wait types or periods is easy with Excel, and the Graph changes straight away.

image

image

I use this quite often, and the information that You get with this is very useful. This solution that we have described in these two blog posts, should be in every DBA’s toolbox.


Republished from geniiius.com [45 clicks].  Read the original version here [2 clicks].

Geniiius
134 · 1% · 369
3
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Visualize Wait stats with Microsoft Excel" rated 5 out of 5 by 3 readers
Visualize Wait stats with Microsoft Excel , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]