IBM M13 Keyboard

Turn off CAPS LOCK and INS

I am a ten finger typist doing around 5 strokes/second – though I do make a lot of mistakes. I learned the craft on a good old fashioned typewriter. The world has moved; unfortunately, keyboards have not. There are keys left in a modern keyboard that are simply of no use anymore – grim reminders of the age of typewriters or just leftovers from ancient (before 2000) times in computers.

Two such keys are CAPS LOCK and INS. As fast typist, you simply don’t need CAPS LOCK. And I have no idea what anyone needs INS for anymore. In other words, hitting either of those keys is always an error. Fortunately, there is a solution that does not require you to take a screwdriver to your keyboard: you can turn those keys off.

The attached registry file will do this nicely. Just unzip and apply.

Registry-IconDisableUselessKeys.Reg

Featured image: The legendary IBM M13 in Stealth Black.

Shooting yourself in the foot

BULK INSERT with Linux Line Endings

After all these years, Mac, Windows and Linux still cannot agree on what special characters are used to represent a new line in a text file.

Windows generated text files will typically end a line with CR+LF (0x0D0A). Unix/Linux prefers LF alone (0x0A) and old versions of Macs will use CR alone (0x0D). See this Wikipedia entry for details.

When receiving and loading text files into databases, this causes a mess unless you are careful. Unfortunately, this is particularly true for the BULK INSERT command in SQL Server because the documentation is misleading.

Continue reading…

Two clueless consultants

How I hire a DBA or Database Developer

Livedrive is currently looking to hire a rock star DBA and developers with a strong understanding of databases. We have 100TB of mySQL data online and a SQL server mirror running at 15k tx/sec non stop (peaking at 200K) with a nice little 3TB OLTP system.

And my goodness – rock star DBAs are hard to find. For those of you looking for one – or thinking you are one – I wanted to write up my advice.

Here is how I hire a DBA:

Continue reading…

Computer Bugs

Race Condition when Creating Unique Values

During my tuning sessions, there is a race condition I run across so often that I think it is worth blogging about it. It concerns the handling of a common requirement: How do I check if the value I want to insert is in the table already? And if it is, don’t insert the value.

Here is how the typical, non DBA person, often does this:

The problem with this pattern is that it is wrong. Under transaction isolation levels less than SERIALIZABLE, the above code does not guarantee that you won’t insert a duplicate.

Continue reading…

Computer Bugs

Curious Partition Function Behaviour

imageJust another short blog today describing a curious issue I found with a query plan this week and a “workaround”.

In our core system, we have a table with two partitions. One partition contains all the work that “has been done” (which has the column WorkItem set to –1) and the other the “work   in progress” (with WorkItem to different values, all > -1).

The reason we have created just two partitions for this table (which is a heap) is that the items that are “work in progress” are often scanned, yet the work that has been done (WorkItem = –1) is the vast majority of the table. This “mini partitioning” is a nice design pattern I often apply to skewed distribution like these. It provides a significant performance boost on table scans. But this week I saw an oddity I have not run into before.

Continue reading…

SQL Server Logo

SQL Server Configuration

Throughout the years, I have become convinced that the default settings used in SQL Server are often wrong for systems that need scale or are properly managed. There is a series of settings I find myself consistently changing when I audit or install a new server. They are “my defaults”

I thought I would share those here. Bear in mind that these are setting that assume a certain level of rational behaviour in the organisation you find yourself in. If you are working in a bank, they may not apply to you.

Continue reading…

Shooting yourself in the foot

Clustered Index vs. Heap

At Stack Overflow the other day, I once again found myself trying to debunk a lot of the “revealed wisdom” in the SQL Server community. You can find the post here: Indexing a PK GUID in SQL Server 2012 to read the discussion. However, this post is not about GUID or sequential keys, which I have written about elsewhere, it is about cluster indexes and the love affair that SQL Server DBAs seem to have with them.

Continue reading…

Ice dancers in sync with each other

Synchronisation in .NET– Part 4: Partitioned Data Structures

In this final instalment of the synchronisation series, we will look at fully scalable solutions to the problem first stated in Part 1 – adding monitoring that is scalable and minimally intrusive.

Thus far, we have seen how there is an upper limit on how fast you can access cache lines shared between multiple cores. We have tried different synchronisation primitives to get the best possible scale.

Throughput this series, Henk van der Valk has generously lent me his 4 socket machine and been my trusted lab manager and reviewer. Without his help, this blog series would not have been possible.

And now, as is tradition, we are going to show you how to make this thing scale.

Continue reading…