I really need to read up on new features when a major release comes out. Just a few weeks ago I learned of a great "new" SQL 2005 function... ROW_NUMBER(). Just in time since SQL 2008 is already out.

For me, this function means a lot less temp tables. I would typically create a temp table with an ID INT IDENTITY(1,1) column to create an DisplayOrder, BatchID, etc. used to group or join on later. Books Online describes the function as "Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition." The syntax is simple, and looks like:

ROW_NUMBER() OVER (ORDER BY ID DESC)

For this example, the data I want to bring back with a DisplayOrder column looks like:

Without ROW_NUMBER(), using a table variable with an identity column:

DECLARE @Subs TABLE (DisplayOrder INT IDENTITY(1,1), [Address] VARCHAR(100), Operation VARCHAR(50), [Contract] VARCHAR(50))

INSERT INTO @Subs ([Address], Operation, [Contract])
SELECT [Address]
    , Operation
    , [Contract]
FROM PersistentSubscribers
WHERE Operation = 'OnEvent2'
ORDER BY ID DESC

SELECT * FROM @Subs

With ROW_NUMBER(), look how beautiful:

SELECT DisplayOrder = ROW_NUMBER() OVER (ORDER BY ID DESC)
    , [Address]
    , Operation
    , [Contract]
FROM PersistentSubscribers
WHERE Operation = 'OnEvent2'

The results from both methods looks like:

Why clutter your inbox with error messages? Why make special code provisions for users to receive error messages via email? Why not log your error messages and have users subscribe to receive them in their favorite RSS aggregator?

If you are logging your exceptions already, you may find it easier to provide a syndication service. The process is ridiculously simple, and starts by creating a new project using the "Syndication Service Library" template. This template creates everything for you. All you need to do now is fill the SyndicationFeed with SyndicationItem objects.

Add a new class file called Feeds.cs:

Modify the App.config file:

You will need to adjust your project's Debug options to have command arguments that look similar to the following to F5-debug your service.

"/client:iexplore.exe" "/clientArgs:http://localhost:8000/Feeds/Errors?env=Production&app=GeoTracker"

Press F5 to test it out.

Here is the IE7 RSS viewer:

Here is your RSS aggregator viewing the same feed:

You will, of course, want to add some additional information to the content of your SyndidationItem, a bogus phrase works for this example.

Also, it is unusual that you would care to keep your exception details around for a long period of time. Since this is a syndicated feed of application errors, you should make special arrangements to archive or delete your exception log on a regular basis. This will not only keep your insert and select times low, but will also alleviate the burden placed on a new subscriber when all of the exceptions from the database appear at once. An alternative would also be to modify the LINQ in the code above to only bring back exceptions from the last 7-60 days depending on your counts. I already archive my exceptions to a master exception repository for all environments by way of an ETL job. This way I can report on my errors without disturbing the live environments too.

I decided to come out of my cave and look around 3.5 a bit. I haven't read much about extension methods, but find them quite useful. They are nothing more than a syntactically superior static helper method. Let's look at a quick example so I can get back to coming up with more excuses to use them everywhere.

I like to batch my database calls as much as possible to avoid repeated opening/closing of connections, etc. To do this, I pass a bunch of ID values into a stored procedure as a comma-separated string. In the stored proc, I break the string apart with everyone's favorite table-valued function fn_MakeTable() to make a table of IDs. Then I can JOIN, UPDATE, or INSERT as needed.

So let's say I have a collection of Orders which I can easily convert to an array of OrderID integers with LINQ. My new best friend to create a comma-separated string of OrderIDs is the following.

You'll see that I have two ToCsv() methods. The first takes a generic array using the this keyword and uses .ToString() as a default converter to string. The second method requires you to additionally pass in a converter to convert the object of type T to a string. Take those converted strings, add them to a CommaDelimitedStringCollection and .ToString() that collection to a full CSV string of integer values.

There are two ways to call these extension methods. The first is the more familiar way. Since they are really nothing more than static helper methods, call them just like any other:

The second is the more elegant and more intuitive way. Call it as if it was built into the Framework:

You may be wondering, what if I write a method that matches the signature of a built-in method like .ToString(). Well, the built-in methods take precedence over extension methods, so array.ToString() will still appear as System.Int32[]. To get your new meaning of .ToString(), you just have to call it in the static helper method way detailed above.

For a generic array of T, you will likely want to provide your own Converter if T's .ToString() method does not display the information you want to show in the CSV string. Below is a lame example of a converter. It takes the int value, converts it to the char value.

I think something so simple, and definitely re-usable, would benefit any developer.

Our friends at Microsoft may have slipped one in on us. After installing the 3.5 Framework Service Pack 1, it appears that you no longer need the [DataContract] or [DataMember] attributes on your DataContracts and DataMembers. I'm not sure what the motivation was for this "enhancement", but it caused some trouble for me the other day.

