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

Monday, August 13, 2012

Copying permissions

As it gets asked time and again on forums… Here is a script to copy all permissions from a database:

SET NOCOUNT ON
DECLARE    @OldUser sysname, @NewUser sysname

DECLARE PC CURSOR FOR SELECT name FROM sys.database_principals WHERE Principal_ID>4 AND is_fixed_role=0

OPEN PC
FETCH NEXT FROM PC INTO @OldUser

WHILE @@FETCH_STATUS=0
BEGIN

SET    @NewUser = @OldUser

SELECT '-- '+@OldUser AS '-- Current User'

SELECT    'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'


SELECT    '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser) AS '--Comment'


SELECT    'EXEC sp_addrolemember @rolename ='
    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'
FROM    sys.database_role_members AS rm
WHERE    USER_NAME(rm.member_principal_id) = @OldUser
ORDER BY rm.role_principal_id ASC


SELECT    CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
    + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
    + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
FROM    sys.database_permissions AS perm
    INNER JOIN
    sys.objects AS obj
    ON perm.major_id = obj.[object_id]
    INNER JOIN
    sys.database_principals AS usr
    ON perm.grantee_principal_id = usr.principal_id
    LEFT JOIN
    sys.columns AS cl
    ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
WHERE    usr.name = @OldUser
ORDER BY perm.permission_name ASC, perm.state_desc ASC


SELECT    CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
    + SPACE(1) + perm.permission_name + SPACE(1)
    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
FROM    sys.database_permissions AS perm
    INNER JOIN
    sys.database_principals AS usr
    ON perm.grantee_principal_id = usr.principal_id
WHERE    usr.name = @OldUser
AND    perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC

FETCH NEXT FROM PC INTO @OldUser
END
CLOSE PC
DEALLOCATE PC