rpowers WebLog

Including Overestimates in MSF Agile Burndown Report

After using the MSF Agile Burndown report for a few weeks in our new TFS 2010 environment, I have to say I am a huge fan.  I especially find the assignment of Work (hours) portion to be very useful in motivating the team to keep their tasks up to date every day.  Here is a view of the report that you get out of the box.

However, I have one problem.  I’d like the top line to have some more meaning.  Specifically, when it is changing is that an indication of scope creep, mis-estimation or a combination of the two.  So, today I decided to try to build in a view that would show overestimated time.  This would give me a more consistent top line.  My idea was to add another visual area on top of the graph whenever my originally estimated time was greater than the sum of completed and remaining.  This will effectively show me at least when the top line goes down whether it was scope change or over-estimation.

Here is the final result.

How did I do it? 

Step 1: Add Cumulative_Original_Estimate field to the dsBurndown

My approach was to follow the pattern where the completed time is included in the burndown chart and add my Overestimated hours.  First I added a field to the dsBurndown to hold the estimated time.

Step 2: Add a column to the query

Step 3: Add a new Item to the QueryDefinition

Step 4: Add a new ChartMember to DundasChartControl1

The burndown chart is called DundasChartControl1.  I need to add a ChartMember for the estimated time.

Step 5: Add a ChartSeries to show the Overestimated Time

tfs 2010 RC Agile Process template update – New Task progress report

Maybe my next post will just be about why I am so excited and impressed with the out of the box templates.  But, for this first blog with my new focus, I thought I would just walk through the process I went through to create a task progress report (to enhance the out of the box Agile template).

So, I started with the MSF for Agile Development 5.0 RC template.  After reviewing the template, I came away pretty excited about many of the new reports.  I am especially excited about the reporting services reports.  The big advantage I see here is that these are querying the Warehouse directly instead of the Analysis Services Cube which means that they are much closer to real-time which I find very important for reports like Burndown and task status.  One report that I focused on right away was the User Story Progress Report.  An overview is shown below:

This report is very useful, but a lot of our internal managers really prefer to manage at the task level and either don’t have stories in TFS or would like to view this type of report for tasks in addition to the User Stories.  So, what did I do?

Step 1: Download the Agile Template

In VS 2010 RC, open Process Template Manager from Team->Team Project Collection Settings.  Download the MSF for Agile Development template to your local file system.  A project template is a folder of xml files.  There is a ProcessTemplate.xml in the root and then a bunch of directories for things like Work Item Definitions and Queries, Reports, Shared Documents and Source Control Settings. 

Step 2: Copy the folder

My plan here is to make a new template with all of my modifications.  You can also just enhance update the MSF template.  However, I think it is cleaner when you start making modifications to make your own template.  So, copy the folder and name it with your new template name.

Step 3: Change Template Name

Open ProcessTemplate.xml and change the <name> of the template.

Step 4: Copy the rdl of the Report you want to use a starting point

In my case, I copied Stories Progress.rdl and named the file Task Progress Breakdown.rdl.  I reviewed the requirements for the new report with some of the users here and came up with this plan.  Should show tasks and be expandable to show subtasks.  Should add Assigned To and Estimated Finish Date as 2 extra columns.

Step 5: Walkthrough the existing report to understand how it works

The main thing that I do here is try to get the sql to run in SQL Management Studio.  So, I can walkthrough the process of building up the data for the report.

After analyzing this particular report I found a couple of very useful things.  One, this report is already built to display subtasks if I just flip the IncludeTasks flag to 1.  So, if you are using Stories and have tasks assigned to each story.  This might give you everything you want. 

For my purposes, I did make that change to the Stories Progress report as I find it to be a more useful report to be able to see the tasks that comprise each story.  But, I still wanted a task only version with the additional fields.

Step 6: Update the report definition

I tend to work on rdl in visual studio directly as xml.  Especially when I am just altering an existing report, I find it easier than trying to deal with the BI Studio designer.  For my report I made the following changes.

• Updated Fields
  • Removed Stack Rank and Replaced with Priority since we don’t use Stack Rank
  • Added FinishDate and AssignedTo
• Changed the root deliverable SQL to pull @tasks instead of @deliverablecategory and added a join CurrentWorkItemView for FinishDate and Assigned to

SELECT cwi.[System_Id] AS ID FROM [CurrentWorkItemView] cwi
            WHERE cwi.[System_WorkItemType] IN (@Task)
            AND cwi.[ProjectNodeGUID] = @ProjectGuid

SELECT lh.SourceWorkItemID AS ID FROM FactWorkItemLinkHistory lh
            INNER JOIN [CurrentWorkItemView] cwi ON lh.TargetWorkItemID = cwi.[System_Id]
            WHERE lh.WorkItemLinkTypeSK = @ParentWorkItemLinkTypeSK
                AND lh.RemovedDate = CONVERT(DATETIME, '9999', 126)
                AND lh.TeamProjectCollectionSK = @TeamProjectCollectionSK
                AND cwi.[System_WorkItemType] NOT IN (@DeliverableCategory)

• Added AssignedTo and FinishDate columns to the @Rollups table
• Added two columns to the table used for column headers

• Added Cells for the two new headers
• Added Cells to the data table to include the two new values (Assigned to & Finish Date)
• Changed a bunch of widths that would change the format of the report to display landscape and have room for the two additional columns
• Set the Value of the IncludeTasks Parameter to 1
  <ReportParameter Name="IncludeTasks">
        <DataType>Integer</DataType>
        <DefaultValue>
          <Values>
            <Value>=1</Value>
          </Values>
        </DefaultValue>
        <Prompt>IncludeTasks</Prompt>
        <Hidden>true</Hidden>
      </ReportParameter>
• Change a few descriptions on how the report should be used

This is the resulting report

I have attached the final rdl.

Step 7: Update ReportTasks.xml

