Simple-Talk

Getting the URL to the Content Type Hub Programmatically in SharePoint 2010

Damon — Tue, 07 Feb 2012 17:23:38 GMT

Many organizations use the content-type hub to manage content-types in their SharePoint 2010 environment. As a developer in these types of organizations, you may one day find yourself in need of getting the URL of the content type hub programmatically. Here is a quick snippet that demonstrates how to do it fairly painlessly:

public static Uri GetContentTypeHubUri(SPSite site) { TaxonomySession session = new TaxonomySession(site); return Session.DefaultSiteCollectionTermStore .ContentTypePublishingHub; }

Red Gate and the Community

RedAndTheCommunity — Tue, 07 Feb 2012 02:14:00 GMT

I was lucky enough to join the Communities team in April 2011, having worked in the equally awesome (but more number-crunchy), Finance team at Red Gate for about four years before that.

Being totally passionate about Red Gate, and easily excitable, it seems like the perfect place to be. Not only do I get to talk to people who love Red Gate every day, I get to think up new ways to make them love us even more.

Red Gate sponsored 178 SQL Server and .NET events and user group meetings in 2011. They ranged from SQL Saturdays and Code Camps to 10 person user group meetings, from California to Krakow.

We've given away cash, software, Kindles, and of course swag. The Marketing Cupboard is like a wonderland of Red Gate goodies; it is guarded day and night to make sure the greedy Red Gaters don't pilfer the treasure inside. There are Red Gate yo-yos, books, pens, ice scrapers and, over the Holidays, there were some special bears. We had to double the patrols guarding the cupboard to protect them. You can see why:

Over the Holidays, we gave funding and special Holiday swag (including the adorable bears), to 10 lucky user groups, who held Christmas parties - doing everything from theatre trips to going to shooting ranges.

What next?

So, what about this year? In 2012 our main aim is to be out there meeting more of you. So get ready to see an army of geeks in red t-shirts at your next event!

We also want to do more fun things like our Christmas party giveaway.

What cool ideas do you have for sponsorship in 2012? An Easter Egg hunt with SQL server clues? A coding competition? A duelling contest with a license of SQL Toolbelt for the winner? Let me know.

From nowhere to somewhere in no time at all.

fatherjack — Tue, 07 Feb 2012 02:00:00 GMT

I am pleased to introduce another guest post from my wife, Annette (@mrs_fatherjack) (this also partially explains the cat picture). 2 years ago I'd never heard of SQL Bits, I wasn't into 'the community' in any way and just plodded along doing what I did, probably not very well. I was employed as a SQL Developer but in reality for the past few years my role had been primarily management with very little development. I went to SQL Bits 6 (London) with my husband, Jonathan (@Fatherjack), not really...(read more)

To Not CI to Eye

Tony Davis — Fri, 03 Feb 2012 11:21:57 GMT

Many developers, including Troy Hunt, here on Simple-Talk, have argued persuasively that each database developer in a team needs to work as sole user of a dedicated database-development environment whilst creating or updating databases. Troy makes a good case, listing several shortcomings of the shared development model: developers are no longer free to experiment and pursue evolutionary design of the database layer, in the same way as any other component of the application, without unduly interfering with the other developers; without the required discipline of checking in changes for continuous integration, source control for the database is hard to enforce; it's very hard to run reliable tests with data dependencies when everyone is free to change the data structures and data.

And yet our surveys suggest that around 80% of database development still takes place in a shared environment, and for valid practical reasons. It isn't just about the data, though for enterprise databases, holding perhaps terabytes of data, and with that test data generated by complex ETL processes, it is no trivial task to replicate this environment in each developer's sandbox database.

Troy's model seemed to assume that developers within the shared model couldn't also access their own sandbox servers, and also that they had no source control. Source Control certainly can be done in the shared model, but just requires more supervision when changes appear that weren't in source control. Many of the consequences he described were due more to this lack of source control, rather than working together on a development server.

Perhaps the strongest argument for the dedicated model, however, is the need to maintain a constant, stable base against which to develop, in which the DDL code in Source Control is used for Continuous Integration. However, does this CI argument really apply equally to the database as to the application? For any enterprise database of reasonable complexity, the up-front design effort should mitigate the need for continuous evolution of the database design, during development. Also, with many database changes being immediately validated, the value to be gained from dedicated development plus CI, to find out if you broke anything, is surely lessened?

Could it be that many of the difficulties of integration and deployment that have been so thoroughly documented are more due to poorly thought-out application-interfaces within the database? Integration problems that are due to version differences are far more likely where this has been omitted from the design, and applications have unrestricted access through the database. Are we in danger of trying to change database development practices to try to cure a problem that is far easier to solve by proper system design?

I'm sure many will disagree; as always I'd love to hear about it.

Cheers,
Tony.

Red Gate does Burns Night 2012

red@work — Thu, 02 Feb 2012 04:00:00 GMT

Whilst we have our fair share, as a company, of native Scots, somehow none of them managed to attend Red Gate's recent Burns Night celebration. Troublingly, this didn't seem to trouble us.

A Burns Supper should not, in theory, be hard to organise. We asked our head chef, Big Steve, if he'd put on a bit of haggis and whisky for us. Danielle, a Red Gater with shady connections in such circles, managed to persuade the Cambridge University Ceilidh Band to provide us with a little post-supper dancing. Our very own Brian Harris, Bard of Ayrshire, Scotland's fairest flower (for the evening), would address the haggis. Everything was going swimmingly.

Big Steve, Red Gate's resident chef, menaces a haggis.

The first hints of disaster became apparent just after lunch on the day itself, when Brian emailed me a full itinerary which laid out all the traditional constituent parts of a well-planned Burns Night supper. All we had was whisky, haggis and an imitation of a Scottish accent which bordered on racism.

After several hours of frantic scrabbling, the thing began to come together. Big Steve was in calm control of the culinary proceedings, and had sourced us not only a plethora of haggi, but also a litre and a half of Tesco Special Reserve whisky (aged three years), three vegan haggises for those with more delicate constitutions, and a hearty selection of oatcakes. Oatcakes!

Haggis, oatcakes, whisky gravy. Not bad for a Tuesday at work.

We had no piper to pipe in the haggis, so we made a 21^st Century improvisation: an MP3 of bagpipe music hooked up to the Red Gate PA system. This had the excellent (but unintended) side effect of annoying Charles Brown, head of the .NET division, who has an apparent horror of bagpipe music. A hand he will come to regret having revealed to me in time, I guarantee.

What we lacked in authentic Scottish people we made up for in numbers and enthusiasm. Our very own Rabbie Chipperfield, sometime developer, gave the Selkirk Grace. Brian Harris addressed the haggis in his best Scotch burr, a performance which was met with particular puzzlement from some of our European guests.

With the Tesco Special Reserve whisky safely stowed within, I gave a brief speech to the immortal memory of Burns himself. The lads then toasted the lasses, and the lasses toasted the lads, and we got down to the serious and important business of haggis consumption. The vegan haggis proved overwhelmingly popular, and there was such a glut of the normal stuff that the good people of Red Gate were treated to both chicken stuffed with haggis and "just haggis, suitable for salad" at lunch the following day.

After the ceremony, those brave souls who had survived the aural and digestive onslaughts of the supper were rewarded with an evening of ceilidh dancing. Och, aye.

Post by Lucy Boyes. Photos by James Billings.

ASP.NET performance: is it all about the database?

Michaela Murray — Wed, 25 Jan 2012 07:09:00 GMT

Developing ASP.NET applications with a data layer, you may be well aware of the pain that performance issues can cause. Perhaps your apps response times drag, connections lag, and the overwhelming user experience is of watching the "page loading" graphic spin endlessly round and round. Sure, your application may be performing poorly. But you don't need a performance profiling tool to fix that, right? What you really need is a better DBA.

Here's why you might be wrong.

Think your ASP.NET applications performance problems all lie in the database? Prove it!

Yes, database issues underlie a lot of ASP.NET performance problems. Time spent retrieving data can dwarf the time your application spends executing your code. But that doesn't mean the problem is out of your hands. To begin with, just assuming the problem lies in the database is a quick route to a smug DBA if it later turns out you’re wrong. Far better to know for sure how much of your applications time is spent waiting around for the database to do something, and how many round-trips to and from the database it makes.

So, how do you know for sure what your application is telling the database to do? If you have a really simple application, and wrote it all yourself, you may genuinely just know. But if your application uses enterprise framework or legacy code, has multiple developers, or has just been sitting around without your attention for a while, reviewing the static source code or stepping through line by line might take too long and fail to show up lurking problems. With a performance profiling tool, on the other hand, you can see exactly what the application does as it runs, pinpoint the calls it makes to the database, and show you at a glance what proportion of your execution time is spent waiting for the database.

Visualizing all of your applications database interactions gives you a quick, comprehensive view of how your code interacts with the database. If you see you’re telling the database to do something perverse, or inefficient, you can make big performance savings by simplifying and optimizing. You might find that data caching, enabling paging, or making better use of background processing makes the whole thing run faster and more smoothly. Secondly, if your own code really is unimpeachable, a visualization like this gives you the smoking gun to brandish at your DBA.

When It’s Not The Database

Sometimes you’ll find yourself with a database problem and no one to blame it on. For example, if your applications a custom frontend onto a commercial database, changing the way that database works might be out of your hands. Then you’ll need to look at interfacing with it more efficiently, or concentrate on improvements to your own codes performance.

Occasionally, and it takes a brave dev to admit this, you may even find that the problem is actually in your code, not in the database.

In both cases, profiling first lets you find the true culprit – be it the database or the .NET code – and therefore find the best solution. That might mean tweaking a method call, or it might, indeed, mean marching down to the sysadmin basement with the incontrovertible truth in your hands.

Inside the ConcurrentCollections: ConcurrentQueue

Simon Cooper — Tue, 24 Jan 2012 22:03:00 GMT

ConcurrentQueue is, like ConcurrentStack, a lockless collection, in that it is implemented without using any locks at all. However, the semantics required for a queue impose a quite different approach; unlike ConcurrentStack, which has a single point of concurrent contention, a queue can be changed at both the head and tail. This means that at least two variables are involved, most likely more. The simple approach of atomically modifying a single variable won't work here.

What does `System.Collections.Generic.Queue` do?

Well, let's have a look at the non-concurrent queue. This is implemented as a circular buffer. However, this design is very hard to use in a lockless way; when a new item is enqueued and the backing array is full, the array is doubled in size to accommodate the new item.

