Thread Synchronisation in SQL Server

Any code optimized for highly concurrent workloads must worry about thread synchronization. SQL Server is no exception, because in a database system, synchronization is one of the core functionalities you rely on the engine to provide (the noSQL crowd may ponder that a bit). In this blog post, I will describe the synchronization primitives, like locks, latches and spinlocks, used by SQL Server to coordinate access to memory regions between threads.

Continue reading…

Hash Post Logo

Exploring Hash Functions in SQL Server

Hash distributing rows is a wonderful trick that I often apply. It forms one of the foundations for most scale-out architectures. It is therefore natural to ask which hash functions are most efficient, so we may chose intelligently between them.

In this blog post, I will benchmark the build in function in SQL Server. I will focus on answering two questions:

  • How fast is the hash function?
  • How well does the hash function spread data over a 32-bit integer space

Continue reading…

Neat trick on United Airlines

Thanks to everyone for a great PASS US – I had a lot of fun at the conference.

Today, I am heading to Japan. My ticket is an economy ticket, and it is a long flight (around 12h). The price for a business class ticket (two weeks ago) was simply too high – 10K USD. Of course, I am not thrilled about such a journey in cramped conditions. But fortunately that is not what is going to happen: I will be lying down sleeping all of it. Here is how:

First, I asked for a potential upgrade at the check-in counter (hint: always be friendly to the girls there, and never sign up for specialized meals). I am only Silver Member on Star Alliance – but they bumped me up to Economy Plus – 5 more inches of legroom. So far so good…

Second, I asked at the gate itself for an upgrade to business. Because the plane is not full, they sell the final upgrade real cheap: 600 USD for upgrade from Economy Plus to Business.

Result: Trans-Pacific flight for the price of Economy + 600 USD. I will be enjoying my champagne and priority boarding in a few minutes.

SQLBits and Phones

imageMy presentation from SQLBits: “Finding the Limits: The Grade of The Steel” should be online soon. There is a lot of stuff to blog about and so little time to do it. It was some fun days of tuning as the picture shows.

I am curious to hear comments on my session. Was it useful? What other tests would you like to see? Do you prefer this presentation style over other styles (no, I won’t do demos!).

Special thanks to the good people over at Fusion-io for letting me use their kit to run tests. You guys rock!

In other news: I finally found a phone that is just a phone. It is called the Nokia X2, I had it for only a few days and I am already liking it a lot. So far, it has survived on only one charge.

Boosting INSERT Speed by Generating Scalable Keys

Throughout history, similar ideas tend to surface at about the same time. Last week, at SQLBits 9, I did some “on stage” tuning of the Paul Randal INSERT challenge.

It turns out that at almost the same time, a lab run was being done that demonstrated, on a real world workload, a technique similar to the one I ended up using. You can find it at this excellent blog: Rick’s SQL Server Blog.

Now, to remind you of the Paul Randal challenge, it consists of doing as many INSERT statements as possible into a table of this format (the test does 160M inserts total)

CREATE TABLE MyBigTable (
c1 UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT
NEWID ()
,c2 DATETIME DEFAULT GETDATE ()
,c3 CHAR (111) DEFAULT ‘a’
,c4 INT DEFAULT 1
,c5 INT DEFAULT 2
,c6 BIGINT DEFAULT 42);

Last week, I was able to achieve  750K rows/sec (runtime: 213 seconds) on a SuperMicro, AMD 48 Core machine with 4 Fusion-io cards with this test fully tuned. I used 48 data files for best throughput, the subject of a future blog.

Continue reading…

Skyrim Optimal Character Build

imageThe winter solstice peace has finally settled and I am heading up North, Scotland, to indulge in good food, good company and great amounts of Whiskey.

In the little spare time I had the last month, I have had a chance to play Skyrim. Some people may call me a sad character, but I apply tuning to all aspects of my life – including the computer games I play.

Here follows my guide to the optimal Skyrim Character Build. If you do not play Skyrim, this article will be lost on you, but I hope to post more interesting things during this vacation.

Here follows my guide to the Optimal Skyrim Character Build. If you do not play Skyrim, this article will be lost on you.

imageThe winter solstice peace has finally settled and I am heading up North, Scotland, to indulge in good food, good company and great amounts of Whiskey.

In the little spare time I had the last month, I have had a chance to play Skyrim. Some people may call me a sad character, but I apply tuning to all aspects of my life – including the computer games I play.

Here follows my guide to the optimal Skyrim Character Build. If you do not play Skyrim, this article will be lost on you, but I hope to post more interesting things during this vacation.

imageThe winter solstice peace has finally settled and I am heading up North, Scotland, to indulge in good food, good company and great amounts of Whiskey.

In the little spare time I had the last month, I have had a chance to play Skyrim. Some people may call me a sad character, but I apply tuning to all aspects of my life – including the computer games I play.

Here follows my guide to the optimal Skyrim Character Build. If you do not play Skyrim, this article will be lost on you, but I hope to post more interesting things during this vacation.

imageThe winter solstice peace has finally settled and I am heading up North, Scotland, to indulge in good food, good company and great amounts of Whiskey.

In the little spare time I had the last month, I have had a chance to play Skyrim. Some people may call me a sad character, but I apply tuning to all aspects of my life – including the computer games I play.

Here follows my guide to the optimal Skyrim Character Build. If you do not play Skyrim, this article will be lost on you, but I hope to post more interesting things during this vacation.

Continue reading…

The Big Picture – EDW/DW architecture

Now that the cat is out of the bag on the Kimball forum, I figured it would be a good idea to present the full architecture that I will be arguing for. I was hoping to build up to it slowly, by establishing each premise on its own before moving on to the conclusion.

But perhaps it is better to start from the conclusion and then work my way down to the premises and show each one in turn.

Continue reading…

Zombie vs Sheep

Physically Placing the Maps in the architecture

Before we leave the maps behind, I need to live up to my promise of describing the storage characteristics of tables visited during the journey through the warehouse architecture. This must include the physical location of maps. Since believe form must follow function in a DW, let us just recall their function:

From a functional perspective, I have shown you how map tables can be used to both track and correct source system keys. Maps are not visible to the end user, but they are a necessary part of the data’s journey from the source to the final data model. Maps also provide the abstraction of, or interface to, master data sources. In the absence of those sources – the maps can even serve as a makeshift master data repository.

Continue reading…