Last step before the template is ready for use is to update the reportTasks.xml file in the reports folder.  This file defines the reports that are available in the template.

Step 8: Upload the template

Open the process Template Manager just like Step 1.  And upload the new template.

That’s it.  One other note, if you want to add this report to existing team project you will have to go into reportmanager (the reporting services portal) and upload the rdl to that projects directory.

Blog Rebranding

I have been spending more and more time on learning as much as I can on Agile Development and also have been fairly immersed in rolling out TFS 2010 in our environment.  I feel like it is time to talk about some of my experiences.  With that, I am rebranding my blog to focus on these topics.  I am going to start with a bunch of blogs on the process I have gone through getting TFS 2010 configured for our development teams.

Last week, Brian Harry was in our office and gave a great talk on the improved tools in TFS 2010 and how Microsoft uses the tools internally.  I followed that up with a high-level overview of the improved out of the box process templates and the process to customize them.  I am definitely very excited about the new features in 2010 and hopefully will keep up my motivation to blog about it.  I am writing my first post right now about the process I went through to build a task progress report based on the user story progress report in the MSF for Agile Development template.  Stay tuned…

Walkthrough of Smiley.NET the FBML sample packaged with the Facebook Developer Toolkit

Smiley.NET is an FBML facebook application that uses the Facebook Developer Toolkit.  This sample is meant to mimick the Smiley sample produced by Facebook in PHP.  The idea behind this application is to show techniques for integrating your application within the Facebook Platform and also ideas for writing an FBML app in ASP.NET.  Before we walkthrough the code, I thought it would make sense to link to a couple of places that anyone writing a Facebook application should know.

Useful Links

The platform homepage – This includes links to various articles, tools and documentation

Anatomy of a Facebook Application – This walks through the various concepts that explain how an application fits into the facebook platform.  This article actually uses the smiley app as an example for several of the concepts.

How to write an ASP.NET Facebook Application – A great walkthrough explaining the basics of using the facebook developer toolkit.

Facebook Developer Toolkit – Open source project that wraps much of the Facebook platform to make it easier to use from .NET.

Facebook API Doc – Part of the facebook developer wiki where the various API methods are described.

Now let’s take a look at the code.  This sample is in the following directory in the codeplex source $/FacebookToolkit/Samples/FBMLCanvasSample.

The first thing to do is setup your facebook application at the facebook develop application.  This is where you will name your application and also get an API Key and Secret.  You really can’t do anything without your API Key and Secret.  After getting this setup, you can get Smiley.NET to function. 

I am going to look at the code first, and second describe the process of setting this application up to run.  So, to start making this application I created a new Web Application.  In this Web application, I added references to facebook.dll and facebook.web.dll from the toolkit.  The facebook assembly has the code that I can use to call the various facebook apis.  facebook.web assembly has some controls and code that I can leverage to make it much easier for my app to live in the facebook platform.

Setup.aspx (Application configuration and Template creation)

If you plan to have your application integrated with the feed system and the publisher, you will need to learn about how these work.  The links below are great starting points.

http://wiki.developers.facebook.com/index.php/Feed

http://wiki.developers.facebook.com/index.php/New_Design_Publisher

Now that you have the overview,  You will need to setup up the feed and publisher urls and create your templates.  To do this you can use the tools on the facebook developer site as described in the links below, or you can use the facebook REST api.  For this sample, we constructed a page that needs to be invoked one time after the application is deployed.  The page is config/setup.aspx.  This page has code to set the app properties, and create two feed templates.  After these are created, the web.config is updated with the ids that were generated and then we can start publishing.

Here is the code used to set the app properties, this is done in place of the edit settings feature at www.facebook.com/developers.

            var dict = new Dictionary<string, string>
            {
                {"application_name","Smiley.NET"},
                {"callback_url",callback},
                {"tab_default_name","Smile.NET"},
                {"profile_tab_url","mysmiles.aspx"},
                {"publish_action","Smile at!"},
                {"publish_url",callback + "handlers/otherPublishHandler.aspx"},
                {"publish_self_action","Smile!"},
                {"publish_self_url",callback + "handlers/publishHandler.aspx"},
                {"info_changed_url",callback + "handlers/infoHandler.aspx"},
                {"wide_mode","1"}
            };
            this.API.admin.setAppProperties(dict);

Next we setup 2 feed template bundles.  Each template bundle contains a 1 line version, a short story version and a long version.  An app can have up to 100 templates.  In this case, we have one for what is published to the users feed (via New Smiley functionality) and 1 bundle for when a smile is sent to a friend via Send Smiley functionality.  I have included the code for creating one of the bundles here.

Masterpage

Now, I create my masterpage.  In this case, I needed to pick between using a masterpage to handle the facebook authentication handshake and a basepage.  The masterpage is a little cleaner in my opinion and really fits exactly how I want this application to work.  In this project the master page is called FBMLMaster.master.  Let’s look at what this contains. 

It defines some placeholder labels for fbml content I want to write from the codebehind, it also defines the dashboard and tabs that will be the container for my application, lastly it defines a content area where each “page” will display its content. 

The code-behind for the master page has a couple of critical things going on.  First it derives from facebook.web.CanvasFBMLMasterPage.  This is the base MasterPage that will handle the authentication handshake with facebook. 

The other 2 things that you will find here are.  1) the SetSelectedTab function.  This a function that can be called from each “page” such that we can set the selected property in the fb:tabs markup.  2) Insertion of the CSS and Javascript.  In the load of the master page, I am checking if the current page is being shown in a profile tab or if it is being shown in the application canvas.  When shown in the profile, I need to inject the CSS inline and hide the tabs, when in the canvas, I can just add a link reference that the fbml rendering engine at facebook can handle. 