To resize the array, the entire contents of the queue has to be read sequentially without using locks and copied into a brand new array by a single thread, during which other threads can enqueue and dequeue items many times. This is very tricky to implement without being subject to race conditions and still ensure high performance.

So, instead, ConcurrentQueue forgoes the circular buffer, and uses a linear array instead. More specifically, it needs to be a linear array with a (conceptually) infinite capacity - although there will only be a finite number of items stored in the queue, items can be enqueued and dequeued an unlimited number of times. Such an array can't be created as a single object.

However, you don't need to keep the entire infinite array in memory, you only need the section of the array that is actually storing items. By splitting the array into finite segments you can create and discard segments as items get enqueued and dequeued to the queue. This is what ConcurrentQueue does.

Queue Segments

To demonstrate, I'll be using a queue with a segment size of 4. To start off with, three items are enqueued. These are added to the leftmost available slot of the tail segment:

Three more items are then enqueued. A new segment is created and assigned to the tail:

And another three:

Five items are then dequeued from the head. The head segment is now empty, so it is discarded:

Using this method, the illusion of an infinite linear array can be created using a finite number of segments.

Achieving thread-safety

ConcurrentQueue uses a segment size of 32, and along with the backing array each segment stores the index items are to be added (m_high) and where they are to be removed (m_low):

private class Segment {
    volatile T[] m_array;
    volatile int m_high;
    volatile int m_low;
}

Thread-safety, in this case, depends on the operation of both enqueuing and dequeuing. So we'll look at the operations in isolation, then put them both together.

Enqueuing

To enqueue an item, you need to:

Increment the m_high variable. This is now the index the item should be inserted at.
Assign the item to the specified slot in m_array.

The key to this is step 1. If step 1 can be done atomically, returning the new incremented value, that gives each thread trying to enqueue an item at the same time separate slots. Step 2 can then be performed concurrently by each thread without having to worry about conflicts. Interlocked.Increment performs this increment atomically:

public void Enqueue(T item) {
    int insertIndex = Interlocked.Increment(ref m_high);
    m_array[insertIndex] = item;
}

That's it! Simple, really...

Dequeuing

Dequeuing acts in a similar way to enqueuing:

Increment the m_low variable. The previous index is the index of the item to be dequeued.
Return the item stored at the specified slot in m_array.

And, similarly, if each thread trying to dequeue an item can be atomically 'assigned' a slot by performing step 1 using Interlocked.Increment, then step 2 can be performed concurrently by several threads:

public T Dequeue() {
    // Increment returns the incremented value of the variable
    int index = Interlocked.Increment(ref m_low);
    return m_array[index-1];
}

But hang on, if we're dequeuing items, we need to check whether the queue is empty first. So checks need to be added to determine when a queue is empty. This depends on the segment state that represents an empty segment. In the case of ConcurrentQueue, this state is when m_low > m_high.

The exact reasons for this specific state representing an empty segment are a gory implementation detail; if you're wondering the reasons for this, then start by working out exactly what m_high and m_low represent.

public bool TryDequeue(out T item) {
    if (m_low > m_high) {
        item = default(T);
        return false;
    }
    
    int index = Interlocked.Increment(ref m_low);
    item = array[index-1];
    return true;
}

All good? Well, no. We've actually introduced a subtle race condition. If we've got a queue with one item in it, and two threads trying to dequeue that one item, then if both threads perform the if (m_low > m_high) check before m_low has been incremented, one will dequeue the item and one will successfully dequeue on an empty queue.

In ConcurrentStack, a similar race condition was solved by using Interlocked.CompareExchange; taking a snapshot, doing some work, then atomically making that work visible to other threads only if the state is still valid. By judicious use of local variables and loops, we can do the same thing here:

public bool TryDequeue(out T item) {
    int index;
    do {
        index = m_low;
        
        // check if the queue is empty
        if (index > m_high) {
            item = default(T);
            return false;
        }
        
        // I think m_low has the same value as index. If it is, replace it with index+1.
        // Return to me what m_low actually was.
        if (Interlocked.CompareExchange(ref m_low, index+1, index) == index) {
            // success - index is now a valid index to dequeue, specific to this thread
            item = m_array[index]
            return true;
        }
        
        // m_low has been changed in the meantime. Go back to the start and try again.
    }
    while (true);
}

In this case, CompareExchange is acting as a conditional Increment.

Combining the two

This is where it gets complicated. The most obvious issue is another race condition, between enqueuing and dequeuing. To reiterate, the steps performed are:

Enqueuing:

Increment the m_high variable. This is now the index the item should be inserted at.
Assign the item to the specified slot in m_array.

Dequeuing:

