Tuesday, July 8, 2014

Ascending Key in SQL 2014

Don’t know if you are all aware of that, but just to make sure…

SQL 2014 introduced a new cardinality estimator that gets automatically activated once you switch the compatibility level to 120. In general this is a very cool thing, and in the very most cases it brings better results than the old one. There is one scenario though where you might face problems with it: The cardinality estimator was never very good with ascending keys. (e.g. Identity columns.) The old one was bad, the new one is exactly as bad… The problem is… For the old one there was a workaround with Traceflags 2389 and 2390. Those DO NOT work with the new cardinality estimator anymore, they are just ignored. So if you are using those TFs be aware of that change.

Wednesday, January 22, 2014

AppLocks in Hekaton

All of you who followed the development of the SQL Server 2014 InMemory OLTP Engine (aka “HEKATON”) will know that Hekaton per definition does not support locks. While this is a good thing per se, after all it’s all about speed, there are scenarios where you would need locks to ensure data integrity and avoid massive amounts of retries. Now you can of course go down the easy road and use sp_getapplock to take your own, application intended, lock to circumvent the problem, but that approach comes with major drawbacks:

1) To do that you need to have a transaction around your code block, which can unnecessarily slow down your operations and in case of “interlocking locks” be a real annoyance.

2) More importantly if you go down that road you are bound to hit the transaction log, even if all your tables in the transaction are in memory only. And THAT can really bring your performance down.

So… What to do about it? Well… If you are me… Write your own sp_getapplock using Hekton tables. The Pro’s of that are that you neither need a transaction nor hit the TLog of your database, the Con is that you are outside the control of SQLs Lock Manager, meaning that you have to ensure you cleanly release those “locks” on all possible codepaths.

What does that look like? Well… Take a look…

1) You need a table to store your locks in. I used a 200 characters nvarchar as a key, but you are really open to do whatever suits you:

CREATE TABLE dbo.HK_AppLock
(
    LockKey nvarchar(200) COLLATE LATIN1_GENERAL_100_BIN2 NOT NULL,
    LockDate datetime NOT NULL

   CONSTRAINT PK_HK_AppLock PRIMARY KEY NONCLUSTERED HASH (LockKey) WITH (BUCKET_COUNT=300000)
   ,INDEX IX_HK_AppLock_Expired (LockDate)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)

Note that I have two indexes on that table. The Primary Key is built as a Hash Index to ensure maximum speed on point lookup (which is the normal scenario), the Expiry-Index is used for orphaned lock cleanup only (Range Scan). Also note that I use SCHEMA_ONLY duarability as persisting locks is not really something you need in daily operations…

2) Build Sprocs for GetAppLock and ReleaseAppLock

CREATE PROCEDURE sp_HK_GetAppLock
    @Key nvarchar(200)
AS
    SET NOCOUNT ON

    DECLARE @Error int
    DECLARE @LockTaken bit=0
    WHILE @LockTaken=0
    BEGIN
        BEGIN TRY
            INSERT INTO HK_AppLock(LockKey, LockDate)
            VALUES (@Key, GETUTCDATE())

            SET @LockTaken=1
        END TRY
        BEGIN CATCH
            SET @Error=ERROR_NUMBER()

            IF (@Error = 41302 OR @Error = 41301 OR @Error = 41305 OR @Error = 41325)
                WAITFOR DELAY '00:00:00:001'
            ELSE IF (@Error = 2627 OR @Error = 2601)
                WAITFOR DELAY '00:00:00:050'
            ELSE
                THROW
        END CATCH
    END

The idea is pretty simple here… Insert the requested lock into the table. If someone else holds the lock already (=the record is already in the table) we will see a PK violation, which is the indicator here. The Catch-Block handles three scenarios: First block handles W/W conflicts with a very short delay + retry, second block handles the PK violation, taking a longer delay in sort of a “spin lock” approach, third block throws whatever other exception we run into up to the client. So effectively what you get is a block until the Insert succeeds.

Note that I do not use native compilation here. Reason for that is that in Native sprocs you have no way of “retrying”, as the transaction context will never change in there.

CREATE PROCEDURE sp_HK_ReleaseAppLock
    @Key nvarchar(200)
AS
    SET NOCOUNT ON

    DECLARE @Error int
    DECLARE @LockReleased bit=0
    WHILE @LockReleased=0
    BEGIN
        BEGIN TRY
            DELETE FROM HK_AppLock
            WHERE LockKey=@Key

            SET @LockReleased=1
        END TRY
        BEGIN CATCH
            SET @Error=ERROR_NUMBER()

            IF (@Error = 41302 OR @Error = 41301 OR @Error = 41305 OR @Error = 41325)
                WAITFOR DELAY '00:00:00:001'
            ELSE
                THROW
        END CATCH
    END

Release goes the same way as Get, No magic here.

3) Last thing is to simulate SQLs lock manager. Meaning: Add a way to clean up orphaned locks.

CREATE PROCEDURE sp_HK_CleanupAppLock
AS
    SET NOCOUNT ON

    WHILE 1=1
    BEGIN
        BEGIN TRY
            DELETE FROM HK_AppLock
            WHERE LockDate<DATEADD(s, -30, GETUTCDATE())
        END TRY
        BEGIN CATCH
        END CATCH
        WAITFOR DELAY '00:00:05'
    END

What I do in my environment is have an Agent job starting every minute, calling that sproc. The sproc actually never finishes, so the 1-minute-schedule is just a precaution in case of problems and server restarts. In my implementation we assume that every lock existing longer than 30 seconds is orphaned. You can of course build more sophisticated logic in there, like including SPIDs or whatever else in the table to allow for a “real” lock management.