The one thing you might notice is the call here to FBMLControlRenderer.RenderFBML.  The FBMLControlRenderer is a class defined in facebook.web.  This will just output raw FBML given a particular usercontrol.  I find writing fbml directly in the user control and using this utility as a very clean way to format and write my fbml (much better than trying to piece it together in a code-behind).  If you look at any of the UserControls in the controls directory of the Sample application you will see this in action.   There is another concept supported by the FBMLControlRenderer which relates to dynamic content.  But, we will look at that with the ProfileBox.ascx.

Now that we have MasterPage that is linking our CSS and JS and controlling the authentication flow, we now need to implement the pages that are associated with the tab described above.  The first page is Default.aspx.  This page is included to demonstrate one thing, and that is a page that is accessible without logging into facebook.   

Default.aspx (publically available canvas page)

The default page just includes some text within the content placeholder and then sets the RequireLogin property of the login and sets the Selected Tab.

A couple of important notes, this page is using the MasterType attribute so that we get a strongly typed instance of the master page in the code-behind without needing to cast. 

The other is that interaction with the master page needs to occur in Page_PreInit.

Home.aspx (Profile box, Application Info Sections and Extended Permissions)

This page demonstrates various things a canvas application can do to integrate with a user’s profile.  The first thing we are doing is in the page_load we are storing a variable on facebook to indicate whether we have setup this user before.  If we have we don’t need to do it again.  The other thing we are doing is creating the info section for this app and the profile box.  This is not a recommended approach, instead you should do this based on user action rather than just at load, but this just shows how to do it in the code-behind.  We will also see how to add a button to your app for accomplishing the same things. 

The profile box and info tab section are described in the anatomy of a facebook app.  They are shown below in this context

Profile Box

Info Sections

Next will look at the actual content of Home.aspx.  This page demonstrates several more ways to integrate your application.  Here is a view of the page, we will then examine each section (how it is done and what it does)

The first section is a button for adding a profile box (as shown above) for this application.  The fbml for this button is

The way that the add-section-button will work for the profile is that facebook will callback to the publisher that you have specified as the Self-Publish Callback URL.  You need to implement a handler that supports the publisher interface to tell facebook what to display in the profile box.  The publisher interaction is among the most complicated parts of building a facebook application.  I am not going to go into the details here, but we can revisit in a future blog post if people are interested.  In the meantime, there is a pretty good walkthrough out on the wiki.  http://wiki.developers.facebook.com/index.php/CSharp_InLine_Publisher.  In this case the Publisher for the profile box is publishHandler.aspx in the handlers folder.

The next section is the similar to the first.  Except this time it is for an info section and the publisher for this is infoHandler.aspx.

The next section demonstrates how to prompt the user for application specific permission.  In the screen above, we see a link to enable email.  This link disappears after the user grants the permission.  The fbml here is:

When the user clicks this link, they are prompted to allow the application to send emails, as shown here.

The last section shows how to have an input form that requests a specific permissions and submits some data.

This page is a simple content page, that formats some fbml to show what we have stored about the user.  This page shows a call to the data store api to get what we have stored.  This application stores all historical smiles in the UserPreference bucket of the datastore api.  There really isn’t anything else going on.  The interesting thing about this page, is that it doubles as the profile tab (if the user adds our app as the profile tab).  It doesn’t even have any code to handle this.  This was all done in the master page, so this page can just function and it will work whether it is in our application canvas or a profile tab.

There are two things going on.  First is an ajax call that is used to store the selected smile in the user’s data store.  This is done with a callback to jsFeed.aspx.  This class takes in the selected index and updated the user’s data store.

After this is done, the final function used some FBJS to pop a publisher dialog to allow the user to publish data.  This is using passed in template id and the Facebook.showFeedDialog javascription function.  We pass mysmiles as the return page after the publisher dialog is complete.

SendSmiley.aspx (Multifeed Story Form)

This page is a demostration of how to publish stories to the wall of one of the user’s friends.  This will use the other publisher that you have specified in your application properties.  In this case, our other publisher (that handles publishing to friend’s walls is in multiFeedHandler.aspx).  To learn more about the multi feed form, read the following wiki page, http://wiki.developers.facebook.com/index.php/MultiFeedStory_form.

In this case, the send smiley page is very simple.  Set the action of the multifeed form to the multiFeedHandler and display the Emoticon grid that is not using the final javascript function.  In this case, the onclick of the image will just set the style to show which is clicked, and the multi feed form will submit it to the selected friends. 

The only other thing to note, is that a multi friend selector is included in this page to allow the user to select the friends they want to target the smiley. 

<fb:multi-friend-input />

Conclusion 

Creating an fbml application that really leverages all of the avenues of integration with facebook is not trivial.  Hopefully, this sample application and the source code avaiable at codeplex will help people overcome the learning curve.  I am going to do my best to support the toolkit better and provide some more articles in the upcoming weeks.  In the next article, I want to examine leveraging facebook connect with the toolkit.  I imagine that it will lead to some updates to the toolkit.

Facebook Developer Toolkit Explained at CNUG

I just finished presenting to the Chicago .NET User Group on Facebook Development using the Microsoft stack.  I focused primarily on the Facebook Developer Toolkit for obvious reasons (it is just what I know).  The session covered an overview of the Facebook API and the Facebook Platform.  We then talked in depth about using the Facebook Developer Toolkit to leverage the Facebook API and to write Canvas pages hosted within the Facebook Platform.  Thanks to my colleague Jonathan Schuster who did the code demos and saved me a bunch of prep time.  I was also able to demo leveraging Silverlight 1.1 on a Facebook IFrame Canvas page.

This is part of the deck where I described an overview of the Facebook Platform. 

If you are new to the Facebook Platform, I'd recommend the recording we made of the session (linked at the end of this post) or check out the interview we did on Thirsty Developer

