Project Perfmon

I recently came across this great poster from the good people at Quest. It provides a huge list of all the relevant Performance Monitor metrics that you should be looking at along with what they mean and what values you should be looking out for.

PerfMonPosterThumb

I duly printed this off on a nice A3 page, started perusing and then realised that i would really need to work up examples for each of the Counters if i was to truly understand them. Thus the Perfmon Project idea was born, i thought, what better way to learn them all than to work through them, top to bottom, left to right and blog my findings.

Please note, this is in no way an attempt to plagiarise the great work from quest or by the authors, it is an effort by me to understand each of these items and build a worked example that i can refer back to at a later date. In each case i will ensure that i fully reference any resources/blogs/pages that i have used to come up with my interpretation. Lets look at it as standing on their shoulders to enjoy the view 🙂

For your own copy, head over to Quest and if you have a spare moment be sure to check out Kevin Klines Blog and dont forget to follow him on twitter

My plan is to post a new item every three weeks or so, as i want to fully understand the material instead of just pumping out posts…..so, on with the project.

In case anyone is wondering, I have contacted Kevin about this project idea and he is more than happy for me to use the poster in posts, thanks Kevin.

Categories: Performance Monitor, SQL | Tags: , , | Leave a comment

SDU Code Camp Material

This is just a short note to let those that are looking for the material from my SQL Code Camp 09 presentation on SSIS debugging know that it will be placed here during this week.

I will break down the presentation into separate posts on Breakpoints, Data Viewer and Error Redirection. There will also be a short post on extracting information from the filename and storing this into a variable (one of the tasks in the package) at the request of one of the attendees.

Finally, thanks to Rob Farley for his support and tips on my presentation and to Greg Low for giving me the opportunity to present.

Categories: Code Camp | Leave a comment

A Storm is brewing, no wait, its SQL in the cloud

SQL Data Services (SDS) has been available as a cloud service but until now we have been told it is NOT SQL Server in the cloud and is not to be seen as a RDBMS. There has since been a big change to that with the announcement that SDS will be enchanced with TDS, yay another TLA, TDS = Tabular Data Stream, or, a full RBBMS in the cloud.

The SQL Data Services Team blog lists, among other features:
Tables?…Check

Stored Procedures?…Check

Triggers?…Check

Views?…Check

Indexes?…Check

Visual Studio Compatibility?…Check

ADO.Net Compatibility?…Check

ODBC Compatibility?…Check

so there we have it, apparently you can just point your connection string towards the gathering clouds and thats it. Time to run a demo on this i think, coming soon….

The full announcment is available here .

Categories: SDS, SQL, SQL in the cloud, Tabular Data Stream, TDS | Tags: , , , , | Leave a comment

Rank makes things less rank

A query that has popped up a few times is to retrieve a list of people along with the first/last/top sale that they have made. If we were after the last sale ever made, this query would be simple, utilising top we would order by the relevant column and get the top 1, done. However in this case we want the top item PER person, not per the entire set, this is where Rank() comes into play.

Using Rank we can avoid iterating through our data with cursors to achieve this query in a single set operation.

Lets use adventureWorks and retrieve the last sale date made by each of our salespeople.

Our first step is to get all of our sales people and the orders they made:

select 
SOH.OrderDate,
SOH.SalesPersonID,
vEmp.FirstName + ' ' + vEmp.LastName Employee
from
Sales.SalesOrderHeader SOH
inner join Sales.SalesPerson SP on SOH.SalesPersonID = SP.SalesPersonID
inner join HumanResources.vEmployee vEmp on SP.SalesPersonID = vEmp.EmployeeID

Now we would like to provide away to determine which OrderDate is the latest, per SalesPerson. This is where rank comes into play:

select 
SOH.OrderDate,
SOH.SalesPersonID,
vEmp.FirstName + ' ' + vEmp.LastName Employee,
rank() over(Partition by SOH.SalesPersonID order by OrderDate desc) OrderRank
from
Sales.SalesOrderHeader SOH
inner join Sales.SalesPerson SP on SOH.SalesPersonID = SP.SalesPersonID
inner join HumanResources.vEmployee vEmp on SP.SalesPersonID = vEmp.EmployeeID

the key section is:

rank() over(Partition by SOH.SalesPersonID order by OrderDate desc) OrderRank

this now gives us a machanism for determining, per SalesPerson, which order was the latest, it will be the order with a OrderRank of 1.

Breaking down the function, the key parts are the partition and the order by:
Partition:
Patition is analogous with ‘Group By’ and is used to literally Partition our dataset to allow for multiple ordering withing the one dataset, Partitioning by the SalesPersonID will let us provide subsequent ranking based on each ID
Order By
Order By is the item which will determine our ranking and is based on the set of data provided by the partition

However, this still does not give us the Last order as per our request, to get the last order, we now filter the result set by OrderRank, limiting it only to those rows with a rank of 1, giving us the last order Per SalesPerson.

As Rank() is a derived column, we must select from this result set and filter the subset:

select
OrderDate, SalesPersonID, Employee
from
(
select
SOH.SalesOrderID,
SOH.OrderDate,
SOH.SalesPersonID,
vEmp.FirstName + ' ' + vEmp.LastName Employee,
rank() over(Partition by SOH.SalesPersonID order by OrderDate desc) OrderRank
from
Sales.SalesOrderHeader SOH
inner join Sales.SalesPerson SP on SOH.SalesPersonID = SP.SalesPersonID
inner join HumanResources.vEmployee vEmp on SP.SalesPersonID = vEmp.EmployeeID
) RankedOrders
where
RankedOrders.OrderRank = 1

Now we nearly have the last sale date of our salesperson. as we can see from the result, any items in the ranking which have the same OrderDate are given a ranking of 1, therefore we get 2 results for Stephen Jiang as he completed two order on June 1. Depending on your reporting requirements you can handle this with a more selective ranking order or in our case simply using distinct as we require the sales person and the last sale date.

select distinct
OrderDate, SalesPersonID, Employee
from
(
select
SOH.SalesOrderID,
SOH.OrderDate,
SOH.SalesPersonID,
vEmp.FirstName + ' ' + vEmp.LastName Employee,
rank() over(Partition by SOH.SalesPersonID order by OrderDate desc) OrderRank
from
Sales.SalesOrderHeader SOH
inner join Sales.SalesPerson SP on SOH.SalesPersonID = SP.SalesPersonID
inner join HumanResources.vEmployee vEmp on SP.SalesPersonID = vEmp.EmployeeID
) RankedOrders
where
RankedOrders.OrderRank = 1


And there we have it, a much less rank way of getting the top results without having to resort to cursors.

Categories: SQL Rank() Adventureworks Sample | 2 Comments

Free E-Book ”Mastering SQL Server 2005 Profiler’

Looks like its a free book frenzy today, Brad McGehee from RedGate has made available his new book ‘Mastering SQL Server 2005 Profiler’ from the link below.

Brad is also available on ‘Simple-Talk’ and ‘Twitter’

Categories: E-Book, Free, Profiler | Leave a comment

Free SSIS 2005 E-Book from MS-Press

Its not often that you can get something for nothing but the good folks at MS-Press have made SQL Server 2005 Integration Services – Step By Step available as a free download from the link below, you do have to register to receive it but im sure most people interested in its contents already receive some mails from MS.

Free SSIS Book

Categories: E-Book, Free, MS-Press, SSIS | Leave a comment

Starting to Study for MCTS 70-445 BI exam

Thanks to Andrew Coates, who has kindly sent me a voucher for a exam, i am jumping straight back into study with 70-445. This is the 2005 flavour of “Business Intelligence – Implementation and Maintenance”, i already had my eye on the 2008 version but considering the study material is not released until April 1, I will go with 2005. This is not a major issue as my MCITP is in 2005 and i think its good to keep them all in sync.

My first step is to grab a copy of the MSPress Training Kit, i have used these kits for all my previous exams so will stick with them. after reading through this and doing all the exercises plus plenty of extra hands on work ill grab a sample exam from Self-test, who i have also used before, and ensure that i get around 90-95% before booking the exam with Coatsies magic ticket.

So you can expect to see plenty of blog posts as i work through the material. Fingers Crossed 🙂

Thanks again to Andrew for the voucher.

Categories: 70-445, certification | Leave a comment

Create a free website or blog at WordPress.com.