All in all the code is very simple and highly efficient. (at least compared to sp_GetAppLock…) It doesn’t solve all problems, but for us it sure solved many of them.

Wednesday, July 10, 2013

FileStream and Windows 2012

I promised you an update when the fix arrives… Here it is: The Microsoft fix for the AlwaysOn FileStream problem with Windows 2012 is published under KB 2835620

Project Hekaton and BLOBs

I have kept silent for quite a while, mostly because I was so busy test-driving Hekaton that I didn’t have much time for other things. And as Hekaton was under NDA for a long while I just couldn’t tell you anything about it. Well, that’s over now… And here is my first post on the topic:

As you might have heard by now Hekaton does not support large objects. (The limitation states that a row is not allowed to exceed 8000 bytes.) Microsofts comment on that is that normally large objects and in memory technology don’t go well together anyway, and you should maybe leave the BLOB in a relational table while putting the rest of the data in memory.

Well, … I disagree… There are some areas where having a BLOB in a latch-free, highspeed environment just makes a lot of sense. Dilema? No… Because there is an easy way to work around the limitation in the Hekaton engine. (Which by the way is the reason I didn’t oppose this limitation a lot harder…) The key idea is to split the BLOB on storing and reunite it on retrieval. Sounds a little stupid, but actually works like a charm, and even hell fast…So… Now that you know the concept you can for sure implement it yourself.

For those of you that are too lazy for that, or just don’t know what the hell I am talking about, here is the POC. It supports Store, Update and Retrieve of a BLOB in Hekaton, based on an nvarchar key. The way I implement this in my projects is to replace the original BLOB field with a reference key field (e.g. a GUID) and then use the sprocs in my script to actually handle the BLOB. It’s not as elegant as I would have liked it to be, but it works for all applications I came across so far.

Please bear in mind that this is a POC script only, it lakes error handling in most cases and for sure doesn’t cover everything. But it should get you a good idea of how it’s done. Therefore I take no warranty whatsoever for the script and what happens to your system by using it.

If you have questions about it, or about anything else in the Hekaton space for that matter please feel free to ping me.

Tuesday, February 12, 2013

FileTable and Windows 2012

I had hoped for a quick fix by Microsoft, otherwise I would have posted this earlier…

A while back I published a white paper on how to run a backup solution using FileTables. Please be aware that this solution currently only works if you use SQL Server 2012 on Windows Server 2008 R2. Reason for that is a bug in Windows Server 2012 that causes FileStream not to work in conjunction with AlwaysOn Availabililty Groups. (The solution will look fine in the management tools, but the FileStream will not be available via the AGs listener.)

I’ll put a post up once a hotfix is available.

Wednesday, November 14, 2012

Follow Up: FileTable with AlwaysOn AGs – Bug

I wrote a post a while back talking about two issues with FileTable in SQL right now and said that they would be fixed in SP1… Well… Service Pack 1 is released now, but unfortunately one of the fixes didn’t make it in, as it posed different problems, up to creating BSODs on the system. The checkpoint issue is fixed, so failover works seamlessly now, what is still missing is the sync issue.

The way this leftover issue presents itself is during a failover scenario where clients still can connect to the old primary. If the old primary is completely unreachable your data will be safe. So I know the workaround is shitty, but for now I can only advise you to not do manual failovers and in case you need to move a DB for maintenance reasons you do so by either cutting the network link to the old primary box or send it into a BSOD. Again, this is shitty, but at least it is safe. And it will still give you high availability…

I’ll keep you posted on the progress of the resolution.

Wednesday, October 17, 2012

Is that DB online?

Ever had the need to figure out if the DB you are connecting to is actually online? Like for example if you have a SQL Agent Job that runs on top of a mirrored database? That is a fairly easy task after all when you only consider Mirroring or LogShipping, in that case you could just query sys.databases and look at the state. But once you have AlwaysOn involved the thing gets a little tricky, as AlwaysOn Availability Groups don’t set the state right…

So here is the simple way of doing it right for all versions starting with Yukon up to Denali:

CREATE FUNCTION [dbo].[ufn_IsDatabaseOnline](
    @dbname sysname) RETURNS bit
AS
BEGIN
    DECLARE @RetVal bit
    SET @RetVal=0
    DECLARE @Role int

    select @Role=mirroring_role from sys.database_mirroring m
    inner join sys.databases d ON m.database_id=d.database_id
    where mirroring_guid is not null
    AND d.name=@dbname

    -- Check if Mirror and Principal
    if (@Role IS NOT NULL)
        if (@Role=1)
            SET @RetVal=1
    IF (SELECT @@MICROSOFTVERSION / 0x01000000)>=11
    BEGIN
        -- Only check HADRON if Version is 11 or higher
        select @Role=rs.role from sys.dm_hadr_database_replica_states dr
        inner join sys.dm_hadr_availability_replica_states rs on dr.group_id=rs.group_id
        inner join sys.databases d ON dr.database_id=d.database_id
        WHERE dr.is_local=1 AND rs.is_local=1
        AND d.name=@dbname

        -- Check if HADRON and Principal
        if (@Role IS NOT NULL)
            if (@Role=1)
                SET @RetVal=1
    END

    -- Handle Non-Mirrored/HADRed DBs
    IF (@Role IS NULL)
    BEGIN
        IF(SELECT state FROM Sys.databases WHERE name=@dbname)=0
            SET @RetVal=1
    END

    RETURN @RetVal
END