The turn out was good.  I think we had 40+ people and most were new to Facebook development.  It was fun to introduce this platform to a new group of .net developers.  If you are interested in the presentation, the slides, demos and a video of the presentation can be accessed at clarityTV.

Facebook Developer Toolkit Version 1.3 Released

My thanks go out to my colleague Jonathan Schuster, who took over support of the Facebook Developer Toolkit for the past few weeks.  He was able to push his way through a bunch of enhancements and bug fixes that the users have been waiting very patiently for. 

This release marks a good milestone for the Toolkit as the majority of known issues with the support of Canvas applications are solved.  In my opinion, the canvas support is now very solid and will really ease the learning curve for new developers who want to leverage the toolkit.

One of the big features of Release 1.3 is support for the compact framework.  Many thanks to Peter Foote who dedicated a bunch of time to work on these features.  For those of you interested, the release contains the following items.

Thanks to all of the people who have kept this project alive as I was pulled away.  Pretty cool.  We have had close to 500 downloads already.  I am hoping to re-engage and help add support for new things that Facebook is releasing.

Hosting Vista File Previews in Managed Code

As part of my work on the Coding4Fun Developer Kit 2008, I created a WinForm control encapsulating the flow to display file previews.  The host is dependent on an improved version of Stephen Toub's framework, originally published here.  My enhancements were basically to make the COM wrapper classes public so that they can be used by the Managed PreviewHandlers and also by my Preview Handler Host.  The source for this project including the updated framework, Stephen's sample preview handlers, my host control and a sample application using the host control are available here.  Also, they can be downloaded from the codeplex project for the Coding4Fun Developer Kit 2008.

A couple of quick notes before I describe how the managed preview handler host works. 

1. For any of this to work, the C4F.DevKit.PreviewHandlerFramework.dll needs to be installed in GAC.
2. Also, this host will work with all non-managed preview handlers and any managed preview handlers that were built against the PreviewHandlerFramework mentioned above.  Any Managed Preview Handlers that were compiled using Stephen's original framework or another method will not work with this host. 

To host the results generated by any registered file preview handler in managed code, the PreviewHandlerFramework’s COM Wrappers are leveraged. In addition, part of this host needs to look up the preview handler that is currently registered with a particular file extension. To accomplish this, the PreviewHandlerHost leveraged the registry code from Stephen Toub’s Preview Handler Association Editor (http://blogs.msdn.com/toub/archive/2006/12/14/preview-handler-association-editor.aspx). With the code to find the registered handler in hand, we constructed a control to implement the preview handler Data Flow as shown below (all of this code is part of PreviewHandlerHost project, PreviewHandlerHostControl.cs):

• Declare an object that is the PreviewHandler (this will be cast to different COM Interfaces as needed)
• Unload any existing previews before generating a new preview
  • Call Unload of IPreviewHandler Interface (dDefined in PreviewHandlerFramework as a wrapper to existing COM interface)

Visual C#

private object _comInstance = null; 
private void GeneratePreview() 
{ 
if (_comInstance != null) 
{ 
((IPreviewHandler)_comInstance).Unload(); 
}

Visual Basic

Private _filePath As String 
Private Sub GeneratePreview() 
If Not _comInstance Is Nothing Then 
CType(_comInstance, IPreviewHandler).Unload() 
End If

• Build a RECT struct using the bounds of the visible area where the preview handler can draw. In this case, that is the full bounds of the PreviewHandlerHostControl

Visual C#

RECT r; 
r.top = 0; 
r.bottom = this.Height; 
r.left = 0; 
r.right = this.Width;

Visual Basic

Dim r As RECT 
r.top = 0 
r.bottom = Me.Height 
r.left = 0 
r.right = Me.Width

• Find the CLSID of the preview handler registered for the file extension of the file using registry lookup. This is typically done by looking in the registry under HKEY_CLASSES_ROOT\%file_extension%\shellex\{8895b1c6-b41f-4c1c-a562-0d564250836f}. This Registry key will have a value pointed to the Class ID (CLSID) of the COM registration of the registered preview handler. For more information about how preview handlers can be found in the registry, see this article.
• Create an instance of the preview handler using Reflection and the CLSID found in the registry.

Visual C#

Type comType = Type.GetTypeFromCLSID(new Guid(handler.ID)); 
_comInstance = Activator.CreateInstance(comType);

Visual Basic

Dim comType As Type = Type.GetTypeFromCLSID(New Guid(handler.ID)) 
_comInstance = Activator.CreateInstance(comType)

• Call the appropriate initialize for the create preview handler (either passing in a stream or filepath)
  • If the preview handler is a stream previewhandler, using a COM IStream Wrapper to handle Stream marshalling to COM

Visual C#

if (_comInstance is IInitializeWithFile) 
{ 
((IInitializeWithFile)_comInstance).Initialize(_filePath, 0); 
} 
else if (_comInstance is IInitializeWithStream) 
{ 
if (File.Exists(_filePath)) 
{ 
StreamWrapper stream = new StreamWrapper(File.Open(_filePath, FileMode.Open)); 
((VSExpressDevPack.PreviewHandlerFramework.IInitializeWithStream)_comInstance).Initialize(stream, 0); 
} 
else 
{ 
throw new Exception("File not found"); 
} 
}

Visual Basic

If TypeOf _comInstance Is IInitializeWithFile Then 
CType(_comInstance, IInitializeWithFile).Initialize(_filePath, 0) 
ElseIf TypeOf _comInstance Is IInitializeWithStream Then 
If File.Exists(_filePath) Then 
Dim stream As StreamWrapper = New StreamWrapper(File.Open(_filePath, FileMode.Open)) 
CType(_comInstance, VSExpressDevPack.PreviewHandlerFramework.IInitializeWithStream).Initialize(stream, 0) 
Else 
Throw New Exception("File not found") 
End If 
End If

• Call SetWindow on the PreviewHandler, passing in a handle to the control and the bounds (RECT created earlier)

Visual C#

((IPreviewHandler)_comInstance).SetWindow(this.Handle, ref r);

Visual Basic

CType(_comInstance, IPreviewHandler).SetWindow(Me.Handle, r)

• Call DoPreview on the PreviewHandler

Visual C#

((IPreviewHandler)_comInstance).DoPreview();

Visual Basic

CType(_comInstance, IPreviewHandler).DoPreview()

If you are interested in more information, the zip file included with this project has all of the source and a document describing a walkthrough of using this control.  Additionally, the Coding4Fun Developer Pack 2008 Vol.1 Codeplex project has all the info and will also have any fixes/enhancements.

Coding4Fun Developer Kit 2008 Vol.1 Beta Released on Codeplex

Recently, I have been spending most of my time helping put together the Coding4Fun Developer Kit that was released today on Codeplex.  This is a Kit put together by Microsoft to open up many of the new and powerful APIs avaiable in Vista and Visual Studio 2008 to enable simple programming interfaces that can be used from managed code.

This developer kit contains .net wrappers and sample projects for the following Vista APIs

• Bluetooth
• Contacts
• Digital Sound Recording
• Messaging/Email
• Picture Sharing
• Picture Acquisition
• Power Management
• Preview Handlers
• Feeds
• Desktop Search

I made two big contributions to this developer kit.  The first was building the Preview Handler Wrapper.  This Wrapper included enhancing the managed preview handler framework built by Stephen Toub and published in the January Edition of MSDN Magazine.  In addition, I wrote a managed preview handler host, to enable displaying any preview handler within a .net winform application.  Watch for my next post describing, Hosting File Previews In Managed Code.  My other contribution to the project was pulling the various projects together and building the installer.  I used WIX.  It was a tedious process at times, but ultimately pretty effective.  I promise to write another post detailing my experience with WIX and what I did to put this installer together.

Check out the codeplex site for the Coding4Fun Developer Kit 2008 vol.1.  Definitely some cool and interesting stuff.

SQL 2005 Performance Tuning using DMVs - Part 4 of 7 - I/O Health

In my previous 3 posts in this series, I went through an introduction to Dynamic Management Views and an in depth look at how DMVs can be used to analyze and pinpoint CPU bottlenecks.  In today's post we will take a look at using DMVs for analyzing I/O Health and pressure.

You can use the following DMV query to find currently pending I/O requests. You can execute this query periodically to check the health of I/O subsystem and to isolate physical disk(s) that are involved in the I/O bottlenecks.

SELECT 
    database_id, 
    file_id, 
    io_stall,
    io_pending_ms_ticks,
    scheduler_address 
FROM    
    sys.dm_io_virtual_file_stats(NULL, NULL)t1
INNER JOIN
       sys.dm_io_pending_io_requests as t2
ON    t1.file_handle = t2.io_handle

If you find, that you regularly have a high number of pending I/O requests, you can use the following query to determine which SQL batches use the most I/O.

SELECT TOP 5 
    (total_logical_reads/execution_count) as avg_logical_reads,
    (total_logical_writes/execution_count) as avg_logical_writes,
    (total_physical_reads/execution_count) as avg_phys_reads,
     Execution_count, 
    statement_start_offset as stmt_start_offset, 
    st.[text], 
    qp.query_plan
FROM sys.dm_exec_query_stats  qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY 
 (total_logical_reads + total_logical_writes) Desc

If you are troubleshooting blocked I/O realtime, you can use dm_tran_locks and dm_waiting_tasks to determine what sql is blocking and what is waiting.

SELECT 
    t1.resource_type,
    'database'=db_name(resource_database_id),
    'blk object' = t1.resource_associated_entity_id,
    t1.request_mode,
    t1.request_session_id,
    t2.blocking_session_id     
FROM 
    sys.dm_tran_locks as t1
INNER JOIN 
    sys.dm_os_waiting_tasks as t2
ON 
    t1.lock_owner_address = t2.resource_address and
    t1.request_session_id = t1.request_session_id

The last thing to check when analyzing I/O is the utlization of tempDB.  You can use dm_db_file_space_usage to determine the amount of space that is being used in tempdb by user_objects, internal_objects and the version_store.

SELECT
    SUM (user_object_reserved_page_count)*8 as user_objects_kb,
    SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,
    SUM (version_store_reserved_page_count)*8  as version_store_kb,
    SUM (unallocated_extent_page_count)*8 as freespace_kb
FROM sys.dm_db_file_space_usage
WHERE database_id = 2

That's it for this edition, in the next installment we will look at finding Problem Queries.

The code from this post is available here.

Facebook Developer Toolkit Version 1.2 Released

I have been spending most of time working on the Facebook Developer Toolkit and monitoring our codeplex site.  Things have been going pretty, the response has been pretty positive and the help from the other site contributors has been great.  We even just became the 10th most popular project on CodePlex.  Which is a pretty cool accomplishment and pretty impressive company.

Today, we released Version 1.2 of the Toolkit.

Included in this release are:

The major change is the Canvas base pages, these should help new developers solve the riddle of building Facebook Canvas applications.

Thanks to everyone who helped with code and feedback.  I think we continue to make good progress on this project.  You can download the release or documentation here.

SQL 2005 Performance Tuning using DMVs - Part 3 of 7 - Alleviating CPU Pressure

This is the third installment on my series on using Dynamic Views to help with SQL 2005 performance tuning.  The previous post on Identifying CPU Pressure can be found here.

After identifying that a database is cpu constrained, the next step is to find out what is using all the cpu.  The first step is to identify the SQL batches that are using the most CPU.  To find these batches, you use dm_exec_query_stats and join to dm_exec_sql_text to get the actual SQL statement. The following query will yield the top 10 (currently cached) sql statements for cpu utilization.

SELECT 
    derived_table.total_cpu_time,
    derived_table.total_execution_count,
    derived_table.total_cpu_time/derived_table.total_execution_count as cpu_per_execution,
    derived_table.number_of_statements,
    Plans.query_plan
FROM    
(
SELECT TOP 10 
    SUM(qs.total_worker_time) as total_cpu_time, 
    SUM(qs.execution_count) as total_execution_count,
    COUNT(*) as  number_of_statements, 
    qs.plan_handle
FROM 
    sys.dm_exec_query_stats qs
GROUP BY qs.plan_handle
ORDER BY SUM(qs.total_worker_time) desc) derived_table
CROSS APPLY sys.dm_exec_query_plan(plan_handle) as Plans
order by derived_table.total_cpu_time/derived_table.total_execution_count desc

Another common cause of CPU over-utilization on a database server is query optimization time.  If you take periodic snapshots of the dm_exec_query_optimizer_info DMV, you can get a good idea of the amount of time spent optimizing.  The other way to look at optimization time is to examine how often queries are recompiled.  The following SQL is useful for this examination:

--To examine queries that recompile often,
--plan_generation_num indicates the number of times 
--the query has recompiled. 
--The following sample query gives you the top 10 stored procedures 
--that have been recompiled.

SELECT top 10
    sql_text.[text],
    sql_handle,
    plan_generation_num,
    execution_count,
    dbid,
    objectid 
FROM 
    sys.dm_exec_query_stats a
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) as sql_text
WHERE
    plan_generation_num >1
ORDER BY plan_generation_num DESC

Another common cause of high CPU database server is queries that require parallel processing.  You can use a combination of dm_exec_requests, dm_os_tasks and dm_exec_sessions to find all active requests utilizing parallel processing.

SELECT 
    r.session_id,
    r.request_id,
    MAX(ISNULL(exec_context_id, 0)) as number_of_workers,
    r.sql_handle,
    r.statement_start_offset,
    r.statement_end_offset,
    r.plan_handle
FROM 
    sys.dm_exec_requests r
INNER JOIN sys.dm_os_tasks t on r.session_id = t.session_id
INNER JOIN sys.dm_exec_sessions s on r.session_id = s.session_id
WHERE 
    s.is_user_process = 0x1
GROUP BY 
    r.session_id, r.request_id, 
    r.sql_handle, r.plan_handle, 
    r.statement_start_offset, r.statement_end_offset
HAVING MAX(ISNULL(exec_context_id, 0)) > 0

Alternatively, you can just identify all cached plans that allow parallel execution. 

SELECT 
    p.*, 
    q.*,
    cp.plan_handle
FROM 
    sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) as q
WHERE 
    cp.cacheobjtype = 'Compiled Plan'
    AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
        max(//p:RelOp/@Parallel)', 'float') > 0

This should help you identify and address the causes of high CPU usage.  Next installment will take a look at identifying I/O bottlenecks.  You can download the source for these examples here.

Silverlight 1.1 on Facebook Canvas Page using ASP.NET and Facebook Developer Toolkit

A couple weeks ago, working with Microsoft we delivered the facebook developer toolkit to help developers write Facebook applications using .net.  Since then, we created a codeplex project to help keep the toolkit up to date and relevant.  It has been fun, and the response/involvement from the community has been great.

Yesterday, I spent some time working on how to build canvas pages leveraging the toolkit.  A canvas page is a page that is accessed directly within facebook.com and the result is integrated into the user experience on facebook. These canvas pages were a major part of the new F8 facebook platform.  For more information, check out the Facebook Developer documentation.  Anyway, after struggling with the flow of authentication and application adding, I finally got it working and created 2 new canvas base pages (one for FBML type and one for IFrame).  I posted on codeplex about the changes here.  I also posted some very simple Canvas page examples for both FBML and IFrame.

Now that I had a good framework in place for building the canvas page, I wanted to see if I could get a Silverlight control with Facebook context showing up in my IFrame canvas page.  From past experience, I knew there were some major obstacles.

1. Some namespaces used in the Facebook.dll (wrapper to the API) are not supported in Silverlight namespace.  Most notably: System.XML, System.Web.
2. Silverlight 1.1 Alpha does not support cross domain posts.
3. It will be tough to manage the facebook login process from the Silverlight control, since we can not show the facebook hosted login page.

With this in mind, I had a plan.  And after a frustrating day, just got a Silverlight control showing on a canvas page with some facebook context.  Here's how.

1. Added a new webservice (SilverlightService.asmx) to my ASPNETPlatformSamples web site.  This webservice is a proxy used by the silverlight application to call facebook
2. Updated the ASPNETPlatformSamples web.config to support the web service.
3. Added a ScriptService attribute to SilverlightService to enable it to support JSON calls
4. Added WebMethod GetFriends wrapping the FacebookService.GetFriends(), this method needs to take the 4 parameters that comprise a facebook session, so that calls can maintain context.

   [System.Web.Script.Services.ScriptService]
   public class SilverlightService : System.Web.Services.WebService
   {
       Facebook.Components.FacebookService _fbService = new Facebook.Components.FacebookService();
   
   
       [WebMethod]
       public UserJSON[] GetFriends(string api, string secret, string session, string userid)
       {
   

5. Created a stripped down version of the Facebook.Entity.User class that is serializiable by JSON.  JSON can't serialize enums or subclasses.  So, for this I just stripped them out.  This is called UserJSON and is currently just a member of my ASPNETPlatformSamples web site. 
6. In the GetFriends WebMethod, converted Collection of Users returned from Facebook call to Array of JSON compatible users.

           Collection<Facebook.Entity.User> friends = _fbService.GetFriends();
           UserJSON[] x = UserJSON.ConvertFacebookUserArray(friends);
           return x;
   

7. Created a new Canvas page (IFrame\Silverlight.aspx), alongside to my other IFrame canvas samples
8. Added a placeholder for the Silverlight control and 4 hidden fields to store the facebook context.

   <head>
       <title>Silverlight Project Test Page </title>
       
       <script type="text/javascript" src="Silverlight.js"></script>
       <script type="text/javascript" src="CreateSilverlight.js"></script>
   </head>
   
   <body>
   <form id="Form1" runat="server">
   <asp:HiddenField ID="hidAPI" runat="server" />
   <asp:HiddenField ID="hidSecret" runat="server" />
   <asp:HiddenField ID="hidSession" runat="server" />
   <asp:HiddenField ID="hidUser" runat="server" />
       <div id="SilverlightControlHost" >
           <script type="text/javascript">
               createSilverlight();
           </script>
       </div>
   </form>
   

9. Added code in code-behind to populate hidden fields (so they can be accessed by silverlight control).

   public partial class _Default : CanvasIFrameBasePage 
   {
       private const string FACEBOOK_API_KEY = "c559128010f3edee33796fd4205361c2";
       private const string FACEBOOK_SECRET = "85887d1c9a8334e5059742468a5400ee";
   
       new protected void Page_Load(object sender, EventArgs e)
       {
           base.Api = FACEBOOK_API_KEY;
           base.Secret = FACEBOOK_SECRET;
           base.Page_Load(sender, e);
           hidAPI.Value = this.FBService.ApplicationKey;
           hidSecret.Value = this.FBService.Secret;
           hidSession.Value = this.FBService.SessionKey;
           hidUser.Value = this.FBService.UserId;
   
       }
   }
   

10. Created a new silverlight project using Orcas called SilverlightWebServiceClient
11. Added a web reference to the above WebService
12. Added code in page_loaded event handler of xaml code behind to call the GetFriends method on the WebService to retrieve an array of friends.
13. Then simply wrote text to a textblock with the count that was found.
14. I then needed to add a Silverlight Reference into the ASPNETPlatformSamples.  This essentially copies the .xaml from the Silverproject, and the compiled .xaml.cs into a ClientBin folder of the web site project so they can be accessed from within the website.
15. You can see it in action, by going to my IFrame canvas sample.   After logging in and adding the application, you should have a Silverlight demo link which will show the Silverlight page.

I checked the updated c# code for ASPNETPlatformSamples and a new project for the Silverlight Control into the codeplex site.

Now we need to build some cool Silverlight canvas apps.  If anyone has better ways to do this, I'd be interested in learning about them.

Next up, Silverlight in FBML Canvas page.

SQL 2005 Performance Tuning using DMVs - Part 2 of 7 - Identifying CPU Pressure

In the my first post on DMVs, I gave an introduction to Dynamic Management Views and talked about some of the tuning activities that DMVs make easier.  I also gave my top 10 DMV list.

In this installment, I am going to review how DMVs can be used to analyze CPU utilization on a machine running SQL Server.

Identifying if a System has CPU Pressure

Prior to DMVs, the typical approach to checking whether a server was experiencing CPU bottlenecks was to use the Processor Queue Length performance counter.  This counter should typically be near 0 for systems not experiencing CPU pressure.

This approach is still valid, but is not necessarily a good measure for a server used for SQL Server.  To see why this is the case, we must examine SQL Server Execution Model.

SQL Server uses a User Mode Scheduler (UMS) to control the execution of SQL Server user requests (SPIDs or session_ids). The UMS manages the execution of SQL Server requests (without returning control to Windows). So when SQL Server gets its time slice from the Windows scheduler, the SQL Server UMS manages what user requests are run during this time.

In a 4-proc scenario, there will be 4 User Mode Schedulers, one for each CPU. Each UMS uses a number of constructs (queues, lists and worker threads) to govern execution. At any given time, each UMS will have at most a single running thread, a runnable queue of requests that are
waiting for CPU, a waiter list (for resources such as IO, locks, memory), and a work queue (user requests that are waiting for worker threads).

Whenever the current running thread needs I/O it is moved to the wait list.  When this occurs, the next request from the runnable queue is started.  When the thread needing I/O completes, it is moved back to the end of the runnable queue. 

The time waiting for a resource is shown as Resource Waits. The time waiting in the runnable queue for CPU is called Signal Waits. In SQL Server 2005, waits are shown in the Dynamic Management View (DMV), sys.dm_os_wait_stats.  In this DMV, high Signal Wait % indicates a CPU constraint.   

The query to measure cpu pressure using sys.dm_os_wait_stats is as follows:

SELECT     
    sum(signal_wait_time_ms) AS signal_wait_time_ms,
    cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2)) as '%signal (cpu) waits',
    sum(wait_time_ms - signal_wait_time_ms) as resource_wait_time_ms,
    cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2)) as '%resource waits'
FROM 
    sys.dm_os_wait_stats

You can also monitor the SQL Server schedulers using the sys.dm_os_schedulers view to see if
the number of runnable tasks is typically nonzero. A nonzero value indicates that tasks have to wait for their time slice to run; high values for this counter are a symptom of a CPU bottleneck. You can use the following query to list all the schedulers and look at the number of runnable tasks.

SELECT 
    scheduler_id,
    current_tasks_count,
    runnable_tasks_count
FROM
    sys.dm_os_schedulers
WHERE  
    scheduler_id < 255

That's it for this installment, see you next time as we find out how to determine the likely causes of the CPU pressure we just identified.

SQL 2005 Performance Tuning using DMVs - Part 1of 7

A few months ago, I researched and put together an internal presentation on one of the more interesting features in SQL 2005 (Dynamic Management Views).  This topic is not quite as cutting edge now as it was when I researched it.  But, I think it is still useful to blog what I found.  First off, thanks to Kim Tripp at SQL at www.sqlskills.com.  I first became interested in this topic when watching some of her webcasts.  I also found many of my good samples of DMV useage from some of her posts.

I am going to break this up into parts, this first part is just an intro. 

Microsoft made a big effort with SQL Server 2005 to make the server more transparent to administrators and developers. The result is DMVs, which should make database tuning and maintenance easier.

