Friday, November 30, 2007

Undetected Deadlocks in SQL Server

Every once in awhile, things that are hard to explain happen when running resource-intensive queries against SQL Server. Yesterday (and by extension, this morning) I had one of those happenstances.

There's a phenomenon in the SQL Server engine known as an "Undetected Deadlock". They have been around for awhile, and there have been numerous patches and hotfixes related to the problem.

Basically, these undetected deadlocks can happen in many places, but the most common is related to parallelism. For example, a query gets parallelized, then CXPacket waits occur, and then somehow the SQLOS CPU Scheduler gets "out of whack" and one or more CPUs forget that they're participating in a parallel query and try to move on to other things. In rare cases, this leads to a deadlock because not all CPUs are in sync with one another. At this point, all activity on the server comes to an end because of a "Suspended" process. Checking the SQL Server message log (I will still call it an error log!) results in the following:



The only way out of this mess is to kill the process that caused it in the first place.. If you look closely at this error log, you'll see that the problem actually started at 8pm and wasn't resolved until 6am when I killed the process.

Wednesday, November 28, 2007

Visual Studio 2008 & SQL Server 2005

I made a pretty big mistake the other day. I installed Visual Studio 2008 Team Suite on my primary desktop workstation. Instead of doing the smart thing and installing it side by side with Visual Studio 2005, I uninstalled Visual Studio 2005, then installed 2008.


The main reason that this is a bad idea can be summed up in the following picture:


Because I uninstalled VS2005, I broke SQL Server Business Intelligence studio. Since most of the code I interact with on a regular basis is SQL Server related, this is a problem.


In order to solve the problem, I have to reinstall SQL Server 2005 Workstation components and then reapply SP2 for SQL Server (and the subsequent rollup patch). All in all a pain in the neck.

Don't make this same mistake!

Monday, November 19, 2007

WTF?? Or, more appropriately, Error Dialogs

My laptop has been giving me fits lately, so I decided to augment my image backup with a file backup using the Windows Vista Backup and Restore Center... About 30 minutes into the backup task, the following dialog appeared:


Now, I know I'm not the smartest guy in the world, but does this dialog make sense to ANYONE????

SQL Server 2008 "Katmai" CTP 5 New Features

