Friday, October 14, 2011

When sp_ doesn’t mean “stored procedure”

For some people in the community this seems to be very common knowledge. Well, if it’s so then shame on me, because I didn’t know…

I have been asked some times how those special system stored procs like sp_help work. Those sprocs that only exist in master database, but still you can call them from every DB you are in. So far I thought this was something special, internal, of SQL Server… Well, wrong…

The trick is easy: Whatever object you create in master database that is called sp_<something> you can automatically use in every DB on that server. Because in this case sp_ doesn’t mean “stored procedure” but it means “special”…

Thanks at this point to Kelan Delaney who brought this up at a presentation today…

Wednesday, October 5, 2011

The day Sequences saved the world

In my last post I brought up the idea of reverse indexes and how those could save you from latch contentions.Well, after another day of hard work it turns out that the new Sequence feature in SQL Denali really is a kind of universal life saver for high load OLTP applications… I don’t know how many sequences we did already to get rid of latch contention and last page inserts, but I can tell you, it were quite a few…

There are two things I have to add though regarding my last post:

First… If you run sequences in really high load environments you have to use the CACHE feature. In the case of a server crash this might leave you with gaps in your sequence, but if you don’t use the Cache you will get locking issues on the sequence once you hit somewhere around 10.000 Fetch next statements per second.

And second… While my T-SQL bit reverser works perfectly fine it is sort of CPU intensive… Our 80-core server burnt about 1% CPU per 1000 rows inserted, with more than half of that going into the bit reverser. I did a lot of tests following this finding, and it almost hurts me to say that, but in this one case SQL CLR really is the best solution you can have… Using the CLR function instead of the T-SQL function we are almost down to nothing for the bit reverse.

Oh, and here is the code of the CLR function: (If anyone wants the compiled DLL or the complete solution please ping me and I’ll mail it to you.)

public static Int64 Reverse(Int64 inVal)
    ulong Result = 0;
    ulong Input = (ulong)inVal;
    int i;
    for (i = 0; i < 63; i++) // 64 bits...
        Result <<= 1; // Shift result by one digit
        if ((Input & 1) == 1) // If lowest bit of input is 1 add one to result
        Input >>= 1; // Now shift the input so that the next bit is lowest
    return (Int64) Result;

Monday, October 3, 2011

Bit reversion

This might seem to be something you never need in SQL Server, but maybe you do and just never knew, so hear me out:
The problem started with a typical scenario for logging tables and even more with some OLTP tables that are heavily inserted: You have an identity column as your primary key (because GUIDs are taking too much time, or you just want bigints or whatever…) and due to the latch contention on the last index page you just can’t get more inserts than 10.000 a second. (OK, that might be enough for almost everyone… But maybe you are not almost everyone?) So what to do? The idea is called “Reverse index”… (Some other DB systems have those out of the box, SQL doesn’t… Doesn’t matter, we can build one…) the basic idea behind it is that you do a binary flip of the increasing number. How does this help? Well, look at the values you get: (tinyint as a sample…)
Identity value Binary Reverse New value
1 00000001 10000000 128
2 00000010 01000000 64
3 00000011 11000000 172
4 00000100 00100000 32
5 00000101 10100000 160
6 00000110 01100000 96
You see how the values keep jumping? Now there is no latch contention anymore on the PK index…
OK, now how do you accomplish this?
First you need to get rid of the identity. Leave the PK without default value, or build your default with what comes next.
Second we need to get a new unique number. Let us all bow before SQL Server Denali, because it comes bearing gifts for us… The magic word is called SEQUENCE. and it is simple:
Now you get the next value calling:
SET @Variable= NEXT VALUE FOR <SomeSequenceName>
Now all you need to do is the inversion… And here is how that is done: (The code works for Bigint…)
    @Input bigint
RETURNS bigint
    DECLARE @WorkValue bigint=@Input
    DECLARE @Result bigint=0;
    DECLARE @Counter int=0;
    WHILE @Counter<63
        SET @Result=@Result*2
        IF (@WorkValue&1)=1
            SET @Result=@Result+1
            SET @WorkValue=@WorkValue-1
        SET @WorkValue=@WorkValue/2
        SET @Counter=@Counter+1
    RETURN @Result
And now you can glue this together… If you want a default value it might be easiest to get rid of the input Parameter for the BitReverse and query the sequence in the function itself, but this is up to you.