Check if the queue is empty (m_low > m_high). If it is, return false.
Increment m_low (if m_low hasn't changed) and return the item.

Start with an empty queue. Thread 1 executes step 1 of enqueuing; m_high is incremented. Thread 2 then tries to dequeue an item; step 1 of dequeuing thinks the queue has items because m_low <= m_high. Step 2 of dequeuing then executes, and the item dequeued is the null value, because thread 1 hasn't yet assigned the enqueued item to the backing array.

Hmm, how to solve this? There's more than one variable involved here; the race condition involves the relationship between two variables, m_high and m_low, not a single variable being changed behind the scenes, which was the problem solved previously using CompareExchange and loops.

Well, we can't swap round the order of operations for enqueuing, because step 1 is what atomically assigns a slot when several threads are enqueuing items at once.

What we need is some sort of notification that enqueuing has finished assigned the item to the backing array. Then the dequeuing thread can wait for that notification before dequeuing the item from the queue:

Enqueuing

Increment the m_high variable. This is now the index the item should be inserted at.
Assign the item to the specified slot in m_array.
Set state indicating the item at that index has been enqueued.

Dequeuing:

Check if the queue is empty (m_high >= m_low). If it is, return false.
Increment m_low (if m_low hasn't changed).
Wait for the state to be set at the index to dequeue.
Return the item.

This is the role played by the m_state array in ConcurrentQueue.Segment. Each segment has an int[] m_state alongside the T[] m_array; a 1 in a particular slot indicates the item has been set, and a 0 (the default value) indicates it isn't set. This turns the Enqueue method into this:

public void Enqueue(T item) {
    int insertIndex = Interlocked.Increment(ref m_high);
    m_array[insertIndex] = item;
    m_state[insertIndex] = 1;
}

and TryDequeue into this:

public bool TryDequeue(out T item) {
    int index;
    do {
        index = m_low;
        
        if (index > m_high) {
            item = default(T);
            return false;
        }
        
        if (Interlocked.CompareExchange(ref m_low, index+1, index) == index) {
            // busywait until m_state[index] is set
            while (m_state[index] == 0) {}
            
            item = m_array[index]
            return true;
        }
    }
    while (true);
}

By marking both m_array and m_state as volatile, this stops the JIT reordering accesses to those arrays, ensuring that the invariant implicit in this code (m_state[index] == 1 if and only if m_array[index] has an item in it) doesn't get broken by the JIT or processors reordering instructions.

Tweaking the control flow a bit, and sprinkling calls to SpinWait.SpinOnce() around as the abstraction of a busywait, and you've got the code of the enqueue and dequeue methods of ConcurrentQueue.

Final notes

There's a couple of things I need to briefly mention. Firstly, I haven't yet covered growing the queue. Creating new segments when the current segment gets full is largely handled by enqueuing. When an item is added to the last slot in a segment, the Enqueue method creates a new segment and assigns it as the next segment in the queue using the m_next variable. When the dequeue method reaches the end of the current segment, it waits for the m_next variable to be set before looking inside that segment to see if there's an item to dequeue.

Secondly, the eagle-eyed among you might have noticed that dequeued items don't get removed from the backing array; they are still referenced from the array when items are dequeued. I can't see any specific threading reason for this, other than giving one less location for possible threading issues. However, more importantly, this guarantees that the invariant I mentioned above is never be broken, which can be important if you're reasoning about the collection.

Keeping references around longer than they need isn't so big an issue; the items referenced in the array are dereferenced all together when the containing segment is dereferenced. However it is something to bear in mind if you have large objects referenced in a ConcurrentQueue for a long time; they might not be garbage collected when they otherwise could be.

Concurrent principles

So what principles can we extract from this examination of ConcurrentQueue?

Separating threads

Increment and CompareExchange are used to atomically assign each thread performing an action a separate slot. Each thread can then do work on that slot concurrently without conflicting with each other.
Invariants

An invariant is used to eliminate a race condition between enqueuing and dequeuing, in this case, m_state[index] == 1 iff m_array[index] has an item in it. This ensures enqueuing and dequeuing on the same slot works as it should. Note that this invariant is maintained even at the cost of keeping things in memory longer than they should.
volatile used as a memory barrier

Furthermore, volatile is used to add memory barriers ensuring the JIT doesn't reorder m_state and m_array array accesses and inadvertantly break the invariant.

Well, that's the core of the ConcurrentQueue class. I haven't even begun to cover the supporting methods, and glossed over most of the gory details. These I leave for you to explore and figure out. It's time to move on! We finally get some locks involved, and peer under the covers at ConcurrentDictionary.

Can desktop software be Lean?

benc — Tue, 24 Jan 2012 16:21:01 GMT

Eric Ries and the Lean Startup movement seem to have become the hottest trend since Agile. The Financial Times already ranks Ries' book alongside Clay Christensen's Innovator's Dilemma and Geoffrey Moore's Crossing the Chasm for changing the way we think about innovation and entrepreneurship.

While the book includes "Startup" in the title, and startups are where the ideas were first formulated, its principles are applicable to almost anyone building something new; including point releases of a desktop application!

At the heart of the movement is the concept of the "build-measure-learn" feedback loop.

Ries argues that in order to improve a product you need to track the effect of changes you make to your application. If you're not measuring these changes then you have no idea whether you're actually improving the product. Increasing sales might be attributable to better marketing and not a better product. In fact, unless you measure what you're doing you might actually be making your product worse, not better. It's fairly obvious when you think about it; by being more scientific, formulating hypotheses and running experiments we can eliminate a lot of the opinion and guesswork that can lead a project to ruin.

The Lean Startup movement, borrowing from the Toyota Way, also encourages us to work in smaller batch sizes because "finding out sooner is much better than finding out later". The shorter the feedback cycle the quicker you can adapt. The risk of spending six months working on a feature that nobody wants can be completely eliminated.

Many website and web applications have already taken the principle of smaller batch sizes to its logical conclusion and have adopted continuous deployment. Releasing new updates everyday takes discipline, especially with regard to testing, but is not uncommon. Well-established analytics solutions make tracking improvement simple.

By contrast, adopting build-measure-learn for desktop applications is much harder.

The tools for tracking feature usage of desktop apps are less well-established, so measuring improvements is much harder. Worse still are the deployment issues. Whereas a website can be updated by rolling out the latest build to a single server, for a desktop application to be updated every client needs to be upgraded.

As the end user of the application has to do this themselves, it is impractical to release at anywhere near the same rate. Asking them to update on even a weekly basis results in disgruntled users (iTunes) and a proliferation of versions when they fail to upgrade.

Instead of employing a continuous release cycle, most desktop vendors seem to have resigned themselves to the status quo: release cycles measured in quarters not days, imprecise usage data, and long spells of coding in the dark.

If we're lucky, we try to validate what we're doing with UX tests and beta programs but this is imprecise compared to the web's best practices.

This doesn't have to be the case. With Chrome, Google completely eliminated the pain of updating. As a result they are able to release as regularly as they like, and this has helped give them a major edge in the browser wars. Dropbox, another big name, have also been silently updating for years.

Implementing a comparable system, even using Google's Omaha framework, isn't easy though.

That's why at Red Gate, we're currently trying to build a service that lets developers silently update their application. We want desktop developers to enjoy all the same advantages as those who code for web. Desktop software shouldn't become a second class citizen just because it's too slow to adapt.

We're currently trying to get as much feedback as possible, so if you're interested in learning more please check out Quiet Deploy or leave a comment below! We'd love to talk to you.

* We're also expanding our desktop analytics effort. If you're interested in learning how people are using your desktop app visit Application Metrics.

Educational Programming

Dave Convery — Fri, 20 Jan 2012 13:50:00 GMT

At last, we’ve woken up to the worrying fact that there just aren’t enough good programmers to go around. Instead of aiming to get a generation of students interested in building their own software, education has instead been compelled by successive governments to focus on word processing, presentation graphics, and stultifying vocational work geared to office skills. Something needs to change; the good news is that change may have already started.

Thankfully the current UK government, who were previously expected to be more likely to make Latin compulsory, have listened to cries from industry and done away with rigidly-specified ICT lessons, to replace them with an ‘open-source’ curriculum that returns to schools the decision of what to teach.

Joel Spolsky recently wrote about a New York school that he’s supporting – one that will focus on finding students with a passion for coding and nurture them, rather than skimming off the kids with the best grades for a subject that they don’t necessarily have any affinity for. If successful, there are plans to roll this model out further. Schools everywhere already have valuable support from organisations like MIT, who have created an excellent resource in their Scratch programming language, and PASS, whose volunteers have been working to engage with students.

There are initiatives in hardware like the Raspberry Pi, a tiny, powerful and ultra-cheap Linux box that’s designed to lower the barrier to entry for kids to get started with programming. More importantly, it opens the way to genuinely experimental programming, the sort of thing that produced so many of the great hobbyist programmers of the 80s and 90s. It plugs directly into a TV, and supports USB inputs so that it allows anyone to get set up for very little outlay (the highest-spec version of the machine costs only $35), which will hopefully also make it attractive to schools that would otherwise struggle to buy much dedicated programming hardware.

Beyond the obvious benefit to the students, these initiatives could well help the software industry itself. By getting kids interested in programming at a younger age, we may at last reverse the growing gender gap in future by reducing the perception of programming as being a masculine discipline (something Girls in Technology are already doing good work on). And of course employers would love increased competition for good jobs.

It’s not all good news though, as a drought of great programmers also means a drought of qualified, passionate teachers. Even with the current, much more limited ICT classes, it’s estimated that, in the UK, only 30% of the teachers responsible for these classes actually have direct, relevant education in what they’re teaching. There are many lucrative careers open to good coders, and this is especially the case with the more mathematically-minded where their skills are in demand for the huge amounts of data wrangling needed in the financial sector. It’s not easy to persuade someone that, after some very expensive years at college, they should head into a relatively low-paid line of work, no matter how ‘rewarding’ it might be.

What languages do you think we should be teaching kids? How should we do it? How would you persuade great programmers to pass on their skills and enthusiasm to the next generation by teaching programming?

It always works on my machine.

Brian Donahue — Wed, 18 Jan 2012 10:17:00 GMT

Probably the most common question that the Red Gate developer tools support gets is "Does your X work with Y?" where X is your bit of software and Y is a bit of software made by a different company. This is probably the least answerable question in the known universe. Start with the obvious - "Does X work?" - full stop. Because with millions of possible configurations in Windows, it's entirely possible that it won't "work" at all. But I can personally guarantee you that it "works on my machine"! Second of all, what does "work" mean? Several things that come to mind:

Can X and Y peacefully coexist on the same computer?
Does X extend the functionality of Y?
Is X a replacement for Y?
Can the output from X be processed by Y?
Does the new version of Y cause the old version of X to fail?
If I run X and Y at the same time, will it Div/0 and open a hole in the universe?

Finally, as I already mentioned, the question could be about Fear, Uncertainty, and Doubt. For instance, if a new version of Microsoft .NET Framework comes out, there is a doubt that your program will not function correctly with it. I've heard of a dutiful network administrator mass-emailing every vendor he has bought any software from when a new version of SQL Server is on the horizon - "Does X work with the new version of SQL Server"? I believe it's a bit lazy to ask a question this way and you may not get the answer you expect, unless you are perhaps more specific about the particular functionality you are interested in.

Inside Red Gate - Experimental Results

Simon Cooper — Tue, 17 Jan 2012 17:16:00 GMT

As a brief interlude from my Concurrent Collections series, I thought I would give an roundup of how the lean startup experiments have been progressing. As you can expect, there's been some good aspects and some bad aspects.

The experiments so far

After lots of discussions, arguments, posing and ruling out hypotheses, we came up with two 'starter' hypotheses we could test fairly easily:

Customers don't agree to send data on how they use SmartAssembly; which features they use, the versions of .NET they have installed, etc, because the consent dialog doesn't make it clear the data is all anonymous and aggregated.
This is a prequisite for further experimentation. SmartAssembly isn't a webapp, with google analytics and web logs telling us everything. In order to conduct experiments on SmartAssembly, we need to know how customers are using it once it is installed on their machines, and they need to confirm it's ok for us to collect that data. Our current acceptance rate for usage reporting on SmartAssembly itself is quite low, so we needed to improve this for future experiments.
Customers aren't using feature usage reporting and error reporting within their own applications because those options are swamped amongst all the obfuscation options.

Experiment 1

Hypothesis: Customers don't agree to send data on how they use SmartAssembly because the dialog asking for consent doesn't make it clear the data is all anonymous

The experiment for this is pretty obvious - improve the wording on the consent dialog. This change was applied to SmartAssembly 6.5. We would compare signup rates with 6.2 to see what effect, if any, this change would have.

Result: Inconclusive

We found after 6.5 had been released that we weren't collecting the right data from our download and install process to be able to accurately calculate an acceptance percentage. One of the quirks of the existing feature usage instrumentation is that the answer users give to the consent dialog is used for all future versions of the product with the same major version.

Since 6.5 is a minor version upgrade from 6.2, this means we couldn't differentiate between an existing customer downloading to upgrade from <6.2 to 6.5 (who wouldn't be presented with the new consent dialog), and a new user downloading for the first time. The data we collected couldn't be interpreted one way or the other; there were too many other variables.

Experiment 2

Hypothesis: Customers aren't using feature usage reporting and error reporting within their own applications because those options are swamped amongst all the obfuscation options

To perform this experiment, we produced a version of SmartAssembly that only had merging, signing, feature usage and error reporting available. We only wanted to present this version to customers whom we knew were downloading SmartAssembly for the error and/or feature usage reporting. The only place we could reasonably guarantee this was a reporting-specific landing page. So, the download link on that page would be A/B tested between the reporting-only and standard version of SmartAssembly.

Result: Not enough data

Our limited scope for this test - one specific landing page amongst the many pages on SmartAssembly where customers could download - meant we got very few people downloading the reporting-only version of SmartAssembly; not enough for any conclusion to be drawn one way or the other.

We had also added a 'cop-out' link on the download page so people could guarantee to get the standard version, if they did happen to download the reporting-only version and wonder where all the obfuscation went. We suspect a lot of users clicked this link; unfortunately, it was untracked, so we don't really know.

It's not all bad news...

So, the first experiments we performed didn't really work. However, that doesn't mean they weren't useful. We worked through a lot of the infrastructure issues that restricted our experimentation, and, most importantly, we learnt things:

Make sure you know what the experiment is for, and what data you will collect to validate or refute your hypotheses. Check the data you will be collecting will make sense.
For an A/B test, ensure you will have a sensible number of users on both sides to able to draw conclusions.
Don't allow users to opt-out of experimental builds, even if it's not marketed as an 'experimental' version. Don't blur the boundaries of experimental and non-experimental builds.

So what now?

We've decided we're going down the wrong route. We shouldn't be trying to target existing users of SmartAssembly, who are looking primarily for an obfuscation tool, we need to target new users. We've decided to pivot and create a new product, a new website, and a new analytics platform. If you're interested, there are samples of the kind of data you could have on your applications at http://www.applicationmetrics.com, as well as a sign up to receive more information and take part in our experiments.

A new toy - .NET Demon

Alex.Davies — Mon, 16 Jan 2012 16:17:00 GMT

I'd like to present a new tool for .NET Developers that we've been cooking up in the Red Gate .NET team. It's only a beta at the moment, but it works for most people.

.NET Demon Beta

It's a Visual Studio extension that cuts the time you spend waiting to find whether your code is right. It does this by:

Continuous testing (running your NUnit tests as you type)
Continuous compilation (so you can start running instantly because your code is already compiled)
Inline code coverage (so you know which tests are covering the code you're editing, and if they start failing)
Continuous save (so you don't have to press ctrl-S ever again)

Let me know what you think of it!

5 reasons why I almost loved WPF

Richard Mitchell — Fri, 13 Jan 2012 01:14:00 GMT

Before you read this you should probably read my original post 5 Reasons why I hate WPF. Also "Qwertie" wrote a nice article detail about his overview of why WPF sucks. 1 - Binding There is something so nice about setting a button to be enabled or not enabled based on a property in the view. All the enabling and disabling is handled for you (more or less). I find that using MVVM helps in many ways to separate UI logic from UI appearance. Something I've always found difficult when writing WinForms...(read more)

Inside the Concurrent Collections: ConcurrentStack

Simon Cooper — Thu, 12 Jan 2012 12:35:00 GMT

The first concurrent collection we'll look at is ConcurrentStack. This is conceptually the same as System.Collections.Generic.Stack, but is geared towards occasional concurrent modifications.

Now, in these posts I won't be looking to explain what every method does; just like my other explorations of various collection types, I'll be concentrating on the core implementation and concepts used by the collections, and glossing over the more sordid implementation details and scaffolding required to support an ICollection.

So, without further ado, let's get started:

ConcurrentStack

There are three basic operations you can perform on a stack:

Push: Push a new value onto the top of the stack
TryPeek: Peeking at the top value. In ConcurrentStack, this is quite a simple method.
TryPop: Popping the top value off the stack

ConcurrentStack supplements these with PushRange and TryPopRange for pushing and popping an array of values, ToArray, and the standard ICollection methods.

The stack is implemented as a singly-linked list, with nodes represented by the ConcurrentStack<T>.Node private class:

private class Node {
    internal T m_Value;
    internal Node m_Next;
}

The top of the stack is referenced by the volatile m_head variable. An empty stack is represented by a null value in m_head. For example, if you push the integers 1, 2 and 3 onto the stack in that order, the nodes will look like this:

If you then pop a single value off the stack, m_head will point at the '2' node.

Pushing values

So, there are three operations required to push a new value onto the stack:

Create a new Node object to hold the new value
Setting the node's m_next variable to point to the current head node
Changing m_head to point to the newly-created node.

And, straight away, we've got the possibility of a race condition. Say Thread 1 is pushing the value '4' onto the stack. It's just executed step 2:

Now, Thread 2 is scheduled on the CPU and is pushing the value '5'. Thread 2 executes steps 1, 2 and 3:

Thread 1 then executes step 3:

The node successfully pushed by Thread 2 has disappeared from the stack.

So, what we want to do is perform step 3 only if the head hasn't changed in the meantime. If it has been changed, then go back to step 2 to use the updated head node as the value of m_next. Furthermore, this all has to be done atomically so other threads can't see the intermediate state.

This is where Interlocked.CompareExchange comes into play. If you recall my previous post, CompareExchange performs the following as an atomic operation:

public static T CompareExchange<T>(ref T location, T value, T comparand)
  where T : class {
    T origValue = location;
    if (location == comparand)
        location = value;
    return origValue;
}

Or, in words:

I think location has the same value as comparand. If it is, replace it with value. Return me what location actually is.

This allows us to do the 'check and assign a new head, else try again' operation atomically, like so:

public void Push(T item) {
    Node node = new Node(item);
    do {
        node.m_next = m_head;
    }
    while (Interlocked.CompareExchange(ref m_head, node, node.m_next) != node.m_next);
}

Or, as words:

Create a new node
Set the node's next field to the current head
atomically{I think the current head is the value of the node's next field. If it is, replace it with the new node}. If the head was't actually the same as the next field, go back to 2.

This has eliminated the race condition we had between steps 2 and 3. Only if the head hasn't been changed is the new node pushed onto the stack as the new head. This loop repeats until CompareExchange successfully sets the head to the new node.

This can easily be generalised to a range of values in PushRange - we can simply change step 1 to construct the list of nodes holding the values being pushed, then using the m_next field of the tail node, and the top node as the new head, like so:

So that's pushing values onto the stack. What about removing them?

Popping values

As you can expect, popping items from the stack can be done in a similar way to pushing them:

Node topNode;
do {
    topNode = m_head;
}
while (Interlocked.CompareExchange(ref m_head, m_head.m_next, topNode) != topNode);

However, it's not quite so simple as that. For one thing, the stack may be empty, so m_head might be null. For another, you need to be able to pop several items off the stack as a single atomic operation, which requires navigating several m_next references at once. We need to be slightly more clever about it.

Let's cover checking for an empty stack first. Checking for null is easy, right? if (m_head == null) return false; So we put this at the start of the pop method:

public bool TryPop(out T item) {
    if (m_head == null) {
        item = default(T);
        return false;
    }

    Node topNode;
    do {
        topNode = m_head;
    }
    while (Interlocked.CompareExchange(ref m_head, m_head.m_next, topNode) != topNode);
    
    item = topNode.m_value;
    return true;
}

However, again, we've got a race condition. The null check only happens once. If another thread were to empty the stack while the current thread is inside the while loop, this leaves us with a NullReferenceException lying in wait when we try and get m_head.m_next.

The simple way to fix this is to do the null check inside the loop, eliminating the race condition:

public bool TryPop(out T item) {
    Node topNode;
    do {
        topNode = m_head;
        
        if (topNode == null) {
            item = default(T);
            return false;
        }
    }
    while (Interlocked.CompareExchange(ref m_head, m_head.m_next, topNode) != topNode);
    
    item = topNode.m_value;
    return true;
}

Sorted. So, what about popping multiple values off the stack? Again, like the null check, this has to be performed inside the while loop using the 'snapshot' of the head we've taken in topNode:

public int TryPopRange(T[] items, int count) {
    int i;
    Node topNode;
    do {
        topNode = m_head;
        Node curNode = topNode;
        i=0;

        while (i<count && curNode != null) {
            items[i] = curNode.m_value;
            curNode = curNode.m_next;
            i++;
        }
    }
    while (Interlocked.CompareExchange(ref m_head, curNode, topNode) != topNode);
    
    return i;
}

In fact, this idea of taking the current head, doing some work on it, then atomically swapping it if it hasn't been changed in the meantime can be generalised:

Take an atomic snapshot of the current state
Do some work on the snapshot locally to the current thread
Atomically make the work visible to all other threads, if the snapshot you took is still a valid representation of the state
If it isn't, go back to the start and try again

This is, at its core, how ConcurrentStack works.

Performing atomic operations

So, what underlying aspects of the system let us perform these operations atomically?

Object references are atomic
This lets us take an atomic snapshot of the current state simply by storing the value of m_head in a local variable; m_head will always be pointing at a valid node (or null), it won't be 'half-changed'.
Interlocked.CompareExchange is atomic
This ensures the state-check-and-switch won't be afflicted by race conditions with other threads.
m_head is a volatile variable
m_head is marked as volatile. This means that any changes to that variable by one thread are instantly visible to all other threads on the system; there's no caching to get in the way. Any threads that are attempting to modify m_head will be forced to try again, and they won't overwrite the changes already made.

Gory implementation details

Now, I said at the start that I wouldn't be covering gory implementation details of these collections. However, in this case, there are some important considerations.

If you decompile ConcurrentStack, you'll notice there's a Push method and a PushCore method (similarly for TryPop and TryPopRange). This is a small but significant performance detail; ConcurrentStack assumes there aren't going to be any thread collisions. The public methods all optimistically attempt to perform the operation once. Only if another thread gets in the way do the corresponding Core methods get executed. It is these that contain the full loop logic, along with calls to SpinWait.SpinOnce to try and get the other threads out of the way before they try again.

In fact, TryPopCore goes one step further, and implements a randomised exponential backoff to reduce the chances of several threads all waiting the same amount of time. It does mean that, although ConcurrentStack is lockless, and so eliminates the risk of deadlock, the performance will suffer if there are many threads all modifying the stack at the same time, as each method will have to try several times before its operation succeeds.

That's the essence of how ConcurrentStack is implemented. Next time, I'll take a look at the second lockless concurrent collection, ConcurrentQueue, which goes about achieving thread-safety in quite a different way.

Footnote

I'm now on twitter! If you want to get in touch, comment on the series, or suggest other things I could look at, my username is @simonmcooper

Indexing - take the hint and leave it to the experts

fatherjack — Tue, 10 Jan 2012 07:00:00 GMT

The most common T-SQL command in use has to be the SELECT statement, it is the bedrock of any SQL Professional's day. Sometimes it's used to snatch some data from a table or two while some quick investigation is done, other times it is at the heart of a stored procedure or view that will inform business decisions for coming months or even years. The latter purpose means you should spend some time making sure it is as efficient as possible. Not endless hours to save a millisecond or two (I mentioned...(read more)

Retrieving Passwords from Managed Accounts in SharePoint 2010 for C#

Damon — Fri, 06 Jan 2012 15:17:00 GMT

I was looking for a way to retrieve a password from a managed account when I ran into a post titled How to: Get Your Managed Account Passwords When They are Changed Automatically by SharePoint 2010 by Jason Himmelstein. It was written for PowerShell and I needed in C#, so I figured I would post the converted code in case anyone was looking for the same thing. You will need to have the following using statements:

using System.Runtime.InteropServices;    
using Microsoft.SharePoint.Administration;

Then you can use the following code to retrieve the managed password:

var managedAccounts = new SPFarmManagedAccountCollection(SPFarm.Local);foreach (SPManagedAccount managedAccount in managedAccounts) { var securePassword = (SPEncryptedString)managedAccount .GetType() .GetField("m_Password", System.Reflection.BindingFlags.GetField | System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic) .GetValue(managedAccount); var intptr = System.IntPtr.Zero; var unmanagedString = Marshal. SecureStringToGlobalAllocUnicode(securePassword.SecureStringValue); var unsecureString = Marshal.PtrToStringUni(unmanagedString); Marshal.ZeroFreeGlobalAllocUnicode(unmanagedString); //Do something with unsecureString }

One caveat to this is that you must be running as a Farm Administrator for the code to succeed. Otherwise you will get an error about accessing the registry. It is also relying on reflection to retrieve a non-public internal field, so as my friend Jeff Burt was quick to point out, Microsoft could change it at any time and break this code. Probably not good for production code.

SQL Server's Big Red Buttons

Tony Davis — Fri, 06 Jan 2012 12:24:00 GMT

One of the most reassuring aspects of watching a vintage James Bond film is the comfort of knowing that, just when there seems no further hope that the villain's plans for world domination will be thwarted, Bond will glance up at the wall and notice a big red button. Instantly, he knows that all he has to do is press it and the villain's lair will self-destruct messily, with plenty of pyrotechnics, and armed men being tossed into the air like rag dolls.

Of course, you have to wonder why the technologists who built the lair put that big red button on the wall. It seems to be an irresistible urge, and one to which the creators of SQL Server are not immune, as @SQLPoolBoy noted last week in one of his tweets...

"Looking at a database that has 99% fragmentation across the board. The cause, AutoShrink"

...a database brought to its knees by some poor soul who had accidentally hit one of SQL Server's big red buttons.

You have to feel sorry for anyone who accidentally accepts the default database sizing and auto-growth settings, or turns on AutoShrink, or accidentally creates a collation conflict, or falls foul of any other of a host of 'Red Button' actions that can eventually lead to metaphorical pyrotechnics and DBAs being tossed in the air like dolls.

SQL Server makes it very easy to tweak its various database- and server-level settings and so it's easy for inconsistency to creep in between database and servers, and it's easy for someone to unwittingly hit one of the red buttons. However, for the DBA managing tens of servers, it's not necessarily easy to find out which buttons have been pressed where, or to find best practice advice on how some of these settings really should be configured for each environment.

However, help in various forms is slowly emerging. Brent Ozar has made publicly available his SQL Server Blitz script, which helps you verify some of the absolute fundamentals (Are backups being taken? Are DBCC checks being run?), and then seeks out a few of the more common red buttons, which may need deactivating.

Then there is also SQLCop, a free community tool for "detecting common problems with database configurations and TSQL code". Named after a similar .NET tool (FxCop), it's broader in scope than the Blitz script, checking everything from configuration settings, to fragmented indexes, to missing Foreign Keys, to "code smells" in stored procedures. Red Gate has done some work with the makers of this tool, in incorporating some of their tests into the SQLTest tool, the idea being that the final step to resolving such problems may be automated testing.

However, there is still much work to be done. What are your favorite "Red Button" actions in SQL Server? What is the best way to find and deactivate them before they cause havoc in your Server and databases?

Cheers,

Tony.

Inside the Concurrent Collections

Simon Cooper — Thu, 05 Jan 2012 14:41:00 GMT

The concurrent collections, located in the System.Collections.Concurrent namespace, were introduced in .NET 4 as thread-safe collections that could be used without locking, and there are plenty of posts and articles out on the interwebs giving an overview of the collections and how to use them.

Instead, I'll be focusing these posts on how the concurrent collections are implemented; how they achieve thread-safety and the principles behind their implementation. Not only because I find that sort of thing quite interesting, but also to give you ideas about how you might use these principles in your own code.

Note however, that writing bulletproof thread-safe collections is hard. Really hard. Think carefully about somehow using one of the existing collections before trying to write or adapt your own.

What is a 'thread-safe' collection?

First of all, we need to understand what we mean by 'thread-safe'. Well, let's start with the repository of all human knowledge - wikipedia:

A piece of code is thread-safe if it only manipulates shared data structures in a manner that guarantees safe execution by multiple threads at the same time

OK, well, as an example, if m_Collection is some sort of 'thread-safe' ICollection, what will the result of these two threads running concurrently do?

Thread 1	Thread 2
m_Collection.Add(m_Item);	bool removed = m_Collection.Remove(m_Item);

The answer depends on exactly which order Thread 1 and Thread 2 run with respect to each other. So, whether m_Item is in m_Collection after these two threads have run depends entirely on the whim of the operating system. Thread 2 could try and remove an item that's not in the collection, setting removed to false, then Thread 1 adds the item to the collection. Or Thread 1 could run first, adding the item, which is then immediately removed by Thread 2, setting removed to true. Thread 1 could then merrily carry on executing, assuming m_Item is in m_Collection, not knowing it's been immediately removed by Thread 2.

That, however, is an implementation detail of whoever wrote the code for Thread 1 and Thread 2. The important thing is that, after these two threads have run the above code in some order, m_Collection will either have the item in it, or not; it won't be in some corrupted half-state where some internal datastructures think it has the item and some don't, so that (for example) m_Collection.Contains(m_Item) returns false but the enumerator still returns the item. So, I propose that this is what is meant by a thread-safe collection:

A thread-safe collection is one that can be modified by multiple threads at the same time without corrupting itself.

Achieving concurrency

There is a very simple way of writing a thread-safe collection - implement ICollection<T> by wrapping a List<T> that locks the backing list object on every method call (if you're unclear about what locks are, I'll be covering them below). This means that only one thread can access and modify the backing list at once; thread-safety is therefore maintained. However, this isn't a very good solution; many threads could be blocked by one costly list resize operation. This solution doesn't have very good concurrency.

Ideally, we want collections that don't keep threads waiting. As we'll see, two of the collections (ConcurrentStack and ConcurrentQueue) achieve thread-safety using no locks at all, and the other collections minimise the chances of two threads blocking on the same lock.

Locking and synchronization primitives

You can't just create a lockless thread-safe datastructure out of nowhere, you need some underlying support from the hardware, operating system and runtime to achieve this. Before we look at the collections themselves, we need to understand these primitives and what guarantees they provide.

Atomic types
Some types in .NET are specified as being 'atomic'. That is, if you change the value of a variable of an atomic type, it is guaranteed to appear to change value instantaneously; another thread won't be able to see the variable 'half-changed'. Object references and primitive types of 4 bytes or shorter are always atomic (ints, chars, booleans, floats etc), but 8-byte primitives are only atomic when running on a 64-bit process. The atomicity of object references is especially important to lockless collections, as we'll see in later posts.
Volatile variables
I discussed volatility in a previous post. To recap, marking a variable as volatile means that:
- The JIT compiler won't cache the variable in a cpu register; it will always read and write to it using the original memory location. This is important when multiple threads are changing a variable at the same time, so that any changes made to the variable in one thread are immediately picked up by other threads.
- A read or write to a volatile location introduces a memory barrier at that point, so that other reads and writes won't be reordered with respect to each other. This is important later on, as we'll see.
Locks
Mutual-exclusion locks are one of the more basic synchronization primitives. Each object has a lock associated with it, and by locking on an object you only allow one thread to have 'ownership' of that lock at a time. This allows only one thread to execute the section of code protected by that lock at a time.

When the currently executing thread 'releases' the lock, another thread (and only one other thread) is then allowed to take control of the lock and execute the protected code. Any other threads waiting to take the lock are blocked and cannot continue executing until it is their turn to take the lock.

C# has special syntax for locking on an object:
```
private readonly object m_LockObj = new object();

public void SynchronizedMethod() {
    lock (m_LockObj) {
        // protected code
     }
}
```
C# actually compiles this method to something like this:
```
public void SynchronizedMethod() {
    bool lockTaken = false;
    object lockObj = m_LockObj;
    
    try {
        System.Threading.Monitor.Enter(lockObj, ref lockTaken);
        // protected code
    }
    finally {
        if (lockTaken)
            System.Threading.Monitor.Exit(lockObj);
    }
}
```
This uses the System.Threading.Monitor class to implement the lock. The call to Monitor.Enter blocks the thread until it can take control of the lock, and the lock is released by Monitor.Exit.
System.Threading.Interlocked
Interlocked provides various methods for performing operations that wouldn't normally be atomic in an atomic fashion. For example, Interlocked.Read allows an 8-byte long to be read as an atomic operation (remember, 8-byte primitives are only atomic on 64-bit processes), Interlocked.Add allows you to perform a = a + b (aka a+=b) atomically, Interlocked.Decrement performs a = a - 1 (aka --a) atomically, etc.

The most important of these is Interlocked.CompareExchange. This family of methods performs the following operation atomically (using the generic overload as an example):
```
public static T CompareExchange<T>(ref T location, T value, T comparand)
 where T : class {
 T originalValue = location;
 if (location == comparand)
 location = value;
 return originalValue;
}
```
This might not seem like a particularly useful atomic operation, but it is crucial to the lockless collections, as we'll see.
System.Threading.SpinWait
Introduced in .NET 4, this structure encapsulates the idea of a 'spinwait':
```
while (!variableThatShouldBeTrueToContinue) {}
```
This keeps the thread continually spinning round the while loop, repeatedly checking whether another thread has set variableThatShouldBeTrueToContinue to true. However, performing such a spinwait gives no guarantees that the thread that is meant to set variableThatShouldBeTrueToContinue will be given the chance to execute; the operating system could, if it wishes, simply keep on executing the spinwait and not switch to other threads that actually change this variable.

System.Threading.SpinWait gets round this problem by, as well as simply spinning, occasionally calling Thread.Sleep and Thread.Yield. This will respectively encourage and force the operating system to execute other threads, giving a chance for the spinning condition to be satisfied.

Using the concurrent collections

At this point it's also worth pointing out that using a thread-safe collection will not make the rest of your code thread-safe and free of race conditions; it is not a panacea. As in the example at the top of this post, using a thread-safe collection does not stop a race condition between adding and removing the same item from the collection. Using a thread-safe collection simply means you have one less thing to worry about when writing threaded code. You still have the rest of your code to worry about!

That's it for introductions; in the next post, we'll look at the simplest of the concurrent collections, ConcurrentStack.

Using XML to pass lists as parameters in SQL Server

Phil Factor — Thu, 05 Jan 2012 02:32:00 GMT

(Updated 14th Jan 2012, and again 26th Jan 2012)

Every so often, the question comes up on forums of how to pass a list as a parameter to a SQL procedure or function. There was a time that I used to love this question because one could spread so much happiness and gratitude by showing how to parse a comma-delimited list into a table. Jeff Moden has probably won the laurels for the ultimate list-parsing TSQL function, the amazing ‘DelimitedSplit8K’. Erland Sommarskog has permanently nailed the topic with a very complete coverage of the various methods for using lists in SQL Server on his website.. With Table-Valued parameters, of course, the necessity for having any lists in SQL Server is enormously reduced, though it still crops up.

Element-based XML seems, on the surface, to provide a built-in way of handling lists as parameters. No need for all those ancillary functions for splitting lists into tables, one might think. Yes, indeed, but be careful of the XQuery syntax that you use, as we'll see.

Let’s just take the very simplest example of taking a list of integers and turning it into a table of integers. One can use a simple element-based XML list based on a fragment like this…

DECLARE @XMLlist XML
SELECT @XMLList = '<list>246810151721</list>'

…to give a table like this ..

IDs
-----------
2
4
6
8
10
15
17
21

(8 row(s) affected)

..by using this TSQL expression

SELECT x.y.value('.','int') AS IDs
FROM @XMLList.nodes('/list/i') AS x ( y )

It isn’t as intuitive as a simple comma-delimited list to be sure, but it is bearable. In small doses, this shredding of element-based XML lists works fine, but,<added 26th January> using this particular syntax for getting the value of the element </added 26th January> one soon notices the sigh from the server as the length of the list starts to increases into four figures. Try it on a list of a hundred thousand integers and you’ll have time to eat a sandwich.

OK. Let’s do a few measurements and do a graph.

Jeff Moden’s Split function, scales beautifully as shown by the red line, and it is difficult to measure it, it is so quick. The XML eShred technique by contrast, using the element-based list, and this XQuery syntax, exhibits horrible scaling. This sort of curve is enough to strike terror into the developer. Just as a comparison, I did the process of taking a list and turning it into a table by creating a VALUES expression from the list. Even this took longer than Jeff’s function, presumably because of the overhead of compiling the expression. Of course, the comparison is rather unfair because this third approach , the ‘Using Values Script’ approach, does no validation, but still one wonders what on earth the XML expression is DOING all that time. It must be gazing out the window, reading the paper and scratching itself, one assumes. No. Actually, the CPU is warming up on the task, so it involves frantic activity.

Operations involving XML can be startlingly fast in SQL Server, but this particular critter seems startlingly slow once one gets to a three-figure number in the list that you’re turning into a relation that can then be used in SQL Expressions. Imagine this getting loose in a production system when someone starts passing more values in the list and the database suddenly slows down. How would you track the problem down?

<added 26th January>The big problem here is one of the expression. As pointed out by a reader of the blog, if you modify the expression slightly to

SELECT x.y.value('.','int') AS IDs
FROM @XMLList.nodes('/list/i/text()') AS x ( y )

The shredding suddenly goes like a rocket</added 26th January>.

There is a second, and more wordy version of the simple XML list, The attribute-based list. Here

DECLARE @XMLlist XML
SELECT @XMLList = '<list><y i="2" /><y i="4" /><y i="6" /><y i="8" /><y i="10" /><y i="15" /><y i="17" /><y i="21" /></list>'

…to give exactly the same table ..

IDs
-----------
2
4
6
8
10
15
17
21

(8 row(s) affected)

by using a very similar syntax like this...

SELECT x.y.value('@i','int')
FROM @XMLList.nodes('list/y') AS x( y )

'Eh? What difference could this make?', you're wondering. Well, rather a lot, it turns out. Thanks to a reader comment on the first version of this blog by wBob, we can reveal that this version scales as well as Jeff's amazing 'split' function. I must admit to being rather surprised at the difference in performance of the two. By using the Attribute-based list, or the modified XQuery expression, you can cheerfully pass lists of substantial length to procedures without worrying, it seems. It reminded me of an argument I had some years ago with a distinguished Database Developer who was advising us all not to use XML-based lists because of their performance problems. I'd been using attribute-based lists simply because I copied Bob Beauchamin's example code in 'The Book', and hadn't hit any problem. We couldn't convince each other. Perhaps we'd hit this performance difference.

<added 26th January>We can even improve on this. as Sviridov Konstantin has pointed out, you can tweak it to the point that it goes around twice the speed of Jeff's TSQL split function by using syntax like this...

SELECT x.y.value('.','int')
FROM @XMLList.nodes('list/y/@i') AS x( y )

Which will give a performance comparison with the the TSQL split function. These modified XML shreds must be the fastest non-CLR method of transferring list-based data as a variable in SQL.

</added 26th January>XML in SQL Server seems to remind me of the old poem by Henry Wadsworth Longfellow. (1807-1882)

There was a little girl, who had a little curl
Right in the middle of her forehead,
And when she was good, she was very, very good,
But when she was bad she was horrid.'.

Just as a comparison, here is the horrid XML performing against the first attribute-based XML Shred, and the TSQL split function

As always, one should measure everything you can, and take nothing for granted if you have to deliver a scalable application.

Here is the simple code I used to do the metrics. The results were simply pasted into excel and graphed.

--in order to record the timings, we prepare a log.
DECLARE @log TABLE (
 Log_Id INT IDENTITY(1, 1),TheeVent VARCHAR( 2000 ),
 [Values] INT, CPU FLOAT DEFAULT @@CPU_BUSY,
 DateAndTime DATETIME DEFAULT GETDATE()) ;
--define working variables
DECLARE @List VARCHAR(MAX),@XMLList XML,@AttributeBasedXMLList XML,
 @buildScript VARCHAR(MAX), @XMLbuildScript NVARCHAR(MAX),
 @ii INT, @Values INT;
--and table variables to receive the results/relations
DECLARE @ByteBucket TABLE (TheNumeral INT)
DECLARE @ByteBucket2 TABLE (TheNumeral INT)
DECLARE @ByteBucket3 TABLE (TheNumeral INT)
DECLARE @ByteBucket4 TABLE (TheNumeral INT)
DECLARE @ByteBucket5 TABLE (TheNumeral INT)
DECLARE @ByteBucket6 TABLE (TheNumeral INT)
SET NOCOUNT ON
SELECT @Values=1 --start with one item in the list
WHILE @Values<=3000
 BEGIN
 --build up the list with random integers
 SELECT @List='1',@ii=@Values
 WHILE @ii>1
 BEGIN
 SELECT @List=@List+','+CONVERT(VARCHAR(3),CONVERT(INT,RAND()*100))
 SELECT @ii=@ii-1 -- .. to the required length
 END
 --and pre-prepare the XML List and VALUES script
 SELECT @XMLList='<list>'+REPLACE(@List,',','')+'</list>'
 SELECT @BuildScript='SELECT x FROM (values ('+REPLACE(@List,',','),(')+'))d(x)'
 --and pre-prepare the XML List and VALUES script
SELECT @XMLBuildScript='SELECT @AttributeBasedXMLList = ( SELECT x AS "@x" FROM (values ('
 +REPLACE(@List,',','),(')+'))d(x) FOR XML PATH(''y''), ROOT(''root''), TYPE )'
EXEC sp_executesql @XMLBuildScript, N'@AttributeBasedXMLList XML OUT',
 @AttributeBasedXMLList OUT
 --try doing the delimited list function
 INSERT INTO @log (TheEvent, [Values]) SELECT 'Using Split function',@Values
 INSERT INTO @ByteBucket (TheNumeral)
 SELECT item FROM dbo.DelimitedSplit8K (@list,',')
 --use the XML Shred trick
 INSERT INTO @log (TheEvent, [Values]) SELECT 'Element-based XML',@Values
 INSERT INTO @ByteBucket2 (TheNumeral)
 SELECT x.y.value('.','int') AS IDs
 FROM @XMLList.nodes('/list/i') AS x ( y )
 --use the XML Shred trick
 INSERT INTO @log (TheEvent, [Values]) SELECT 'Element-based XML (text())',@Values
 INSERT INTO @ByteBucket6 (TheNumeral)
 SELECT x.y.value('.','int') AS IDs
 FROM @XMLList.nodes('/list/i/text()') AS x ( y )
 --try the VALUES method
 INSERT INTO @log (TheEvent, [Values]) SELECT 'Using Values script',@Values
 INSERT INTO @ByteBucket3 (TheNumeral)
 EXECUTE (@BuildScript)
 --use the XML Shred trick
 INSERT INTO @log (TheEvent, [Values]) SELECT 'Attribute-based XML',@Values
 INSERT INTO @ByteBucket4 (TheNumeral)
 SELECT x.y.value('@x','int')
 FROM @AttributeBasedXMLList.nodes('root/y') AS x(y)
 INSERT INTO @log (TheEvent, [Values]) SELECT 'finished',@Values
 --use the XML Shred trick
 INSERT INTO @log (TheEvent, [Values]) SELECT 'Attribute-based XML (@)',@Values
 INSERT INTO @ByteBucket5 (TheNumeral)
 SELECT x.y.value('.','int')
 FROM @AttributeBasedXMLList.nodes('root/y/@x') AS x(y)
 INSERT INTO @log (TheEvent, [Values]) SELECT 'finished',@Values
 SELECT @Values=@Values+100
END

--Yes, we need to check that they all agree!
SELECT COUNT(*), SUM(TheNumeral) FROM @ByteBucket
UNION ALL
SELECT COUNT(*), SUM(TheNumeral) FROM @ByteBucket2
UNION ALL
SELECT COUNT(*), SUM(TheNumeral) FROM @ByteBucket3
UNION ALL
SELECT COUNT(*), SUM(TheNumeral) FROM @ByteBucket4
UNION ALL
SELECT COUNT(*), SUM(TheNumeral) FROM @ByteBucket5
UNION ALL
SELECT COUNT(*), SUM(TheNumeral) FROM @ByteBucket6

SELECT
 TheStart.[Values],
 MAX(CASE WHEN TheStart.TheeVent ='Using Split function'
 THEN DATEDIFF( ms, TheStart.DateAndTime, Theend.DateAndTime )
 ELSE 0
 END) AS [Using Split function],
 MAX(CASE WHEN TheStart.TheeVent ='Element-based XML'
 THEN DATEDIFF( ms, TheStart.DateAnddTime, Theend.DateAndTime )
 ELSE 0
 END) AS [Element-based XML],
 MAX(CASE WHEN TheStart.TheeVent ='Using Values script'
 THEN DATEDIFF( ms, TheStart.DateAndTime, Theend.DateAndTime )
 ELSE 0
 END) AS [Using Values script],
 MAX(CASE WHEN TheStart.TheeVent ='Attribute-based XML'
 THEN DATEDIFF( ms, TheStart.DateAndTime, Theend.DateAndTime )
 ELSE 0
 END) AS [XML Attributes],*/
 MAX(CASE WHEN TheStart.TheeVent ='Element-based XML (text())'
 THEN DATEDIFF( ms, TheStart.DateAndTime, Theend.DateAndTime )
 ELSE 0
 END) AS [Element-based XML (text())],
 MAX(CASE WHEN TheStart.TheeVent ='Attribute-based XML (@)'
 THEN DATEDIFF( ms, TheStart.DateAndTime, Theend.DateAndTime )
 ELSE 0
 END) AS [Attribute-based XML (@)]
FROM @log TheStart
 INNER JOIN @log Theend
 ON Theend.Log_Id = TheStart.Log_Id + 1
WHERE TheStart.TheEvent<>'finished'
GROUP BY TheStart.[Values];

SELECT
 TheStart.[Values],
 MAX(CASE WHEN TheStart.TheeVent ='Using Split function'
 THEN theEnd.cpu-TheStart.cpu
 ELSE 0
 END) AS [Using Split function],
 MAX(CASE WHEN TheStart.TheeVent ='Element-based XML'
 THEN theEnd.cpu-TheStart.cpu
 ELSE 0
 END) AS [Element-based XML],
 MAX(CASE WHEN TheStart.TheeVent ='Using Values script'
 THEN theEnd.cpu-TheStart.cpu
 ELSE 0
 END) AS [Using Values script],
 MAX(CASE WHEN TheStart.TheeVent ='Attribute-based XML'
 THEN theEnd.cpu-TheStart.cpu
 ELSE 0
 END) AS [XML Attributes],
 MAX(CASE WHEN TheStart.TheeVent ='Element-based XML (text())'
 THEN theEnd.cpu-TheStart.cpu
 ELSE 0
 END) AS [Element-based XML (text())],
 MAX(CASE WHEN TheStart.TheeVent ='Attribute-based XML (@)'
 THEN theEnd.cpu-TheStart.cpu
 ELSE 0
 END) AS [Attribute-based XML (@)]
FROM @log TheStart
 INNER JOIN @log Theend
 ON Theend.Log_Id = TheStart.Log_Id + 1
WHERE TheStart.TheEvent<> 'finished'
GROUP BY TheStart.[Values];

New Year resolution: A better diet and some home cooking

fatherjack — Mon, 02 Jan 2012 03:00:00 GMT

Many people reflect on their diet at this time of year and decide to make changes and I would suggest that all DBAs should do the same. This is not because I think any DBA is over-weight (although I'd like to be a bit lighter than I am) but because they should be considering what their SQL Servers are having to consume and how that is affecting their health and performance. Just like a human that eats too many take-away burgers a server that is working with poor TSQL will become slow to respond...(read more)

Anatomy of a .NET Assembly - Type forwards

Simon Cooper — Fri, 23 Dec 2011 14:02:00 GMT

If you've ever had a poke around System.dll or System.Core.dll in Reflector, you may have noticed TypeForwardedToAttributes applied to the assembly:

[assembly: TypeForwardedTo(typeof(Lazy<>))]
[assembly: TypeForwardedTo(typeof(LazyThreadSafetyMode))]
[assembly: TypeForwardedTo(typeof(Action))]
[assembly: TypeForwardedTo(typeof(Action<,>))]
[assembly: TypeForwardedTo(typeof(Action<,,>))]
[assembly: TypeForwardedTo(typeof(Action<,,,>))]

This post has a look at what these are, and how they're implemented.

Type forwards

TypeForwardedToAttribute is part of a feature introduced in .NET 2 - Type forwarding. As the documentation says, this is a feature that allows a type to be moved to a different assembly without having to recompile assemblies using that type. This is used extensively by the class libraries when types were moved from System.Core.dll to mscorlib.dll between .NET 3.5 and 4, allowing assemblies compiled against .NET 2 and 3.5 to run on the .NET 4 framework as-is, without having to be recompiled.

However, if you think about it, this is much more than a simple attribute; this is a core change to the CLR type resolution mechanism. Every type that is resolved in an assembly first has to check for the existance of a type forward indicating the type has been moved somewhere else.

This isn't something that can easily be represented in a simple attribute; TypeForwardedToAttribute is actually an example of a pseudo custom attribute.

ExportedType

First, a bit of background. The ExportedType metadata table was originally designed to be used in multi-module assemblies as part of the 'public contract' of an assembly; the manifest module for the assembly would contain an entry in ExportedType for every public type defined in other modules, comprising

TypeName & TypeNamespace: the exported type's full name
Implementation: the module (or nested ExportedType) the type can be found.
TypeDefId: the index within the TypeDef table in the module the type is located.

This allows any tool referencing the multi-module assembly to only need to look in the manifest module to find every public type defined in the assembly and where it can be found; it doesn't have to scan every module comprising the assembly.

When .NET 2 came along, ExportedType was repurposed to store type forwards as well. If Implementation is a reference to an assembly rather than a module, then that assembly is the new location of the type named by TypeName and TypeNamespace (type forwards don't allow you to change the type name or namespace).

So, when the C# compiler sees a type forward in System.Core.dll specified by the attribute

[assembly: TypeForwardedTo(typeof(Action))]

the Action type is resolved using the normal C# type resolution rules to [mscorlib]System.Action, and the compiler generates an entry in ExportedType like so:

TypeName: Action
TypeNamespace: System
Implementation: assembly reference to mscorlib
TypeDefId: 0 (type forwards don't use this field)

this entry is then followed by the core CLR type resolution mechanism so that any references to [System.Core]System.Action are transparently redirected to [mscorlib]System.Action at runtime; assemblies using the forwarded type don't have to be recompiled.

TypeForwardedFrom

Finally, TypeForwardedFromAttribute is the counterpart to TypeForwardedToAttribute; it specifies the assembly a type has been forwarded from (using an assembly name string, rather than a direct metadata assembly reference). However, unlike TypeForwardedTo, this is a normal attribute, has no effect on CLR type resolution, and exists primarily for bookkeeping purposes.

Type forwards may seem like a niche feature aimed at library writers, but they are invaluable in the right circumstances. In the BCL, they allow programs compiled against the .NET 2 and 3.5 frameworks to run on the .NET 4 framework without needing to be recompiled.

Continuous profiling for websites

jamesg — Tue, 20 Dec 2011 14:08:00 GMT

The .NET division at Red Gate have been working on a new way of profiling ASP.NET applications.

Traditional profiling tools live on the developer's workstation and run in short bursts while the developer wrestles with a particular performance problem. If you’re a web developer, you’ll start and stop WebDev or IIS Express, or maybe a local installation of IIS, as part of an edit->compile->deploy->profile cycle. These profilers give you a source code view of where time is being spent, generally with method-level timings. But collecting the data in bursts this way can be cumbersome, and continuous monitoring tools, of the kind sysadmins currently use, seldom give you much detail about which part of the code is performing badly.

Another problem when profiling ASP.NET is that when something runs slowly the developer has to recreate the issue locally; attaching to a process on a running production server may be impossible, and restarting it to hook in a profiler is seldom permitted (or wise). Even getting access to a test machine can sometimes be problematic. Worse, these issues are rarely reproducible in a simple way – the website slows to a crawl, the administrator restarts IIS, everything is fine…and many of the clues required to fix the issue have been destroyed.

We wanted to make a tool to solve these problems: an "always-on" method-level profiler that runs as part of IIS and automatically records call-stack data for any application pools, giving you quick and easy access to the results. With continuous profiling, you can collect information at (and before) the point of failure, an approach much likelier to lead to a successful fix. Continuous profiling on your development machine gets less cumbersome, as edit->compile->deploy->profile becomes simply edit->compile->deploy. And you get real performance data throughout.

How does it work and what will it do to my system?

At first I was slightly daunted by the prospect of testing something that would live inside IIS – it felt like being asked to test the software for a pacemaker – but in fact the continuous profiler is a lot less invasive than standard profilers. It’s pretty much a passive listener, there’s no clever rewriting of IL or function detours, or anything that actually modifies the website under test. That’s not to say it’s perfect yet – it’s running in the IIS process, it’s unmanaged code (as profilers have to be, to sit outside the CLR), and it’s very, very new. On the other hand, our internal web team have been running it for several weeks on their test servers and it’s easy to disable from the IIS control panel.

Continuous profiling works by installing an IIS module that loads before the CLR. .NET is then configured to load the profiler DLL (or ‘core’) into w3wp.exe before the profiled website begins to execute. Our DLL can then record results for our web UI to read and display.

Will it make everything slow?

ANTS Performance Profiler will automatically hide insignificant methods (defined in terms of the number of instructions and amount of branching they contain, and the contribution they make to the total time), and we’re continuing to tune performance as we develop the tool. We’re currently exploring ways to reduce this even more, like functionality that lets you exclude high-overhead methods from profiling, for example. Meanwhile, you can expect your website to be perfectly usable while it’s being profiled, although overhead can vary depending on the nature of the site. Unsurprisingly, something held together with JavaScript will take less of a hit than something more traditionally server-side.

What can I expect to see?

The interface is designed to help you find and explore performance bottlenecks quickly. At the top there’s a zoomable timeline with a graph of CPU usage and underneath there’s another timeline showing all the available data. When a region of time is selected, the main body of the webpage will display an expandable call-tree showing the time spent in each method, the cumulative time spent with all children of that method, and the hit count. If you’ve used Red Gate’s ANTS Performance Profiler before, the layout will be very familiar. Not all the planned features are complete yet – you can pick up our nightly builds to get new functionality as we release it!

Where do I get it?

The Early Access Program is now open. To participate, just download a build from http://help.red-gate.com/help/ANTSPerformanceProfiler/download_eap.html, and let us know what you think. We look forward to hearing your comments.

What Counts For A DBA: Humbug!

drsql — Tue, 20 Dec 2011 06:00:00 GMT

If you have seen the movie ‘The Christmas Carol’, you will remember that the evil bank owner Ebenezer Scrooge is not a proponent of the holiday season, claiming Christmas to be "a poor excuse for picking a man's pocket every twenty-fifth of December" and doesn't even want his employee Bob Cratchet to miss work on Christmas Day; work that involves foreclosing on his customers who no doubt spent their money on the holiday merriment instead of the mortgage. If you are unfamiliar with the story, I won't spoil how it happens, but in the end, not surprisingly, his heart softens to the ordeal, gives Bob the day off and a big raise. However, even Scrooge 2.0's head would have exploded at how much time his employee would be spending on the festivities nowadays.

During the holiday season (more or less all of December and a bit of the surrounding months), the productivity of the average worker falls off to dangerously anemic levels. Preparations for parties, travel, shopping, office decorating, gift exchanging, vacations, team volunteering outings for Toys for Tots/Rescue Mission etc. and other seasonal activities encroach heavily upon the employee's attention. Most companies are complicit with this, organizing several holiday parties (team, department, division, company, SQL User Group), and in some companies this means that employees come back to work the next day a bit tired, or worst case, don't come to work the next due to what is claimed to be a mysterious fruitcake related illness (we know the truth though). With productivity so low, many employees save up their vacation and take most of the month of December off in order to escape the frustration of getting work done in December, thereby multiplying the effect.

However, all of these lazy employees, like me, for instance, are not my focus today. Rather, I wish to send a love letter of sorts to the excellent DBAs and consultants who tirelessly support the work that developers like myself have created and forced them to support, regardless of whether Charlie Brown is on TV or not. Many production DBAs will have to work the holidays, or for the case of the observant DBA, carry around electronic leashes to be available in case their monitoring software determines there is cause for concern (day or night.) For many there will be issues. As each year passes, we get more and more electronic devices being used around the word that require the Internet to operate twenty four hours a day, since Christmas Day in one part of the word is Christmas Eve in another, and just another work day for still more parts of the world.

So during this holiday season, when you are leaned back in your favorite sitting place with your bowl of gingerbread cookies and a glass of eggnog (more nog than egg, naturally), with your new Internet connected phone, tablet, MP3 player, laptop computer, DVR, Internet radio, WiFi Router, TV Remote Control, TV, and Toaster humming along simultaneously, remember to take a few minutes to think about the production support staff, (especially our beloved DBAs!) and what they go through when they are the only person available to solve a problem for tens of thousands of customers that will either be the next repeat customer, or the next negative reviewers, all based on just how well they handle the issues that we developers left them. So whether or not the DBA likes it, on Christmas morning they may have to say "humbug!" to the figgy pudding (whatever the heck that is) or perhaps their child's first holiday to diagnose an issue that may have just needed an index that should have been caught in development or testing (by me, no doubt).

Happy Holidays to all, and to all a good database design!

As an added bonus, enjoy the following festive picture by Andrea Benedtti (anbenedetti on twitter), a SQL Server MVP from Italy by running the following SQL Server 2008+ code...

Christmas Tree Drawn Using SQL Server Spatial Types

/*
********************
Happy SQL Christmas!
********************
Andrea Benedetti, SQL Server MVP
Twitter: @anbenedetti
*/

SET NOCOUNT ON;
/* please choose the level of the tree... :-) */
DECLARE @level smallint = 10;

DECLARE @i tinyint = 1;
DECLARE @Offset smallint = 10;
DECLARE @x1 smallint = 100;
DECLARE @y1 smallint = 100;
DECLARE @x2 smallint = 150;
DECLARE @y2 smallint = 100;
DECLARE @x3 smallint = 125;
DECLARE @y3 smallint = 115;
DECLARE @x4 smallint = 100;
DECLARE @y4 smallint = 100;
DECLARE @Tree TABLE( Id tinyint IDENTITY(1 , 1) ,
Triangle geometry );
DECLARE @Palline TABLE( Id tinyint IDENTITY(1 , 1) ,
Ball geometry );
WHILE @i <= @level
BEGIN
INSERT INTO @Tree( Triangle )
VALUES( geometry::STGeomFromText( 'POLYGON ((' + CAST(@x1 AS varchar( 5 )) + ' ' + CAST(@y1 AS varchar( 5 )) + ',' + CAST(@x2 AS varchar( 5 )) + ' ' + CAST(@y2 AS varchar( 5 )) + ',' + CAST(@x3 AS varchar( 5 )) + ' ' + CAST(@y3 AS varchar( 5 )) + ',' + CAST(@x4 AS varchar( 5 )) + ' ' + CAST(@y4 AS varchar( 5 )) + '))' , 0 ));
INSERT INTO @Palline( Ball )
VALUES( geometry::STGeomFromText( 'POINT(' + CAST(@x1 AS varchar( 5 )) + ' ' + CAST(@y1 AS varchar( 5 )) + ')' , 0 ));
INSERT INTO @Palline( Ball )
VALUES( geometry::STGeomFromText( 'POINT(' + CAST(@x2 AS varchar( 5 )) + ' ' + CAST(@y2 AS varchar( 5 )) + ')' , 0 ));
INSERT INTO @Palline( Ball )
VALUES( geometry::STGeomFromText( 'POINT(' + CAST(@x3 AS varchar( 5 )) + ' ' + CAST(@y3 AS varchar( 5 )) + ')' , 0 ));

SET @x1-=@Offset;
SET @x2+=@Offset;
SET @x4-=@Offset;
SET @y1-=@Offset;
SET @y2-=@Offset;
SET @y3-=@Offset;
SET @y4-=@Offset;
SET @i+=1;
END;
SET @x1 = @x3 - @Offset;
SET @x2 = @x3 + @Offset;
SET @x3 = @x3 + @Offset;
SET @x4 = @x2;

INSERT INTO @Tree( Triangle )
VALUES( geometry::STGeomFromText( 'POLYGON ((' + CAST(@x1 AS varchar( 5 )) + ' ' + CAST(@y1 AS varchar( 5 )) + ',' + CAST(@x2 AS varchar( 5 )) + ' ' + CAST(@y2 AS varchar( 5 )) + ',' + CAST(@x2 AS varchar( 5 )) + ' ' + CAST(@y3 AS varchar( 5 )) + ',' + CAST(@x1 AS varchar( 5 )) + ' ' + CAST(@y3 AS varchar( 5 )) + ',' + CAST(@x1 AS varchar( 5 )) + ' ' + CAST(@y1 AS varchar( 5 )) + '))' , 0 ));

SELECT Triangle
FROM @Tree
UNION ALL
SELECT Ball.STBuffer( 3 )
FROM @Palline;

A suitable present, whatever one's past

Tony Davis — Thu, 15 Dec 2011 17:32:47 GMT

Even DBAs have devoted aunts. They are probably also oblivious to the mental anguish they cause to their relatives in the run-up to Christmas. What would be a suitable gift for someone so deeply in the grip of technophilia that they can tell you the difference between ten apparently identical brands of Smartphone, and have a couple of them stuffed in their pockets? Who then use them to discuss with colleagues the finer points of query-plan caching or buffer-chaining?

I've got it you cry.some amusing, techno-gadgets! Something they'd want to keep with them, at their desk at work, and maybe be reminded of the source of the gift. We've all seen them: the USB lava lamps, a Voltron USB drive, a USB-heated blanket, and toast hand warmers. No; this stuff might have some value to the type of geek that holes up in a drafty basement, secretly inventing new programming languages, or trying to break the latest record for overclocking a CPU. Many DBAs, however, would open the parcel with a frozen rictus of a smile, a gurgle of feigned delight.

A bit more imagination is required. So, here is an idea: a USB Home Brew kit. Plug it into your server or laptop and, with a bit of control software, you can keep the fermentation temperature at a steady 68F, and watch proudly as the brew progresses. Plus, the gentle bubbles of fermentation coupled with the faint odour of malt will be more satisfying, and relaxing, than any lava lamp.

Could I be wrong? I encourage you to submit your ideas for the most delightful, but yet-to-be-manufactured, Christmas gift for IT professionals. The winner will receive one each of every gift linked in this blog (or equivalent value voucher). Three runners up will receive a gift each.

Since this is my final Simple-Talk editorial of 2011, I would like to thank everyone for their support over the year, and to wish you all a happy festive season and 2012. I will probably be back in the New Year, depending on how well my new USB Home Brew business takes off.

Cheers,

Tony.

What Counts For a DBA: Practicality

drsql — Thu, 15 Dec 2011 09:56:00 GMT

As a data architect, and writer on the same subject, I am completely entrenched in learning and applying the discipline of normalization. When I set my course down the road of great database design, my motto is "Fifth Normal Form or bust", even if it takes months to finish the design for just a few tables (and, of course, to make sure every table and column is meticulously named). Not much gives me greater pleasure than the beauty of a database that has been honed to a high degree of normalized perfection and that, to the amazement of the many detractors and doubters, also performs magnificently.

If this sounds like one of Lewis Carroll's stories, there is good reason for it. The reality is that I don't live in Wonderland, and rarely get time to design anything, much less everything, in meticulous detail. My average project tends to fall into one of two categories

The Surprise Project – about 30 seconds after you fall asleep for the night, a mobile device starts dancing to that catchy ringtone that was annoying 10 years ago. Something has failed and it is time to fix it. As long as the problem wasn't caused by you, this can be the most rewarding times of your career, as you put on your Superman Underoos and save the day.
The Incredibly Urgent Project – after months of furtive meetings between various marketing and project managers, over the need for some innovative new campaign, advertising scheme, or product line, a decision has finally been made to go ahead. And they need the project to start…immediately! And the delivery date is…as soon as possible!

My normal workday, as a result, most closely resembles a typical episode of the TV show, Junkyard Wars…"Contestants, you have just 10 hours to build a fully-working dragster from the contents of this junkyard." My favorite team welded together an old motorcycle and a rusty golf cart and went flying down the track. Their solution would never work long enough to survive the complete drag racing season, but it survived 2 trips down the eighth mile. They knew for how long their solution would be used, but most software built like this, with a planned expiration date, usually last years longer than expected.

Many project are like this; get it done quick. The best theoretical solutions need not be posited; your deep and hard-won knowledge of advanced "internals" is of little good to you here. Practical is what is required. When a manager says "as soon as possible", they don't mean "as soon as you can reasonably arrive at the optimum database design and then build a proper solution with stringent application of sound programming principles". They mean "why haven't you got something to show me, already?"

The oft-heard battle cry from management is that "Better is the enemy of good enough"; true, but in the wrong hands, very dangerous. Too often, the definition of "good enough" is taken to mean something works to the point that the manager can check a box, no matter the implications to the future user or maintenance workers.

Does this mean that all of your training, all of your dedication to doing your job the right way, is useless? Not at all; in fact, it is going to be more necessary than ever. Just because your solutions need to be devised with practicality and timeliness as a priority, it doesn't mean that you can actually do things poorly or technical debt will pile up like dirty clothes in a dorm room. It does mean, however, that you need to stop aiming for perfection and start shooting for "good enough", but where good enough implies a practical solution that adopts at least the basic software engineering principles, and a solution that you are comfortable releasing to the users, even in the knowledge that an even better solution was just out of reach.

Almost every solution that is created according to a definition of practical that means "delivered tomorrow" is doomed to be a lesson for the next project; when practical means "as good as practically possible", everything will usually be alright.

Simple-Talk

Getting the URL to the Content Type Hub Programmatically in SharePoint 2010

Red Gate and the Community

From nowhere to somewhere in no time at all.

To Not CI to Eye

Red Gate does Burns Night 2012

ASP.NET performance: is it all about the database?

Think your ASP.NET applications performance problems all lie in the database? Prove it!

When It’s Not The Database

Further reading

Inside the ConcurrentCollections: ConcurrentQueue

What does System.Collections.Generic.Queue do?

Queue Segments

Achieving thread-safety

Enqueuing

Dequeuing

Combining the two

Final notes

Concurrent principles

Separating threads

Invariants

volatile used as a memory barrier

Can desktop software be Lean?

Educational Programming

It always works on my machine.

Inside Red Gate - Experimental Results

The experiments so far

Experiment 1

Experiment 2

It's not all bad news...

So what now?

A new toy - .NET Demon

5 reasons why I almost loved WPF

Inside the Concurrent Collections: ConcurrentStack

ConcurrentStack

Pushing values

Popping values

Performing atomic operations

Gory implementation details

Footnote

Indexing - take the hint and leave it to the experts

Retrieving Passwords from Managed Accounts in SharePoint 2010 for C#

SQL Server's Big Red Buttons

Inside the Concurrent Collections

What is a 'thread-safe' collection?

Achieving concurrency

Locking and synchronization primitives

Using the concurrent collections

Using XML to pass lists as parameters in SQL Server

New Year resolution: A better diet and some home cooking

Anatomy of a .NET Assembly - Type forwards

Type forwards

ExportedType

TypeForwardedFrom

Continuous profiling for websites

How does it work and what will it do to my system?

Will it make everything slow?

What can I expect to see?

Where do I get it?

What Counts For A DBA: Humbug!

A suitable present, whatever one's past

What Counts For a DBA: Practicality

What does `System.Collections.Generic.Queue` do?

`volatile` used as a memory barrier