Well, the long wait for Katmai CTP5 (The "November CTP") is finally over. Microsoft has released CTP5 and has posted the bits to  (Don't look for the bits on Connect just yet as they are not posted there)

New Features

There are a bunch of long-awaited features in CTP5, but here are some of my favorites:

Reporting Services Scale Engine and Robust Server Fit and Finish

A reengineered memory management and scalability infrastructure lays a solid foundation of scalability capability to enterprise customers.  

Transparent Data Encryption

SQL Server 2008 enables encryption of an entire database, data files, and log files, without the need for application changes. Encryption enables organizations to meet the demands of regulatory compliance and overall concern for data privacy. Some of the benefits of transparent data encryption include searching encrypted data using both range and fuzzy searches, searching secure data from unauthorized users, and data encryption. These can all be enabled without changing existing applications.  

Resource Governor - Limit Specification

Resource Governor allows organizations to define resource limits and priorities for different workloads, which enables concurrent workloads to provide consistent performance to end users. This enhancement specifically delivers the resource limit functionality.

Backup Compression

Keeping disk-based backups online is expensive and time consuming. With SQL Server 2008 backup compression, less storage is required to keep backups online and backups run significantly faster since less disk I/O is required.

Plan Freezing

SQL Server 2008 enables greater query performance stability and predictability by providing new functionality to lock down query plans, enabling organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.

Fully Parallel Plans Scale on Partitioned Tables

Partitions enable organizations to manage large growing tables more effectively by transparently breaking them into manageable blocks of data. SQL Server 2008 builds on the advances of partitioning in SQL Server 2005 by improving the performance on large partitioned tables.

Data Collection and Performance Warehouse for Relational Engine

Performance tuning and troubleshooting are time-consuming tasks for the administrator. To provide actionable performance insights to administrators, SQL Server 2008 delivers more extensive performance data collection, a new centralized data repository for storing performance data and new tools for reporting and monitoring

Service Broker Enhancements

Getting the data to the right place at the right time is important.  Service Broker Conversation Priority in SQL Server 2008 gives you greater control over the system by making it easy to configure priority rules so that the most important data is sent first and processed first.

Registered Servers Enhancements

Enhancements to the Registered Servers tool window in Management Studio include running T-SQL queries and policies against groups of servers and the ability to share a common, centrally stored, server topology (Database Engine only).

Synchronous net-changes change tracking for SQL Server

SQL Change Tracking feature provides the functionality to synchronously track changes to data in user table without the need to create triggers or modify schema of the table. Applications will be able to reliably determine what data has changed since a watermark/baseline and will be able to obtain the latest data. The feature is geared towards providing the functionality with least DML overhead.

T-SQL IntelliSense

Transact-SQL IntelliSense provides intelligent aids for Transact SQL scripting that make language references easily accessible for database developers. When coding, you do not need to leave the Database Query Editor to perform searches on T-SQL language elements or your database metadata.  You can keep your context, find the information you need, insert T-SQL language elements directly into your code, and even have IntelliSense complete your typing for you. This can speed up software development by reducing the amount of keyboard input required and minimize references to external documentation.

Declarative Management Framework (DMF) Enhancements

Enhancements to DMF include more expressive conditions (including support for common functions, T-SQL, and WMI queries), more robust target set filtering, custom messages for policies, and the inclusion of best practice policies.

Geo-spatial Support

SQL Server 2008 delivers comprehensive geo-spatial support. The new GEOGRAPHY and GEOMETRY data types provide spatial data support for location-aware applications.  These types can be used to store locations, as well as paths and regions in space, and provide a rich set of functionality for comparing and manipulating these objects.  Use the GEOGRAPHY type when working with latitude and longitude coordinates in a true round-earth model; use GEOMETRY when working in projected planar surfaces, as well as naturally planar systems such as interior spaces.

These types are supported by new spatial indexes, which provide for fast execution of queries involving spatial data.  The query optimizer has been enhanced to build-in knowledge of spatial indexes and types, so that appropriate cost-based plan decisions can be made.

Analysis Services Query and Writeback Performance (FITS)

New MOLAP-enabled write-back capabilities in SQL Server 2008 Analysis Services remove the need to query ROLAP partitions. This provides users with enhanced writeback scenarios from within analytical applications without sacrificing the traditional OLAP performance.

Robust Report Server Platform

Reports can easily be delivered throughout the organization with simplified deployment and configuration. This enables users to easily create and share reports of any size and complexity.

Integration Services - Lookup Enhancements

The need to perform lookups is one of the most common extraction, transformation, and loading (ETL) operations. This is especially prevalent in data warehousing where fact records must use lookups to transform business keys to their corresponding surrogates. SSIS increases the performance of lookups to support the largest tables.

Analysis Services MDX Query Optimizer - Block Computation

Block computations provide a significant improvement in processing performance, enabling users to increase the depth of their hierarchies and complexity of the computations.

Analysis Services Aggregation Design

SQL Server 2008 drives broader analysis with enhanced analytical capabilities and with more complex computations and aggregations. The AS Aggregation Design improvement exposes Aggregation Design objects in SQL Server BI Dev Studio and SQL Server Management Studio and provides tools for users to better work with these aggregation designs. In addition, an advanced view in the new Aggregation Design tab of the cube editor provides the ability for an advanced user to view and manually edit individual aggregations within an aggregation design.

Analysis Services Cube Design

New cube design tools help users streamline the development of the analysis infrastructure, enabling them to build solutions for optimized performance. The AS Cube Design improvement introduces a new Cube Wizard which helps users create better cubes in fewer steps.  The new wizard focuses on having the user answer a few questions to create leaner cubes that better targets their needs.  It also unblocks the previously difficult scenarios of creating a cube a cube based on a single, de-normalized table and creating a cube containing only linked dimensions.

Tuesday, November 13, 2007


Ok, so the name is stupid, but I think the idea actually has some merit!

Microsoft has quietly been putting together a real, honest-to-goodness Internet-facing site that is 100% SharePoint. They call the site SharePointPedia, and it can be found here: (actually that's what the DNS entry gets redirected to, the "real" URL is:

The idea is to collect all things SharePoint related into a single location. Right now the site is a collection of articles and issues that have basically been culled from the MSDN Forums.

Thursday, November 8, 2007

SQL Server 2008 "Katmai" on Windows Server 2008 "Longhorn"

I have recently had the need to work with SQL Server 2008 installed on Windows Server 2008. Since both of these products are in Beta (one more "beta" than the other!), this can really be a challenge.

One of the first things I noticed after installing Katmai (CTP4 - the July CTP) on Longhorn (RC0++) was that you could not connect to SQL Server, even though the installation completed without error. When you attempt, the following happens:


Digging into the error generated, the following TDS error is thrown:



After some digging around, I found the answer to this problem. It just so happens that the version of SqlClient is hosed on the default install of WS08/Katmai and you have to fix it. You do so by installing Visual Studio 2008 "Orcas" Beta 2!


There's a nice post on the Katmai forum that details what does and does not work together with Katmai:

Microsoft PerformancePoint 2007 is Live!

Microsoft announced this morning that their new PerformancePoint 2007 is officially available. PerformancePoint is the next step in brining Business Intelligence information to everyone.

Evaluation editions are available as follows:

There's also new MSDN forums and other content available as follows:

We are happy to announce the Microsoft Forums for Microsoft PerformancePoint Server, ProClarity and Master Data Management. Learn more about these new Forums by reading the announcement on the New Microsoft PerformancePoint Server 2007 Forums. (

Documentation and Training

Check out the Office Developer Portal ( and TechCenter ( for deployment and operations guides, technical whitepapers and 'how-to' help and tips.

Finally, look for the PerformancePoint Server 2007 books (http://sharepoint/sites/BISL/Documents/BookPlan.pptx)and online training webcasts ( , as they should help your prospects, customers and partners get started!

Friday, November 2, 2007

SQL Server Integration Services, Oracle Client and the 64-bit Platform

I was reminded today that I had promised some time ago to create a post about my experiences with SSIS, Oracle and 64-bit environments.

The current project that I am responsible for, Configuresoft's Configuration Intelligence Analytics, is built to extract data from a number of source systems, including those housed in an Oracle Database. One of our major Joint Development Partners has standardized on 64-bit operating systems for all tools, including our CIA product. As it turns out, many of our development environments are 64-bit as well, so from my perspective I figured this would be an easy scenario to support.


As it turns out, the Oracle client is very peculiar when installed into 64-bit environments. The installer will detect the fact that it is 64-bit native, and by default will only register 64-bit instances of itself. This is all well and good of course, *if* you are using 64-bit native components all through the communication channel (i.e, if the application you're building is 64-bit). The unfortunate thing is that the SSIS development environment is a 32-bit application. So, this means that when you initially install a 64-bit Oracle client, you actually cannot use Business Intelligence Development Studio (BIDS) or Visual Studio in order to test (or even validate) the Oracle-facing SSIS components. Fortunately, Oracle has thought that through and provided the ability to install a 32-bit version of the client components. Again, all well and good, except for the fact that by default, when you use DTEXEC to execute the SSIS packages (or use a SQLAgent job), you're actually running a native 64-bit application. ARRRRRRRRRRRRRRGH! So, you test and validate the package in BIDS and it succeeds, but fails when you run for "production" purposes.

To make matters worse, even if you properly install and register the Oracle client (I'm specifically talking about the Oracle 10 client) you'll still have problems due to the fact that there is a bug in the 10.2.x.x Oracle client that precludes the use of many special characters, either in a username or program path (WTF?!?!) that uses the client. The bug is Oracle bug # 3807408 and it is fixed in Oracle patch # 5383042. But wait, there's more!! The path is valid only for the Oracle client, which CANNOT be downloaded anywhere. You must patch a client to get the version. This patch is available (oh by the way, it's only available for 32-bit environments!) as Oracle patch # 4547817.

Oh, and did I mention that you have to pay Oracle for the patches?


By now I'm sure (assuming you've read this far) that you're wishing I'd just cut to the chase...


Oracle and SSIS 64-bit Happiness

The following steps are in my opinion the only steps you can take to get SSIS and Oracle to play well in 64-bit environments:

  1. Install the Oracle 32-bit client (This is the client that is available from Oracle's website)
  2. Install the Oracle # 4547817 patch (which will upgrade your client to
  3. Install the Oracle # 5383042 patch (fixes Oracle bug # 3807408)
  4. Develop your SSIS Packages
  5. When ready to execute the package, do NOT use a SQLAgent SSIS Package task to execute them. Use a CMDExec task that explicitly uses the 32-bit version of DTEXEC (located in Program Files (x86)\Microsoft SQL Server\90\DTS\binn)

The above steps will work 100% of the time. However, you will lose the 64-bit execution environment benefits (better memory management basically) in your SSIS packages. You may want to consider breaking out any package that requires Oracle Connectivity to run as a separate job, that way you can limit the impact of the 32-bit only environment.

Thursday, November 1, 2007

SQL Server Integration Services Package Manager Utility

Joey Demaio's dear friend (and my colleague) Matthew Roche has recently released his SSIS Package Manager Utility (known as PacMan) into the wild on CodePlex.

This tool has been incredibly useful for my team at Configuresoft and I hope it will be useful to you too. If you develop packages in SSIS, you most certainly need this tool!


Using witexport.exe to Generate a TFS WorkItem Type

The "WorkItem" is a very important part of any collaborative development effort. Basically the WorkItem represents a specific task that must be completed by someone in order for the project to move forward. When working with Visual Studio Team System and Team Foundation Server  you will notice that each project has a specific guidance template associated with it. In the case of an out of the box installation, you have the MSF for Agile template and the MSF for CMMI template. Generally speaking, these are good enough for most projects. There are a number of add-on templates available, and Microsoft provides a Process Template Editor (part of the Power Toys now) that allows you to create your own.

In my current project, we are primarily using MSF 4.2 for Agile as our guiding template, but have a need to track both Requirements and Scrum-like sprints. The "Requirement" WorkItem type is included in the MSF for CMMI template that ships with TFS, and the "Sprint" WorkItem type is included with the downloadable eScrum template. The only way to combine these WorkItem types is to extract them from their respective templates and in my case,  import them into the Agile Template.

Exporting a WorkItem Type

Before you can export a WorkItem type, you need to ensure that you have a project created that contains the Type you wish to export. It's probably a good idea to create a project on a development server (Or a sandbox server if you have one available) so that you can keep them around as necessary.

In order to export a WorkItem Type, you must use the witexport.exe utility, which is located in the <install drive>\Program Files\Visual Studio8\Common7\IDE folder. It is actually a very simple utility to use. In my case, the server that I want to connect to is named DEVCIATFS and the project is named CSIDemoeScrum (using the eScrum template). In order to extract the "Sprint Details" WorkItem type, run the witexport.exe utility as follows:


(In case you can't read the capture above, the command line is witexport.exe /f SprintDetails.xml /t DEVCIATFS /p CSIDemoeScrum /n "eScrum Sprint Details" )

The result is a fully-formed XML file that contains the definition for a Sprint Details WorkItem that when imported into TFS looks like this: