Note: While I didn't have any problems, I would recommend keeping the iso for Beta 2 around until you are confident you got everything removed. Sometimes software needs to get something from the original installer to uninstall.

In case you have been living under a rock this week, VS 2008 RTM was released. I don't expect to see much difference between Beta 2 and RTM, given how solid of a product Beta 2 was. ScottGu has a post on the recommended steps to uninstall Beta2 and install the RTM version. It sounds like you could install on the top of Beta 2, but I prefer to put forth the extra effort to get a "clean" install, or at least as clean as I can get without wiping my machine (is this another example of where moving to virtual platform would make sense?).

Of course the first step was to obtain VS 2008 Team Suite from MSDN, which was a bit of a challenge earlier in the week. I'm assuming everyone was trying to get their hands on it, and unless Microsoft is cheap when it comes to bandwidth (doubtful), it goes to show how much people wanted this as soon as it was released. ScottGu had a previous post about where to get all the bits (including the 3.5 redistributable), and I found this post which led me to the stand alone download for Team Explorer.

Next, I followed the recommended uninstall order. I'm glad that I don't install things I know I will never use, less things to uninstall. The names provided in Scott's post are word for word as they show up in Add/Remove programs, so feel free to sort by name. Even with detailed instructions, I still managed to screw up. .Net Framework 3.5 looks very similar to .Net Compact Framework 3.5, and I ended up uninstalling the full framework first. Not on the list, is Team Explorer, and Visual Studio itself. I opted to uninstall Team Explorer first, and the process froze on me.

A "quick" reboot seemed to fix the problem. I proceeded with uninstalling Team Explorer, and the two entries for Visual Studio. I also rebooted again as per Scott's recommendation, and then began the install process, starting with Team Suite, and then Team Explorer and a few more reboots and I was done.

Update:

I came across a script which is supposed to help with the uninstall of the Beta Bits.

Today I made my first attempt at installing (clean install) BizTalk 2006 R2 on our development server. I started out by reviewing the documentation for installing on Windows Server 2003 in a single server configuration. You can find this documentation by clicking the link on the initial install screen for BizTalk. This will take you to Microsoft to get the latest version of the documentation. I also picked up the multi server deployment (production environment), and Vista (development workstation).  The first part of the documentation is more of reference material. Starting on page 38 it switches to detailed installation instructions for each piece that needs to be installed, starting with Windows Server itself.

Installation Wizard

1. Click thru until you get to Select Components. There are obviously the required core components. I also selected items which I thought we might need in the future, but didn't impact the amount of pre-requisite software I needed to install. There is a very nice matrix in the install document that outlines what needs to be installed for each feature. Based on that I selected:

• Documentation
• Server Runtime
• BizTalk EDI/AS2 Runtime (Possible Future Need)
• WCF
• Portal Components
• BAM (Possible future use)
• Human Workflow Web Service (Possible Future Use)
• Administration Tools
• WCF
• Additional Software
• SSO Admin
• SSO Master Server
• BAM Client

I also had to change the install path from C:\ to E:\ to comply with IT standards.

Clicking next brings you to a screen where you select how you want certain pre-requisites (redistributables) downloaded and installed. Choosing the download option (not auto install), takes you to the MS downloads site and cancels the installation. The cab file I needed to download was 102MB, and will be needed for a couple of other installations, so downloading it separate was a good idea.

2. After downloading and coping the cab file to the server, I restarted the installation, re-selected my components, and then chose the option to install redistributables from the cab file. I opted to provide my password for automatic login after reboots. I've copied and pasted the summary of items to be installed that is displayed before you click install.

Prerequisites

The following component(s) will be installed automatically on this computer:

• - Microsoft SQL XML 3.0 Service Pack 3
• - Microsoft Office Web Components
• - Microsoft ADO MD.Net 8.0
• - Microsoft ADO MD.Net 8.0 Patch
• - Microsoft ADO MD.Net 9.0
• - Setup runtime files
• - Enterprise Single Sign-On Server
• - Enterprise Single Sign-On Administration
• - Microsoft Primary Interoperability Assemblies 2005
• - Microsoft Document Explorer 2005

Microsoft BizTalk Server 2006 Components

The following components will be installed:

• - Documentation
• - Server Runtime
• - BizTalk EDI/AS2 Runtime
• - Windows Communication Foundation Adapter
• - Portal Components
• - Business Activity Monitoring
• - Human Workflow Web Service
• - Administration Tools
• - Windows Communication Foundation Administration Tools
• - Additional Software
• - Enterprise Single Sign-On Administration Module
• - Enterprise Single Sign-On Master Secret Server
• - BAM Client

3. No reboots were required, and no errors were reported. By default, when you finish the install, you are taken to the Configuration Wizard.

Configuration Wizard

1. The first thing you have to do is select your configuration type from either Basic or Custom, with the main difference being what service accounts are used for what services. I chose basic, which sets up all of the services to use the same account.

On page 55 of the instructions, it states that the specified user account will be granted the necessary permissions, including SQL permissions. I went ahead and created a local machine account that belonged to the users (not power users) group. I then specified this account in the configuration wizard and clicked Configure.

2. A summary of the configuration about to be performed pops up. I did a quick review, and then clicked next. I then sat, with my fingers crossed as the progress bar ticked by, hoping everything configured correctly. I didn't want to find out what graphic is displayed for an error (on success a green check box is displayed next to each section that is configured). The EDI/AS2 runtime took the longest to configure. I might not install that next time.

3. Everything completed successfully, except there was one warning with SSO. I have a feeling it is a note to back up the SSO secret. Another possibility is that I am running the SSO service with a local, and not a domain account.

Backup SSO Secret

1. Start/Programs/Microsoft Enterprise Single Sign-On
2. Expand Enterprise Single Sign-on
3. Right Click System and choose backup secret
4. Choose a path, and enter a password.
5. Make sure to write down your password and backup the file that was created. I store all my passwords on this nature using KeePass. I also can attach the actual backup file to the password entry in KeePass. If you have problems coping the file, check the NTFS permissions on the file.

Simple Test - CBR

1. Pre-Conditions: Event logs were clear of errors and warnings at this point. I did a quick inspection of services to verify the ones I was aware of, were running.

• BizTalk Service BizTalk Group : BizTalkServerApplication
• Enterprise Single Sign-On Service
• SQL Server (MSSQLSERVER)
• SQL Server Agent (MSSQLSERVER) - Change startup from manual to automatic

2. Reboot and check event logs for errors on startup. I also took this opportunity to snapshot the VM so that I could clean out my CBR test.

3. I created a simple CBR scheme, using 2 local folders, 1 send and 1 receive port. The exact implementation details are outside the scope of this post.

What's Next

Next up, is the upgrade of a BizTalk 2006 install to 2006 R2, so check back for a write up of how that goes.

Microsoft, breaking from it's usual secrecy around the next version of Windows, has released some material on Channel 8 about work being done on the Kernel. Referred to as WinMin, the Kernel weighs in at just 25MB and is supposed to be used in a a variety of Windows products, desktop, server, and small form factor. The video posted, consists of Eric Traut giving a presentation with an integrated power point deck.

The presentation is primarily on Microsoft's hypervisor and vitalization. The comments on Windows 7 don't come until 44 minutes into the video.

Other interesting notes

• Vista and Windows Server 2008 are the first versions of windows that are Vitalization aware
• Admits that upgrading the OS is painful at best. Vista actually installs a new OS and tries to pull settings from XP. Could Windows 7 actually allow you to reinstall the OS without reinstalling?
• Admits it's embarrassing that there are un-installer products.
• Demos of Windows 1 and 2
• WinMin
  • 25MB Kernal
  • 40MB of memory, 7MB free.
  • HTTP Server? I hope that's for demo's only.

The "scheduled" release date is 2010, which is 2-3 years out (simple math FTW). I point out the 2-3 year time frame, because I think it's significant for a couple of reasons. First, how many things got cut from Vista in the 2-3 years leading up to it (WinFS anyone?). Second, how much of what we are hearing today, will make it to the final product? Third, will I still be blogging in 2010, such that I can look back on this post and comment on it when Windows 7 gets released.

I have read comments that Vista is like WinME, and agree somewhat. Not that Vista is as bad as WinME, but that I am expecting (hoping) the next version of windows to be everything Vista was supposed to be, and then more. Along the same lines, people always seem to think that WinXP was so great, but they were comparing it to WinME. I switched to an NT based operating system with Windows 2000 and never looked back. I had a few games that wouldn't run, but there were work around's available. When XP was released, it wasn't as big of a deal for me.

I'm still running XP at home, and Vista at work on my laptop I got back in March. At home, I recently reformatted, and after running Vista for 6 months, didn't see any benefit, especially since I only had 2 GB ram (no need to go 64bit, and yes I realize there is a 64 bit XP), and do DX10 video card. Just over a month later, I'm running 4GB of ram (for no other reason then I got an extra 2GB on the cheap), and I had to go get a new video card (8800GT) to play COD4 at acceptable frame rates and resolutions. Nuts.

With the LAN party this weekend, I don't have enough time to rebuild in time. Then again, it's the type of late night challenge I wouldn't mind. I'm thinking I might just image my system drive and give it a go. I still have all my notes (what to install), from the last time I rebuilt, and the idea of doing a VM based setup with my 4GBs of ram is an option.  

References:

• CNet
• Tech Republic
• Channel 8

As a follow up to my last post, I wanted to post a quick note about a change in the location of AL.exe (assembly linker), in .Net 3.5.

I was attempting to use TFS Build to build a project that was 100% VS 2005 (basically the solution was still a VS 2005 solution), but I was getting an error stating that al.exe could not be found in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727, and that I either needed to pass the path in to MSBuild, or, install the SDK. Not wanting to install an older SKD, I opted for the path option, but then I needed to determine the path. I figured a newer version of al.exe must be installed somewhere with .Net 3.5, and sure enough, it's under C:\Program Files\Microsoft SDKs\Windows\v6.0a\Bin.

I checked out the TFSBuild.rsp file and added the following line:

/p:ALToolPath="C:\Program Files\Microsoft SDKs\Windows\v6.0a\Bin"

Checked it back in, and it worked.  

With our migration to VS 2008 (Beta 2) complete, the next task was to formally adopt an automate build process, and we chose Team Build as the software to drive that process. Previously I had setup CC.Net, as I had used it at a prior job so I was familiar with out. However, there was never a buy in from the whole team, so no one except me really cared when the build broke, or how to go about fixing it.

As part of our migration plan, we have gotten buy in from the entire team (IS) on an automated build process, so for at least a the development and testing level we will have an automated process. The next step after that, is using Team Build to actually automate the deployment to our staging and production environments, such that we can easily deploy small, and large changes in an efficient, consistent, and error free manor.

One of the downsides to development, at least for me, is the task of deploying a change to production. There is usually a lengthy change management process, a number of documented (and un documented) steps needed to do the actual deployment, and then a series of manual tests to run to make sure everything is still working. At a presentation I attended as part of the ARCReady series, the concept of a truly automated build and deployment process was presented, and that concept is what I hope to accomplish (eventually) on my current project.

The first step in achieving my utopia, is to start with a simple build that can be invoked at anytime. I actually have another developer working on setting up the builds for our main project. The project I'm using for this example, is for our smart devices.

To get started, open Visual Studio and select New Build Definition from the Build Menu. This opens up a dialog box with 6 tabs/sections listed on the right, and the tab details on the left. The General, Project File and Build Defaults require information in order to proceed. Workspace, Retention Policy and Trigger all have defaults chosen, but you can change them if so desired. When you are done configuring all of the tabs, click OK.

• General:

Build definition name, an optional description, and a checkbox to enable or disable the build. I named the build "ProjectName - All", and entered a short description of what I hoped to accomplish with the build.

• Workspaces:

You need to select a Source Control Folder, and a Local folder. The defaults are the root of the project in TFS, and a folder defined as $(SourceDir)

• Project File:

Here you select the MSBuild file used to define the build. The default location is $/ProjectRoot/TeamBuildTypes/BuildDefinitionName. If there is no TFSBuild.proj file in the location, you will have the option to created one, which is what I did here.

Choosing to create a new TFSBuild file, bring up a new dialog that will allow you to select a solution in the workspace, 1+ configuration options (i.e. debug or release and target platform), and build options (unit tests, code analysis, etc). I happen to have a solution in this workspace, so I am using it, and chose to build both a debug and release version.

When you are finished with the TFSBuild options, click finsih to return to the Build Definition dialog

• Retention Policy:

Here you describe how builds should be retained, and the values you choose is completely dependent on your individual needs. For now, I'm selecting "Keep Latest Only" for Failed, Stopped and Partially Succeeded, and "Keep 5 Latest" for Succeeded.

•  Build Defaults:

You need to specify a default Build Agent (computer running Team Build), and a staging share. I was surprised to see that the Build agent we had already defined in another project, was not available in the drop down list. I recreated it by clicking the New button. I had to sepcifcy a Agent Name, computer name, and working directory (defaulted to $(Temp)\$BuildDefinitionPath)). If you don't want the source downloaded and built from C:\Documents and Settings\TFSServiceAccountName\Local Settings\Temp\Projectname\BuildName, change the working directory here.

Since the team working on this project is a little different, I've setup a different folder/share for this project so that I can assign appropriate share and NTFS rights.

• Trigger:

Finally, you can select when to build. Options include manual (check-ins do not trigger a new build), Build each check-in, Accumulate check-ins until the prior build finishes, build every week on the following days.

For requirements, I am choosing the manual build option.

You new build definition should show up under TFSServer\Proejct\Builds in Team Explorer. Right click on the build and choose Queue New Build. Here you can select the build definition (defaults to the build you right clicked on), Build Agent, drop folder for the build (defaults to the folder you chose under Build Defaults), a queue priority, and option MSBuild command line arguments.

My build started, and downloaded all of the source from the root of the Project, which is somewhat undesirable, as it's not all needed. Also, it would be better if the build kicked off from a specific project within the solution I had selected. However, I'm in a pretty good position right now, I just need to tweak some stuff. When the build fails, a new bug is created, and if you have email alerts setup, you will be notified.

Issues Resolved:

• Limit the amount of source downloaded (don't download the entire tree starting at $/Project

I solved this, at least for now, by going back into the build definition, and editing the workspace mapping. I change the default from $/Project -> $(Source) to $/Project/Folder -> $(Source)\Folder. Not only will this limit the amount of source downloaded, it is also used to

• Reference Paths

There is a section in the TFSBuild.csproj file for specifying reference paths. This is very useful when you have those developers who always add a reference in a nonstandard location and break the build. It is specified in an item group property, as a child of the root <Project> element. In my file, it was inserted at the end. I am going to try to insert this into a .targets file that I can reference from multiple build files.

<ItemGroup>
    <!--  ADDITIONAL REFERENCE PATH
     The list of additional reference paths to use while resolving references. For example:
     
         <AdditionalReferencePath Include="C:\MyFolder\" />
         <AdditionalReferencePath Include="C:\MyFolder2\" />
    -->
  </ItemGroup>

 

• Get a specific version (label)

I should have realized this earlier, but without an option in the Queue Build GUI, my need for building a specific version from a label drops significantly. I wanted QA to be able to build a version from a label, as defined by the dev team, such that the dev team could move on and start developing and checking in without impacting QA. The only "supported" way to get a specific version is to override the CoreGet task (see Ref #8), which means QA would have to check out and modify the TFSBuild.csproj file.

Open Issues:

• Select a specific project in a solution to build from.
• Current work around, is to create a new solution, or modify the solution I've been using to unselect some problem projects.

Miscellaneous:

• Read on Buck's blog that you have to have an interactive session running on the build server to do automated UI tests. He has instructions on how to do this. (See Ref #5)
• Extending the TFSBuild.csproj file. You can define reusable .targets files, which are just MSBuild files, and then import them into the TFSBuild.csproj file using <Import Project="Path\FileName.targets"/>
• You can add tasks based on TF Build targets in either the TFSBuild.csproj file, or your imported .targets. (See Ref #2)
• You can do incremental builds by setting the SkipClean property to true (See Ref #3).
• The .rsp file that is created with the TFSBuild.csproj file is a file that is used to pass command line arguments to MSBuild (See Ref #7).

References:

1. MSDN Documentation
2. Customizable Team Foundation Build Targets
3. How to: Customize the SolutionToBuild Item Group
4. Buck Hodges Blog
5. Buck's basic guide to Team Build 2008
6. Incremental Builds
7. MSBuild command line reference
8. Get a specific version

Not that we use the TFS reports that much, but I noticed the main report on the front page of project site in Share Point, showed the warehouse hadn't been updated in a couple of days. So begins another adventure into getting it to work again.

At first I thought it was going to be easy, the service wasn't running. We had a power failure in the data center this past week, and it looks like on reboot, the service failed to start. Our new data center isn't running at production levels yet, so a power failure due to human error is not surprising. However, after restarting the service, the warehouse still wasn't updating.

I decided to invoke the warehouse manually, and ran the scripts suggested on this familiar MSDN thread to try to see what was going on. The manual warehouse update worked once, then wouldn't work again (Status always returned idle) until I restarted the IIS server. The results of the query did not seem to update, even though the Status values returned from the web service indicated that the warehouse update had ran. I decided to look at the ScheduledServices.xml in Program Files\Microsoft Visual Studio 2008 Team Foundation Server\TFSServerSchedule, and it was showing a wierd value for last run time (lastrun="1:1:1:0:0:0"). I'm guessing that this value indicates the process started, but never finished, so it never updated the date. My final clue to work with was an event log entry stating there was an unexpected exception while calculating code churn (TF53010). The specific details spoke of a incomplete character stream and possibly invalid encoding, in an xml file I had checked in recently.

I came across a MSDN post that mentioned that you can add this (<add key="ProcessingType" value="Full" />) appKey to your warehouse app.config. I could tell the warehouse web service restarted due to the delay in the web page for invoking the warehouse, but it appears like the run command has no effect again. So after "running" once, it won't run again at all. The value in ScheduleServices.xml is not changing at all (I manually changed the value so I could tell), so things are working at all. Event log is clear, so I need to find some other type of information.

I downloaded an installed DebugView from Sysinternals (used it before in TFS 2005) and changed tracing levels as outlined in the MSDN post linked to above, however either something change in 2008, or I setup the debugger wrong cause I got nothing. There were some other settings related to tracing, so I turned those on as well (appSettings.traceWriter = true, and set all switches to level 4). Nothing at all. The changes to the web.config file, which restart the app domain, seem to have some effect in that GetWarehouseStatus returns values to indicated it's trying to process, however, I get no debug or trace information. I take that back, Debug information has finally stared showing up I just don't know why.

Unfortunately there was nothing that pointed to why the warehouse processing was failing in Debug View. It seemed to show that everything was working correctly. I'm thinking that ScheduledServices.xml is only updated when the scheduler runs the warehouse, not manually. Looking back at my reports, them seem to be updating now.

So in summary, things appear to be working for now (need to wait 1 hour to see if the scheduler runs as expected), but I don't know exactly what fixed the problem. I turned off debugging and full processing, and things seemed to continue to work. I think the TfsWarehouse.dbo._WarehouseConfig table has contains rows that provide better information on when the warehouse last ran. You can easily change the RunIntervalSeconds setting to change how often the scheduler runs the task. I restarted the scheduler tasks after changing. Since our database is small, I set this to 2 minutes just to see if it was working, then set it back to 60 minutes.

You may have noticed (well, probably not, I don't think this blog has made it mainstream yet, but if you stumble across this blog from an errant Google search, welcome), a flurry of posts today, which make up for the lack of posts in the last 2 weeks. My company, has just competed it's migration (for the most part) from VS 2005 to VS 2008. I am happy to say, that I was a key driving force in the adoption of VS 2008 (Beta 2), and was the first one on the team to use it for work related tasks.

One of the biggest tasks, was the migration of our TFS server, from 2005 to 2008, running on a new VM, in a new domain (that knows nothing of the previous domain). I have 2 posts about this migration (here and here), which had me putting in some long nights (4:30am, 2:30am, 1:30am), and a low WAF (wife acceptance factor). Since TFS is a production server, most of the work had to be done after hours. Most of the problems I encountered, were not do to TFS, but just moving a production server in general. The tfsAdmin tool could stand a few extra functions to support moving between domains, but in the end, I lucked out do to the small team size, and small number of open work items.

With the multi-targeting feature of VS 2008 (compile projects against .Net 2.0, 3.0 and 3.5), it was mostly backwards compatible with our VS 2005 development environment. I only ran into a few issues where I had to do some work work around's as to not break everybody else still running on VS 2005.

• New Code Analysis Rules
• New compiler, which thru a couple of new errors
• Test projects would always be set to use version 9.* of the TestTools dll. That along with a few other nuances, was the biggest issue.

Now that everyone (well almost everyone, and I can't comment on who isn't at this point, but let's just say we don't have direct control over their development tools) is running VS 2008, the few incompatibilities are gone, and our new build server will be rocking real soon. I need to work on a checklist of new IDE features to make sure I take advantage of them. After using VS 2005 for the past 2 years, it's easy to get into a routine and miss out on the new features.

One thing that I haven't commented yet on, is the stability of the Beta 2 build. 2 words, ROCK SOLID. They could have just labeled it RTM.

Well, it seems like more and more blogs I read are doing this, so seeing as I'm in the "repost" stage of blogging, I figured I'd jump on the bandwagon.

True cost of off shoring development: Found this from Rocky's blog. A co-worker of his, wrote a post on the true cost of off shoring, which has, recently started to affect me. The author does a good job of identifying the hidden costs in off shoring, and even if the numbers he uses in his examples are off, it still gives you an idea of what to look for.

How to achieve blog success in one easy step: Jeff Atwood discusses why he started blogging and how he achieved success (100,000 hits/day). I would say I am exactly where Jeff started, maybe one day I'll have people who actually read my blog.

LOLCats compiler for VS.Net and the CLR: I still havn't figured out exactly what LOLCats is, but the fact that someone wrote a compiler for it is too funny.

I must make a confession, I am not a DBA. I think I know quite a bit about SQL, but when it comes down to it, there is just too much to know without specializing in SQL. I will probably sound like a SQL rookie with this post, but that's the price to pay for getting better, acknowledging weaknesses, and working to improve on them.

A query in one of our batch processes was timing out, and it was up to me to fix it. You pretty much have two options, the easy way, just increase the timeout, or the harder, tune the query. I personally feel the second option is more rewarding, but ended up spending more time on it then I expected, and exposed myself as the DBA imposter that I am.

I have tried to reproduce the general form of the query. It basically consists of several derived tables joined together and returned as an XML document.

SELECT table1.column1, table1.column2, table2.column1, table2.column2,
       table3.column1, table3.column2, table4.column1, table4.column2
FROM (SELECT sub1.column1 as column1, sub2.column2 as column2
      FROM sub1 INNER JOIN sub2 ON sub1.id = sub2.id
      WHERE Clause) table1
FROM (SELECT sub1.column1 as column1, sub2.column2 as column2
      FROM sub1 INNER JOIN sub2 ON sub1.id = sub2.id
      WHERE Clause) table2
FROM (SELECT sub1.column1 as column1, sub2.column2 as column2
      FROM sub1 INNER JOIN sub2 ON sub1.id = sub2.id
      Where Clause) table3
FROM (SELECT sub1.column1 as column1, sub2.column2 as column2
      FROM sub1 INNER JOIN sub2 ON sub1.id = sub2.id
      WHERE Clause) table4
ORDER BY table3.column1, table3.column2
FOR XML AUTO, ELEMENTS

I ran the query thru the DB Engine tuning advisor, and it came back with no improvements, as there were existing indexes which it probably thought was adequate. I also looked at the estimated execution plan, and noticed 2 sorts, each with a 25% weight. According to the comments in the stored procedure, one of the last things to be added was the ORDER BY table3.column1, table3.column2, so I commented that out, and the query ran in 1 second (original execution time was ~50 seconds). The first thing I thought was a missing index, or an index that needed to be rebuilt. There was an index already on table3.column1, so I tried adding column2 and that didn't help at all.

Let me take a step back and offer an opinion on how to classify the level of knowledge a person has with SQL.

• Level 0: Just starting out. Doesn't understand even the basics of optimization and performance.
• Level 1: Some experience in writing good queries. Understands concepts such as normalization, not doing much with indexes at this point.
• Level 2: Starting to place an emphasis on writing good queries, and creating good schemas. Starting to create indexes
• Level 3: Can start to spot performance issues in other peoples queries. Knows how to use the DB engine tuning tool and estimated execution plan
• Level 4: Adds indexes, hints, etc without needed the DB engine tuning tool for most queries
• Level 5+: I haven't gotten there yet, but it probably involves, among other things, intelligently using the skills from level 0-4, as just adding indexes isn't always the solution, and can cause issues themselves.

I was basically stuck at level 3 witch my problem, and needed to move to level 4. I would suspect that allot of people who list SQL experience are at the same level. The SQL engine does a pretty good job of optimizing queries, and throwing something into a GUI tool isn't that hard to do once you know about it. For smaller projects, the database isn't going to be too complex, and on really big, enterprise level projects, you'll probably have a dedicated DBA.

I knew of Index hints, it just wasn't clicking at this point that, that is exactly what I needed to do. I turned to everyone's favorite helper, Google and started searching for ways to increase performance on sorts. The first hits were the obvious things, add an index, but then I started to see glimpses of things like, index hints. I redid my search for index hints, and started to get more specific hits, and realized this was what I needed, and it finally clicked with other things I have read in the past. When you never really have to do this level of DBA, you don't think of everything you should, you get rusty compared to say, someone who has just read a book on SQL.

Armed with what "tool" I needed to do, I set out to add an WITH INDEX (ix_Table1_Column1_Column2) statement to my query, but even this was a humbling experience. I just couldn't figure out where to stick it, I kept getting syntax errors. I finally looked up the syntax for a select statement in books online, and very carefully examined the syntax and compared it with my query. I finally realized that the statement has to go between the "FROM sub1" and the "INNER JOIN" statement in the table3 sub query. After adding my index hint, the query completed in ~2 seconds, or a 48 second improvement.

This was a learning experience on several levels for me. First, I fixed my query, and realized that when the DB tuning tool fails to provide an answer, you got to switch to "manual" mode. I was lucky enough to have know to try removing the ORDER BY statement, and with out the comment in the stored procedure, I'm not sure if I would have been able to figure out exactly where to look. I'm sure the information provided in the estimated execution plan, would have pointed a more experience DBA in the right direction.

I was reading a recent post by Jeff Atwood about how he had a new virtual server for his blog, and how one of his requirements was a 64bit OS. Jeff provided a link to an article from 2005 on the Microsoft Operations blog that makes a case for using 64bit OS’s on servers. I have always thought that conventional wisdom has said that if you do not need more then 4GB of ram, you don’t need a 64bit OS, and the hassles that go along with it. Apparently, the ability to have a larger virtual address pool offers significant improvements to performance for web applications.

The article, written in 2005, shows some relative performance comparisons between x86 and x64 measured in responses per second, and response time. The requests per second are pretty close, with one category (static) performing better on x86, but the differences in response time are phenomenal. Aside from raw performance numbers, it was noted that Worker Processes recycled less, which would lead to improved end user experience. It was also noted that asp.net 1.1 is only available in 32bit, but that there was no problems in running it on a 64bit OS, and that the 2.0 version of the framework has a 64 bit version.

The biggest issues faced by the operations team (in 2005), was making sure their 3rd party app's like AV and Backup, and any drivers, were 64bit compatible. I'm hoping 2 years later, this is slightly better.

We are just starting to get some servers up for our new project and the question of 32 vs 64 bit has come up. Normally I'd be all about the latest and greatest 64bit goodness, but IT is always wanting to take the conservative approach. However they've agreed to go 64bit OS for the DB servers for sure, so I don't know what the difference is between those servers and the web servers, should be the same as far as AV, backup and drivers are concerned. We'll see how it goes.

Jeff also provide a link to another Microsoft Operations post about the free log parser provided by Microsoft.