Wednesday, December 26, 2007

Team Foundation Server Power Tools - TFS 2008

For those of us who actively use Team Foundation Server and who've updated to TFS 2008, these tools are a must-have! It's good to see that Microsoft has really kept up with the need to deliver these add-ons quickly!!

The tools can be found here:

Concurrent with the release of the power tools, Microsoft has also released an update to the TFS Source control provider. They have been pretty quiet about this, but the provider fixes several rather annoying bugs. It can be downloaded here:

Monday, December 24, 2007

Addskills Expert Seminar - Analysis Services Deep Dive - Stockholm

I just returned from leading a 2 day "Experts Seminar" hosted by Addskills (Formerly Jonsson & Lepp) in Stockholm, Sweden. The course was well attended and was quite challenging. I will use this blog entry to post any additional materials and answer any further questions that delegates who attended the course have.

Download the slide deck here: Expertseminarium SSAS 071217-18.pptx

Download the additional materials here: Analysis Services Expert Seminar - Additional

For those that attended the seminar, please don't hesitate to send any questions my way, either via the comments here, or email at

Saturday, December 15, 2007

SQL Connections - Spring, 2008

I will be presenting a session on the SQL 2008 Resource Governor at the spring SQL Connections conference, which is in Orlando, Florida April 20-23rd, at the World Center Marriott.

If you're interested in learning about SQL Server internals, this will be a good session for you to attend. Here's the abstract for the session:

Introduction to the SQL Server 2008 Resource Governor

With multiple workloads on a single server, administrators must avoid problems such as a runaway query that starves another workload of system resources, or low priority workloads that adversely affect high priority workloads. SQL Server 2008 includes the Resource Governor, which enables administrators to define limits and assign priorities to individual workloads that are running on a SQL Server instance.

Attendees will gain insight into the need for the Resource Governor, will see live demonstrations of the Resource Governor in action, and will learn necessary tips and tricks on how the resource governor can be used to solve real-world problems.

Thursday, December 13, 2007

Visual Studio 2008 "Data Dude"

Gert Drapers just posted a "What's new in 2008" entry on his blog. Basically there's not a whole lot of new functionality, but several bug fixes and things of that nature. See the entry here:

Saturday, December 8, 2007

People over Process

In an earlier blog post, I mentioned some of the tenets of Agile development and how they applied to Business Intelligence projects. One of the tenets that is close to my heart is "Individual Interactions over Processes and Tools" Basically, what this tenet says to me is that any Agile process must be constructed to value the people more than the process. To me this just screams common sense, but I am truly amazed at how uncommon it seems to be these days.

My team is a fairly small team, but we've built a product over the last year that has already been sold and deployed into our customer base and has generated significant revenue. This despite the fact that many of the team members had never even heard of the technologies that I was asking them to master (Not to mention the fact that when we started the project, NONE of the technologies we employed were more than Beta). I honestly (and yes, I know I'm biased) don't know how you could be more successful with a project. Some think we got lucky, some think we just chose an easy path (this is the funniest as far as I'm concerned, I guarantee you that those who say this would have failed miserably given the same task) and there are those who think it was a combination of both...

In my spare time, I'm part of a process-improvement team who's charter is to construct an Agile process that will help us get products to market faster and more reliably. Part of the process is developing various work items and work product that can be defined and rolled out to a wide audience. I was in a meeting the other day that started off innocently enough, we were reviewing a specific work product type that we want to use across all Agile projects. I looked at the output and thought, "wow, I wouldn't have thought of this that way, but it looks pretty good, I like it and can use it".. By the end of the meeting, the work-product had been torn to shreds and all sorts of workflow and parent/child relationships were now included. All I could think of as I left the meeting was the fact that had I been forced to use this thing that was just constructed, there would be no way we'd have ever gotten off the starting line in my current project. This experience has caused me to come up with a set of 2 simple rules for any Agile project:

  1. The process should be minimally invasive, or even invisible
  2. When all else fails, see rule #1

If the tools surrounding a process are too rigid or too invasive, developers are NOT going to use them. Again, this is COMMON SENSE as far as I can tell....

Ok, I guess I'm done ranting for now....

SQL 2008 Resource Governor

As you might have noticed, in an earlier post I mentioned several new CTP5 features for SQL Server 2008, "Katmai". One of these new features is the Resource Governor. For those of us who've worked with SQL Server in larger-scale production systems, the Resource Governor is a godsend. I've recently had the opportunity to play around with the Resource Governor and thought I'd share some concepts here.

Resource Governor Overview

Simply put, the Resource Governor is a component of SQL Server 2008 that allows administrators to configure pools of resources (Think IIS application pool, although it isn't quite that simple) and set specific limits on those resources. There are 2 main components that make up the Resource Governor:

  • Workload Group - A Workload Group is a container that is used to limit certain SQL Server-specific resources, such as Degree of Parallelism or user requests.
  • Resource Pool - A Resource Pool is a collection of system resources such as memory or cpu.

A Workload Pool is assigned to a Workload Group, which is assigned to the Resource Governor.

Configuring Resource Pools

Resource Pools (at least in CTP5 of SQL Server 2008) consist of settings for the minimum and maximum CPU and Memory utilization. The following code creates a pool named "limitedPool" and sets the max memory utilization to 50% and the max CPU utilization to 30%.


You can also set the MIN percentage of either or both of these settings as well.

Configuring Workload Groups

Workload Groups are a little more complicated because they allow you to control some specific SQL Server related conditions, as well as apply Resource Pools. The following code creates a Workload Group named wrkGroupLimited:

MAX_DOP = 8,
USING poolLimited;

This code sets the overall importance of the group to "Medium" which is the default setting (This is an enumeration that is used very similar to the OS "priority" setting for an execution thread), limits each request to 50% of the memory size (no single request can consume more than 50% of the memory allocated to the group), sets both the maximum query time and the maximum "wait for resources" time to 5 minutes, sets the maximum degree of parallelism to 8, and sets the maximum number of outstanding requests in the group to 30. The "poolLimited" Resource Pool is assigned to this group as well.

Creating a Classifier Function

In order for SQL Server to be able to use the Workload Groups that you've created, you need to classify each connection to determine which group that specific connections falls into. This is done through the use of a Classifier Function. Classifier functions are new in SQL Server 2008 and execute each time a new connection is made to the server. Classifier functions are scalar user-defined functions and are basically used to return the name of the target Workload Group for each user connection. The following code creates a Classifier Function that tests to see if the application making the connection is Management Studio, and if so, assigns the connection to the "wrkLimited" Workload Group. If the application is not Management Studio, the default Workload Group is used.

IF (APP_NAME() = 'Microsoft SQL Server Management Studio')
SET @grpName = 'wrkgroupLimited'
SET @grpName = 'default'
RETURN @grpName

Assigning a Classifier Function to the Resource Governor

The final step in the configuration of the Resource Governor is to assign the classifier function and activate the configuration. The following code assigns the fnsRsGovMgr function to the Resource Governor:


And finally, the following code activates the configuration:


Now, when a user connects to the SQL Server instance using Management Studio, their system resource utilization will be very limited.