“DMVs can expedite the diagnosis process by eliminating the need to generate and analyze physical dumps in most cases. DMVs provide a simplified and familiar relational interface for getting critical system information. This information can be used for monitoring purposes to alert administrators to any potential problems. Or, the information can be polled and collected periodically for detailed analysis later.”

DMVs are very useful in diagnosing different SQL performance problems.  I will go into more specifics with the future posts here.  But in general, you will find great resources for looking into any of the following:

• Resource Bottlenecks
  • CPU
  • Memory
  • I/O
• TempDB Bottlenecks
• Slow Queries
  • Bad Plans
  • Missing Indexes
  • Blocking
• Index Fragmentation

Here is a good list of the Naming Conventions of the DMVs that are available:

• CLR Related
  • dm_clr_
• Database Related
  • dm_db_
• Execution Related
  • dm_exec_
• Full Text Search Related
  • dm_fts_
• Index Related
  • dm_db_index_
  • dm_missing_index_
• SQL OS Related
  • dm_os_
• I/O Related
  • dm_io_
• Query Notifications Related
  • dm_qn_
• Replication Related
  • dm_repl_
• Service Broker Related
  • dm_broker_
• Transaction Related
  • dm_tran_

After playing around with all the DMVs I could find, I put together a list of my 10 favorites:

1. dm_exec_query_stats – performance stats of all queries
2. dm_exec_sql_text – returns exact sql for entire batch given a sql handle
3. dm_exec_query_plan – return query plan for entire batch given a plan handle
4. dm_db_index_usage_stats – displays how often each index is used, show Read Seeks, Read Scans, Write Seeks and Write Scans
5. dm_db_index_physical_stats – replace DBCC SHOWCONTIG used to check index fragmentation
6. dm_db_index_operational_stats – use to check for index contention and blocking
7. dm_os_wait_stats – Used to determine the types of waits (signal or resource that are occurring)
8. dm_os_waiting_tasks – Used to help identify blocking
9. dm_tran_locks – Used to help indentify blocking
10. dm_os_performance_counters – Query Access to SQL Server Performance counters

The last thing I wanted to cover in this initial post, was just methods for writing queries to identify the DMVs and their types.  Some DMVs are actual views, while others are tabled valued functions.  Again, thanks to Kim Tripp for most of this knowledge.

Download a script for identifying DMVs and syntax to querying each here

Mixed Authentication- ASP.NET Forms and IIS 6.0 Windows Integrated

Recently I was working on a website that needed to leverage both Integrated Windows and Forms Authentication.  The idea is that this website has some users that are on Active Directory and should be able to gain access without being challenged from credentials when they are logged into the domain.  Additionally, these users should be allowed to enter their AD credentials and access the site if they access the site remotely (while not logged in to AD).  This is all handled by IIS Integrated Windows Authentication.  However, for our situation we also have users who are not in AD at all and need to access the site.  We preferred to not create AD accounts for these users just to permit access to the site.  The solution we wanted was try windows integrated, if it fails, use forms authentication.  This problem will go away with the integrated pipeline of IIS 7.0 (more on this with a later post).  But, in IIS 6.0 this problem proved very tough to solve.

After doing some research, we found this article.  http://msdn2.microsoft.com/en-us/library/ms972958.aspx.  This was a great article and provided the basic approach to the solution. 

To summarize

1. Set site to use Forms Authentication
2. Set web.config authorization to deny anonymous users
3. You need to set one page in your web application to use Windows Integrated Authentication (this was a revelation to me, setting one page to different IIS authentication than others in same app).  In this case we call it WinLogin.aspx
   1. In this page, capture the Request Variable "LOGON_USER" and then call FormsAuthentication.RedirectFromLoginPage.
   2. This allows you to mimick a forms logon for the windows user.
4. Set Forms loginUrl to page from step 3
5. Handle Windows Integrated failures.  Add a custom handler for 401 errors, and in the static HTM page.  Use a javascript function to forward to the real ASP.NET forms login page (WebLogin.aspx).

6.  Update web.config to allow anonymous access of WebLogin.aspx.

7.  Redirect to original requested URL

 

This was very helpful and got us a solution that solved most problems.  However, we quickly ran into one problem that we couldn't live with. For IE users who did not have our site as a "Trusted Site", Windows Integrated would popup a login dialog prompting for credentials prior to our custom 404 provider.  So, our non AD users would have to press cancel on this to get to our login form.  Just not acceptable. 

Some more research brought us to another great post that ultimately gave us they framework for our solution.  The article that we used to help us solve this problem is here http://glazkov.com/blog/credentials-screening/.

We were not able to get his exact implementation working, and ended up changing the code quite a bit.  But, the essence of the approach is exactly as described in that post (and the subsequent updates). 

The short explanation of how this works is:

• Create an HTTPModule to handle incoming requests
• In this module, emit a javascript function to try to load the page that is setup as requiring Windows Integrated. 
• Back in the module, check the LOGON_USER server variable.  (If javascript succeeded in accessing that file, this variable will be populated)
• Continue with redirect

The key to the solution that solves the original problem is the javascript load of the page set to require Windows Integrated Authentication.  What this accomplishes is pre-empting the load of this page that IIS will do and allows our code (in the handler) to respond the results of the check prior to IIS potentially popping up a dialog.

The last thing we needed to do was simply added AD integration into the weblogin form.  So, we could still allow AD users who access the site without being logged into the domain use the domain credentials.  I will discuss this in my next post.  The code I am posting with this blog does not have the AD integration on the login page itself.  (As it will take more work for me to factor that out into a digestable snippet).

You can download a sample here.