For this example I will be using the base project VS2008 gives you when you create a new WCF Service Library. I am simply adding a NestedType to the CompositeType given in the base project.

Before installing SP1, having code as it appears below would cause an error during Metadata Exchange that reads something like "Metadata contains a reference that cannot be resolved". Notice that CompositeType's NestedObject is marked as [DataMember] and also notice that the NestedType class is not marked as [DataContract] and has no [DataMember] attributes. Adding [DataContract] on NestedType and [DataMember] on IsVisible will clear this error and everything will work as expected. 

The same code in use after SP1 will not cause this error. WCF will interpret from CompositeType's [DataContract] attribute and NestedObject's [DataMember] attribute that you meant to put [DataContract] on NestedType. So what's the big deal, right? WCF is doing me a solid by guessing at what I meant to do. To me, this violates the repeated opt-in theme present in WCF. For every other important decision, the developer must write code to opt-in to a feature. For example, TransactionFlow defaults to false so we don't use the client's incoming transaction with explicitly writing code that says to do so.

This is clearly not on the same level as TransactionFlow. But why does it assume something about my objects? Why does it assume that every member of my object should be a DataMember?

I noticed this new "feature" when troubleshooting some code that had different namespace names specified in the DataContract attribute. Since the NestedType did not have a [DataContract] attribute, the namespace was using the original namespace name. The equivalent of CompositeType came through correctly, but the NestedObject had no value.

Enterprise applications store their data in a relational database. Our code reads the data stored in tables with many complex joins and business rule laden queries. We take the results of those queries and construct an equally complex business entity that is used by our application logic. Most developers, myself excluded, hate working with the database. Writing, modifying, or even seeing T-SQL causes some developers to itch. LINQ to SQL serves as a partially effective Hydrocortisone to relieve the itch. But they still need to maintain the schema, write SQL-mindful LINQ queries, and deal with the constant DataContext updates.

Imagine a world where you no longer need to translate your complex business entities to and from relational tables.  A world where there is no database backing store. A world where we create our business entities and store them in memory. Even better, in memory on a shared resource. Does it sound like an inconceivable futuristic developer heaven? Well it probably is, but this is really cool stuff in the works.

Enter the Microsoft project code-named "Velocity." The blurb on the overview page reads:

"Velocity" is a distributed in-memory application cache platform for developing scalable, high-performance applications. "Velocity" can be used to cache any CLR object and provides access through simple APIs. The primary goals for "Velocity" are performance, scalability and availability.

I have been working with the Digipede Network, the leading grid computing software solution, for a few months. The Velocity architecture sounds remarkably similar to Digipede's. I have seen the great benefits of the Digipede Network and have high expectations for Velocity.

The Digipede Network, for those of you that haven't seen it yet, consists of a central Digipede Server and one or many Digipede Agents. The server receives client requests and assigns tasks to the agents. The client uses the Digipede API to communicate with the server. The API pretty much wraps client-to-server and server-to-client WSE2 web service calls. This architecture allows you to take almost any CPU-intensive process and spread the workload among tens or hundreds of commodity or server grade machines. The result is a very high performing and easily scaled system with few code changes from what you do today.

Digipede Network Diagram:

Digipede only works in this configuration, while Velocity has two proposed deployment models. You can have a "caching tier", similar to Digipede's Server and Agent configuration, or you can house Velocity as a Caching Service directly in IIS7. I don't know how communications will be handled between the client API and the "caching tier", but I assume it will be some sort of service calls (WCF perhaps). All CLR objects stored in the Velocity cache must be marked [Serializable] just as task worker classes must be to work with Digipede.

The Velocity API looks simple enough too. It exposes intuitive Get() and Put() methods where you call the cache by name. I can see how versioning of the cached objects might get tricky. Your application will also need a new configSection that specifies the deployment mode, locality, and also contains the list of cache hosts. As this is a distributed solution, the standard virtual machine playground doesn't work too well to really test this out.

This looks promising, and I'll be following the progress of the project closely.

Download Velocity

Download the Velocity CPT 1 samples

A few months after SQL 2005 was released and hit the productions servers, some people started experiencing some odd behavior in their stored procedures. Simple stored procedures that normally return in 0 seconds would take upwards of a minute to return. Even more strange was the fact that the same query, outside of a stored procedure, would still return in 0 seconds.

It never affected me personally... until today. Three years late to the party. It's funny how much more interested I am in the causes and solutions for this apparent problem when it affects me. "Parameter Sniffing" is the term Microsoft uses to describe the feature that causes this odd behavior. While it appeared as an issue when I encountered it today, I found that the feature is not only well-intentioned but quite useful.

The execution plan is generated and cached the first time your stored procedure is called. When the execution plan is being created, SQL Server reads the input parameters and uses them to optimize the execution plan for those parameters. This is called "parameter sniffing." If the input parameters used in the first call to the stored procedure are atypical for the overall use of the stored procedure, a less than ideal execution plan will be cached for all subsequent calls.

Simply dropping and recompiling the stored procedure does not seem to affect the cached execution plan. Updating statistics on the tables used in the stored procedure will cause the execution plan to be regenerated on the next call of the stored procedure. However, if the same or similar atypical parameters are used on the first execution of the stored procedure, an equally sub-optimal execution plan will be cached.

You can turn off parameter sniffing. This is accomplished by assigning the input parameter values to local variables inside the stored procedure and then using the local variables within the stored procedure. When the execution plan is created, SQL Server will look at the table statistics to optimize the query for the "average" use. It does this by looking at the tables used in the query and analyzing row counts, etc. to find a reasonable plan that will likely suit a majority of situations.

My stored procedure was bringing back multiple resultsets to be used to create a hierarchical structure in code. It works essentially like the following:

CREATE PROCEDURE [dbo].[usp_Order_GetOrderDetails]
(
   @StartOrderId INT,
   @EndOrderId INT
)
AS
BEGIN
   SELECT *
   FROM Order
   WHERE OrderId BETWEEN @StartOrderId AND @EndOrderId
 
   SELECT *
   FROM OrderLineItem
   WHERE OrderId BETWEEN @StartOrderId AND @EndOrderId
END

I was testing the stored procedure for full day using the same ID for @StartOrderId and @EndOrderId. Since the intended use of this stored procedure is almost always @EndOrderId = @StartOrderId + 1000, this makes a big difference when calculating the estimate number of rows returned. I forced SQL Server to assume that my execution plan should be based on an ID range of 1 instead of 1000. Turning off parameter sniffing lessens these effects.

To turn off parameter sniffing, it would look like this:

CREATE PROCEDURE [dbo].[usp_Order_GetOrderDetails]
(
   @StartOrderId INT,
   @EndOrderId INT
)
AS
BEGIN
   DECLARE @Start INT
   DECLARE @End INT
   SET @Start = @StartOrderId
   SET @End = @EndOrderId
 
   SELECT *
   FROM Order
   WHERE OrderId BETWEEN @Start AND @End
 
   SELECT *
   FROM OrderLineItem
   WHERE OrderId BETWEEN @Start AND @End
END

This immediately improved the performance of my stored procedure. The time to complete reduced from ~2 minutes to ~2 seconds for my typical 1000 ID range (I know 2 seconds is a lot, but these tables have millions and millions of rows). But only one piece of code in the application calls this stored procedure, and 99 out of 100 times it will have a range of 1000 IDs. Why would I want SQL Server to guess how many Orders I will typically bring back when I know the exact number?

I should have the optimal execution plan if I update statistics on Order and OrderLineItem, and then call usp_Order_GetOrderDetails 1, 1000 after I compile this stored procedure. This sounds like a lot of work to me, and I did not notice any performance boost by doing this. I chose to leave parameter sniffing off.

The only drawbacks to turning off parameter sniffing is the weird looking SQL and the inevitable questions during code review about the crazy input parameter to variable mapping. But when you school the doubters on the causes and effects of parameter sniffing, it will put another notch in your guru stick.

From what I have read, this was not a new feature in SQL 2005. I can't, however, find any mention of it in SQL 2000 books online, and this feature never showed its face in SQL 2000.

Juval Löwy mentioned the Microsoft Service Trace Viewer in a webcast today. If you ever wondered exactly what WCF does under all of those covers, check this out.

First things first. Enable tracing on the client and host applications using the WCF Configuration Editor. Enable the verbose trace level and check all of the listener settings. This will add all of the necessary <system.diagnostics> settings in your config file. The next time you start each of the applications, a .svclog file will be created that will be used by the Service Trace Viewer.

Start your host, start your client, run through the test cases that you want to analyze in the viewer. After your test run is complete, open the viewer, located at C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SvcTraceViewer.exe. "Open" the host.svclog file, and then "Add" the client.svclog file. Both "Open" and "Add" are menu items under "File".

Start on the Activity tab, look through the host and client activities that occurred. Everything from ServiceHost construction through ServiceHost closing shows up. This is very cool, especially when analyzing the differences between different security, session, and reliability settings.

When you are done looking through the activities, check out the Graph tab. Here you can look at the interactions between the client and host, as well as looking at the details of each activity (at the top right). At the bottom right, you will also notice the formatted and xml details of this activity.

This is a very cool tool for both debugging and training. Below is my lame test projects, if you want to skip past the configuration and check out the tool. My .svclog files are located in the Client and Host folders.

SvtTest.zip (190.32 KB)

Enjoy! Thanks to Juval for the direction.

I couldn't take the X-Files-esque output window anymore. Here are my spinoffs of the ZenBurn (recently posted as ScottGu's) theme.

VSSettings1.zip (118.09 KB)