tag:blogger.com,1999:blog-70105948661396946032024-03-01T00:17:31.947-08:00Rick's SQL Server BlogRickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.comBlogger21125tag:blogger.com,1999:blog-7010594866139694603.post-84386819718554181882014-07-08T04:24:00.001-07:002014-07-08T04:25:00.169-07:00Ascending Key in SQL 2014<p>Don’t know if you are all aware of that, but just to make sure…</p> <p>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.</p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com1tag:blogger.com,1999:blog-7010594866139694603.post-25224373425257332512014-01-22T23:39:00.001-08:002014-01-22T23:39:13.000-08:00AppLocks in Hekaton<p>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: </p> <p>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.</p> <p>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.</p> <p>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. </p> <p>What does that look like? Well… Take a look…</p> <p>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:</p> <p>CREATE TABLE dbo.HK_AppLock<br>(<br> LockKey nvarchar(200) COLLATE LATIN1_GENERAL_100_BIN2 NOT NULL,<br> LockDate datetime NOT NULL</p> <p> CONSTRAINT PK_HK_AppLock PRIMARY KEY NONCLUSTERED HASH (LockKey) WITH (BUCKET_COUNT=300000)<br> ,INDEX IX_HK_AppLock_Expired (LockDate)<br>) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)<br></p> <p>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…</p> <p>2) Build Sprocs for GetAppLock and ReleaseAppLock</p> <p>CREATE PROCEDURE sp_HK_GetAppLock<br> @Key nvarchar(200)<br>AS<br> SET NOCOUNT ON</p> <p> DECLARE @Error int<br> DECLARE @LockTaken bit=0<br> WHILE @LockTaken=0<br> BEGIN<br> BEGIN TRY<br> INSERT INTO HK_AppLock(LockKey, LockDate)<br> VALUES (@Key, GETUTCDATE())</p> <p> SET @LockTaken=1<br> END TRY<br> BEGIN CATCH<br> SET @Error=ERROR_NUMBER()</p> <p> IF (@Error = 41302 OR @Error = 41301 OR @Error = 41305 OR @Error = 41325)<br> WAITFOR DELAY '00:00:00:001'<br> ELSE IF (@Error = 2627 OR @Error = 2601)<br> WAITFOR DELAY '00:00:00:050'<br> ELSE<br> THROW<br> END CATCH<br> END<br></p> <p>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.</p> <p>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.</p> <p>CREATE PROCEDURE sp_HK_ReleaseAppLock<br> @Key nvarchar(200)<br>AS<br> SET NOCOUNT ON</p> <p> DECLARE @Error int<br> DECLARE @LockReleased bit=0<br> WHILE @LockReleased=0<br> BEGIN<br> BEGIN TRY<br> DELETE FROM HK_AppLock <br> WHERE LockKey=@Key</p> <p> SET @LockReleased=1<br> END TRY<br> BEGIN CATCH<br> SET @Error=ERROR_NUMBER()</p> <p> IF (@Error = 41302 OR @Error = 41301 OR @Error = 41305 OR @Error = 41325)<br> WAITFOR DELAY '00:00:00:001'<br> ELSE<br> THROW<br> END CATCH<br> END<br></p> <p>Release goes the same way as Get, No magic here.</p> <p>3) Last thing is to simulate SQLs lock manager. Meaning: Add a way to clean up orphaned locks. </p> <p>CREATE PROCEDURE sp_HK_CleanupAppLock<br>AS<br> SET NOCOUNT ON</p> <p> WHILE 1=1<br> BEGIN<br> BEGIN TRY<br> DELETE FROM HK_AppLock<br> WHERE LockDate<DATEADD(s, -30, GETUTCDATE())<br> END TRY<br> BEGIN CATCH<br> END CATCH<br> WAITFOR DELAY '00:00:05'<br> END<br></p> <p>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. </p> <p>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.</p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com1tag:blogger.com,1999:blog-7010594866139694603.post-28437788957967805682013-07-10T07:50:00.001-07:002013-07-10T07:50:21.882-07:00FileStream and Windows 2012<p>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 <a href="http://support.microsoft.com/kb/2835620" target="_blank">2835620</a></p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com1tag:blogger.com,1999:blog-7010594866139694603.post-81733904446180192342013-07-10T00:31:00.001-07:002013-07-10T00:31:03.081-07:00Project Hekaton and BLOBs<p>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:</p> <p>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. </p> <p>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. </p> <p>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 <a href="http://www.mrc.at/files/hkblob.zip">POC</a>. 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.</p> <p>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.</p> <p>If you have questions about it, or about anything else in the Hekaton space for that matter please feel free to ping me.</p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com1tag:blogger.com,1999:blog-7010594866139694603.post-29285470026555652312013-02-12T02:48:00.001-08:002013-02-12T02:48:04.287-08:00FileTable and Windows 2012<p>I had hoped for a quick fix by Microsoft, otherwise I would have posted this earlier… </p> <p>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.)</p> <p>I’ll put a post up once a hotfix is available.</p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com2tag:blogger.com,1999:blog-7010594866139694603.post-82498646223089539932012-11-14T09:48:00.001-08:002012-11-14T09:48:12.087-08:00Follow Up: FileTable with AlwaysOn AGs – Bug<p>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. </p> <p>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…</p> <p>I’ll keep you posted on the progress of the resolution.</p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com0tag:blogger.com,1999:blog-7010594866139694603.post-24006845940979117322012-10-17T09:46:00.001-07:002012-10-17T09:46:38.413-07:00Is that DB online?<p>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… </p> <p>So here is the simple way of doing it right for all versions starting with Yukon up to Denali:</p> <p>CREATE FUNCTION [dbo].[ufn_IsDatabaseOnline](<br> @dbname sysname) RETURNS bit<br>AS<br>BEGIN<br> DECLARE @RetVal bit<br> SET @RetVal=0<br> DECLARE @Role int</p> <p> select @Role=mirroring_role from sys.database_mirroring m <br> inner join sys.databases d ON m.database_id=d.database_id<br> where mirroring_guid is not null<br> AND d.name=@dbname</p> <p> -- Check if Mirror and Principal<br> if (@Role IS NOT NULL)<br> if (@Role=1)<br> SET @RetVal=1<br> IF (SELECT @@MICROSOFTVERSION / 0x01000000)>=11<br> BEGIN<br> -- Only check HADRON if Version is 11 or higher<br> select @Role=rs.role from sys.dm_hadr_database_replica_states dr<br> inner join sys.dm_hadr_availability_replica_states rs on dr.group_id=rs.group_id<br> inner join sys.databases d ON dr.database_id=d.database_id<br> WHERE dr.is_local=1 AND rs.is_local=1<br> AND d.name=@dbname</p> <p> -- Check if HADRON and Principal<br> if (@Role IS NOT NULL)<br> if (@Role=1)<br> SET @RetVal=1<br> END</p> <p> -- Handle Non-Mirrored/HADRed DBs<br> IF (@Role IS NULL)<br> BEGIN<br> IF(SELECT state FROM Sys.databases WHERE name=@dbname)=0<br> SET @RetVal=1<br> END</p> <p> RETURN @RetVal<br>END</p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com1tag:blogger.com,1999:blog-7010594866139694603.post-66801384081330144692012-08-13T23:45:00.001-07:002012-08-13T23:45:57.658-07:00Copying permissions<p>As it gets asked time and again on forums… Here is a script to copy all permissions from a database:</p> <p>SET NOCOUNT ON<br>DECLARE @OldUser sysname, @NewUser sysname</p> <p>DECLARE PC CURSOR FOR SELECT name FROM sys.database_principals WHERE Principal_ID>4 AND is_fixed_role=0</p> <p>OPEN PC<br>FETCH NEXT FROM PC INTO @OldUser</p> <p>WHILE @@FETCH_STATUS=0<br>BEGIN</p> <p>SET @NewUser = @OldUser</p> <p>SELECT '-- '+@OldUser AS '-- Current User'</p> <p>SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'</p> <p><br>SELECT '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser) AS '--Comment'</p> <p><br>SELECT 'EXEC sp_addrolemember @rolename =' <br> + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'<br>FROM sys.database_role_members AS rm<br>WHERE USER_NAME(rm.member_principal_id) = @OldUser<br>ORDER BY rm.role_principal_id ASC</p> <p><br>SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END<br> + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) <br> + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END<br> + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default<br> + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'<br>FROM sys.database_permissions AS perm<br> INNER JOIN<br> sys.objects AS obj<br> ON perm.major_id = obj.[object_id]<br> INNER JOIN<br> sys.database_principals AS usr<br> ON perm.grantee_principal_id = usr.principal_id<br> LEFT JOIN<br> sys.columns AS cl<br> ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id<br>WHERE usr.name = @OldUser<br>ORDER BY perm.permission_name ASC, perm.state_desc ASC</p> <p><br>SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END<br> + SPACE(1) + perm.permission_name + SPACE(1)<br> + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default<br> + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'<br>FROM sys.database_permissions AS perm<br> INNER JOIN<br> sys.database_principals AS usr<br> ON perm.grantee_principal_id = usr.principal_id<br>WHERE usr.name = @OldUser<br>AND perm.major_id = 0<br>ORDER BY perm.permission_name ASC, perm.state_desc ASC</p> <p>FETCH NEXT FROM PC INTO @OldUser<br>END<br>CLOSE PC<br>DEALLOCATE PC</p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com0tag:blogger.com,1999:blog-7010594866139694603.post-33874971303712052872012-07-27T00:50:00.001-07:002012-07-27T00:50:06.866-07:00FileTable with AlwaysOn AGs – Bug<p>After a long while of testing and troubleshooting we identified two bugs in SQL Server that can hit you if you run FileTables in conjunction with AlwaysOn Availability Groups. One concerns the way Checkpoints are done in FileStream, which could lead to very long failover times, the other one concerns a handle issue that could lead to the system running out of sync without noticing. </p> <p>Right now there is no feasible workaround to both problems, but Microsoft has already confirmed that both issues will be fixed in Service Pack 1. </p> <p>If you should encounter this problem and need help working around it please ping me and I will share the necessary details. </p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com3tag:blogger.com,1999:blog-7010594866139694603.post-72377054336739815722012-04-23T09:43:00.001-07:002012-04-23T09:43:22.189-07:00Logshipping without a Windows Fileserver<p>I’ve been quiet for quite a while now, but not due to lack of activity… I struggled a long time with a problem in my logshipping configuration: The fileserver as a single point of failure in the middle. Now, finally, I found a solution for that, which doesn’t require the use of expensive disk subsystems to get geo-strechted. And (as always when I have such ideas…) I tried to put it into a blog post, unfortunately only to get to a point where I gave up because of the simple complexity of the idea. Does it mean I gave up the project? Of course not… I just changed the delivery model. </p> <p>So behold… My first whitepaper:</p> <p><a href="http://www.mrc.at/docs/LSFileserver.pdf" target="_blank">Logshipping Fileserver within SQL</a></p> <p>Enjoy. </p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com0tag:blogger.com,1999:blog-7010594866139694603.post-27207430663876961982012-02-06T09:03:00.001-08:002012-02-06T09:03:53.017-08:00Optimized… in Assembler<p>OK, now for the fanatics as always: The same thing I just posted, but in assembler:</p> <p>BitReverse PROC<br>PUSH r13<br><br>MOV rax, rcx<br>MOV r13, rcx<br>SHR rax, 1<br>AND rax, 0x5555555555555555<br>AND r13, 0x5555555555555555<br>SHL r13, 1<br>OR rax, r13</p> <p>MOV r13, rax<br>SHR rax, 2<br>AND rax, 0x3333333333333333<br>AND r13, 0x3333333333333333<br>SHL r13, 2<br>OR rax, r13</p> <p>MOV r13, rax<br>SHR rax, 4<br>AND rax, 0x0F0F0F0F0F0F0F0F<br>AND r13, 0x0F0F0F0F0F0F0F0F<br>SHL r13, 4<br>OR rax, r13</p> <p>MOV r13, rax<br>SHR rax, 8<br>AND rax, 0x00FF00FF00FF00FF<br>AND r13, 0x00FF00FF00FF00FF<br>SHL r13, 8<br>OR rax, r13</p> <p>MOV r13, rax<br>SHR rax, 16<br>AND rax, 0x0000FFFF0000FFFF<br>AND r13, 0x0000FFFF0000FFFF<br>SHL r13, 16<br>OR rax, r13</p> <p>MOV r13, rax<br>SHR rax, 32<br>SHL r13, 32<br>OR rax, r13</p> <p>POP r13</p> <p>ret</p> <p>BitReverse ENDP</p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com0tag:blogger.com,1999:blog-7010594866139694603.post-20372156222763638002012-02-06T08:52:00.001-08:002012-02-06T08:54:43.416-08:00Optimizing the BitReverse<p>Well well well, seems finally a way emerged to do the reversing without the need of a loop. And here is the trick:</p> <p>Figure this simple starting value (16 bit for demo, works the same with 64 of course):<br>1010000010100000 <br>Now let’s do a little bit math:<br>1) We just flip all odd and even bits using a 0101010101010101 mask. For this we use the original value twice, one time shifted left by 1 bit, one time shifted right, then ANDed with the mask and back ORed together. Here is the code:<br>i=((i>>1) & 0x5555) | ((i & 0x5555)<<1);<br>What does that do? Let’s break it up:<br>i>>1 translates to: 0101000001010000. Now do the AND 0x5555:<br>0101000001010000<br>0101010101010101<br>result:<br>0101000001010000. <br>The original ANDed with 0x5555 means:<br>1010000010100000<br>0101010101010101<br>result:<br>0000000000000000<br>Now OR those together and you get 0101000001010000</p> <p>2) Now we use 0011001100110011 as bit mask and do it again, basically flipping pairs:<br>i=((i>>2) & 0x3333) | ((i & 0x3333)<<2)<br>First half:<br>0101000001010000 >>2 –> 0001010000010100<br>AND 0011001100110011<br>result: 0001000000010000<br>Second half:<br>0101000001010000 AND<br>0011001100110011<br>result: 0001000000010000 << 2 : 0100000001000000 <br>Now OR those:<br>0001000000010000<br>0100000001000000<br>result: 0101000001010000</p> <p>3) Now we swap nibbles, but I think you got the point…<br>0101000001010000 –> 0000010100000101</p> <p>4) Last we swap the bytes. (Which in this case results in no change.) </p> <p>Voila, the bits are flipped… </p> <p>Here is the sample code for 64 bit in C#:</p> <p>[SqlFunction]<br>public static Int64 Reverse(Int64 inVal)<br>{<br> ulong Result=(ulong) inVal;<br> // odd and even<br> Result=((Result>>1)& 0x5555555555555555)|((Result & 0x5555555555555555)<<1);<br> // pairs<br> Result=((Result>>2)& 0x3333333333333333)|((Result & 0x3333333333333333)<<2);<br> // nibbles<br> Result=((Result>>4)& 0x0F0F0F0F0F0F0F0F)|((Result & 0x0F0F0F0F0F0F0F0F)<<4);<br> // bytes<br> Result=((Result>>8)& 0x00FF00FF00FF00FF)|((Result & 0x00FF00FF00FF00FF)<<8);<br> // words<br> Result=((Result>>16)& 0x0000FFFF0000FFFF)|((Result & 0x0000FFFF0000FFFF)<<16);<br> // double words<br> Result=(Result>>32)|(Result<<32);</p> <p> return (Int64) Result;<br>}</p> <p>One thing: If you (like me) want to flip only 63 bits (to keep the value positive) just add Result=Result<<1; before the even/odd flip. </p> <p>Thanks very much to the anonymous poster that brought up the idea. </p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com0tag:blogger.com,1999:blog-7010594866139694603.post-6490656580440851432011-12-28T00:37:00.001-08:002011-12-28T00:37:06.957-08:00MutliSubnetFailover – the DNS conundrum<p>I love new technology and I love new ideas, but sometimes the coolest of those are somewhat not thought through… When Microsoft announced the support for multisubnet clustering in SQL 2012 I was cheerful, as this is a feature that can come in really handy in time like these. Unfortunately in the real world this new feature soon proved to be … let’s say … improvable. </p> <p>Here’s the deal: When you have a virtual network name (VNN) in a cluster with multiple IP addresses behind it in the end what that does is automatically reset DNS entries whenever you failover the group. Now as all of you know (and if you don’t know it’s still a fact unfortunately…) client computers have this nasty thing that’s called “DNS resolver cache”, which basically means that names that have already been resolved to IP addresses will be cached locally and not sent to the DNS server again for a while. (That while is called the “Time to Live”, or TTL for short.) VERY good idea in normal life, otherwise DNS Servers would have a real problem and every request would be WAY slow… Unfortunately… VERY bad idea for failover scenarios like ours with the DB, especially if you require real high availability…</p> <p>OK, now, Microsoft is not THAT stupid… in fact they have quite smart people there and those people thought about this problem too. What they did to tackle this was quite simple: Make the client driver aware of the problem so it can solve it. The way they did this was… well… Interesting at best… They changed the behavior of the VNN so it would always register all IPs in DNS, not only the active one, (ok, this was not a real change, cluster service had this feature for a while now, they just use it…) and build a change into the client to get all those IPs and automatically connect and reconnect to the active one. </p> <p>Nice idea in theory… The problem starts when you begin using applications that are not aware of that idea. Why? Because what happens by default when you have multiple IPs in DNS is that the client fetches a random one (OK, not really random, but it doesn’t matter for now…) and places that one in his cache. If that one is the one that is currently inactive… Well… Let’s call it a bad day… You will just not get a connection open, and the server will not fetch a new IP because the resolver cache already has one… So… Tough luck…</p> <p>OK… Enough with that… So how do you overcome this problem? Well, unfortunately there is no really good way to do it… (Unless of course all your applications can be made aware of the situation by using the latest drivers…) Here is what I would do for now as a next best thing to a solution:</p> <ol> <li>Stop the DNS behavior of the VNN, let it run as it would have normally. Unfortunately you can’t change this in the UI, only via commandline: cluster.exe RES <ResourceName> /PRIV RegisterAllProvidersIP=0</li> <li>Reduce the TTL of that DNS entry to whatever is the longest you can possibly wait for a failover. I chose 15 seconds for that, but feel free to go your own way. Luckily this setting can be changed in the DNS management UI.</li> <li>Still keep the MultiSubnetFailover=True parameter in the apps that support it. It doesn’t really help, but maybe you reach the point at some time where you have a homogenous system again and can use that new pattern.</li></ol> <p>There is an alternative way too that might even be better than this one. But I would strictly only recommend this if your system is rather small. (In numbers of DBs…) You could leave the SQL listener as it is and connect your MutliSubnet aware applications there. Then add a second VNN to the group that is set with minimal TTL and no RegisterAllProvidersIP flag to connect your legacy apps to. This setup will give you maximum availability and flexibility, but it requires extra work and extra IP addresses…</p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com0tag:blogger.com,1999:blog-7010594866139694603.post-62866713383862002932011-11-10T21:54:00.001-08:002011-11-10T21:54:56.661-08:00BitReverse again<p>Just to satisfy the high performance fanatics: Below you find the most efficient reverser algorithm I could write so far.Unfortunately getting this code into SQL Server is not as simple as it might look. ODS (the extended stored procedure API) seems to have more overhead than the CLR and CLR interop also generates more overhead than you can save. If anybody has good ideas on how I can get this efficiently in, please give me a ping.</p> <p>BitReverse PROC <br>PUSH r13</p> <p>XOR rax, rax<br>MOV r13, rcx<br>MOV rcx, 63<br>JumpLabel:<br>SHR r13,1<br>RCL rax,1<br>LOOP JumpLabel<br>POP r13</p> <p>ret</p> <p>BitReverse ENDP</p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com0tag:blogger.com,1999:blog-7010594866139694603.post-68565922752693449632011-10-14T03:12:00.001-07:002011-10-14T03:12:35.341-07:00When sp_ doesn’t mean “stored procedure”<p>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…</p> <p>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…</p> <p>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”… </p> <p>Thanks at this point to Kelan Delaney who brought this up at a presentation today… </p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com0tag:blogger.com,1999:blog-7010594866139694603.post-61387150030794140642011-10-05T10:10:00.001-07:002011-10-05T10:10:15.192-07:00The day Sequences saved the world<p>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… </p> <p>There are two things I have to add though regarding my last post:</p> <p>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.</p> <p>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.</p> <p>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.)</p> <p>[SqlFunction]<br>public static Int64 Reverse(Int64 inVal)<br>{<br> ulong Result = 0;<br> ulong Input = (ulong)inVal;<br> int i;<br> for (i = 0; i < 63; i++) // 64 bits...<br> {<br> Result <<= 1; // Shift result by one digit<br> if ((Input & 1) == 1) // If lowest bit of input is 1 add one to result<br> Result++;<br> Input >>= 1; // Now shift the input so that the next bit is lowest<br> }<br> return (Int64) Result;<br>}</p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com26tag:blogger.com,1999:blog-7010594866139694603.post-68138898032842338332011-10-03T17:44:00.001-07:002011-10-04T18:06:32.419-07:00Bit reversionThis might seem to be something you never need in SQL Server, but maybe you do and just never knew, so hear me out: <br />
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…)<br />
<table border="0" cellpadding="2" cellspacing="0" style="width: 400px;"><tbody>
<tr> <td valign="top" width="100">Identity value</td> <td valign="top" width="100">Binary</td> <td valign="top" width="100">Reverse</td> <td valign="top" width="100">New value</td></tr>
<tr> <td valign="top" width="100">1</td> <td valign="top" width="100">00000001</td> <td valign="top" width="100">10000000</td> <td valign="top" width="100">128</td></tr>
<tr> <td valign="top" width="100">2</td> <td valign="top" width="100">00000010</td> <td valign="top" width="100">01000000</td> <td valign="top" width="100">64</td></tr>
<tr> <td valign="top" width="100">3</td> <td valign="top" width="100">00000011</td> <td valign="top" width="100">11000000</td> <td valign="top" width="100">172</td></tr>
<tr> <td valign="top" width="100">4</td> <td valign="top" width="100">00000100</td> <td valign="top" width="100">00100000</td> <td valign="top" width="100">32</td></tr>
<tr> <td valign="top" width="100">5</td> <td valign="top" width="100">00000101</td> <td valign="top" width="100">10100000</td> <td valign="top" width="100">160</td></tr>
<tr> <td valign="top" width="100">6</td> <td valign="top" width="100">00000110</td> <td valign="top" width="100">01100000</td> <td valign="top" width="100">96</td></tr>
</tbody></table>You see how the values keep jumping? Now there is no latch contention anymore on the PK index… <br />
OK, now how do you accomplish this? <br />
First you need to get rid of the identity. Leave the PK without default value, or build your default with what comes next.<br />
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:<br />
CREATE SEQUENCE <SomeSequenceName> START WITH 1 INCREMENT BY 1<br />
Now you get the next value calling:<br />
SET @Variable= NEXT VALUE FOR <SomeSequenceName><br />
Now all you need to do is the inversion… And here is how that is done: (The code works for Bigint…)<br />
CREATE FUNCTION BitReverse<br />
(<br />
@Input bigint<br />
)<br />
RETURNS bigint<br />
AS<br />
BEGIN<br />
DECLARE @WorkValue bigint=@Input<br />
DECLARE @Result bigint=0;<br />
DECLARE @Counter int=0;<br />
WHILE @Counter<63<br />
BEGIN<br />
SET @Result=@Result*2<br />
IF (@WorkValue&1)=1<br />
BEGIN<br />
SET @Result=@Result+1<br />
SET @WorkValue=@WorkValue-1<br />
END<br />
SET @WorkValue=@WorkValue/2<br />
SET @Counter=@Counter+1<br />
END<br />
<br />
RETURN @Result<br />
<br />
END<br />
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.Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com9tag:blogger.com,1999:blog-7010594866139694603.post-60102999898337152652011-08-17T05:07:00.001-07:002011-08-17T05:07:48.504-07:00SlipStreaming<p>It was not too long ago that I wrote my post regarding slipstreaming. (That is installing patches together with SQL server in one run…) Now it seems that my information is outdated already, as Microsoft changed the way this works in SQL Server “Denali”. And I have to say: THANK YOU Microsoft, this has become a hack of a lot easier… Just add two lines to DefaultSetup.ini and place the patches all in one directory. Almost to easy to even write about it, but just to be complete:</p> <p>[Options]<br>UpdateEnabled=True<br>UpdateSource=".\Update"</p> <p>This is the entry for the defaultsetup.ini. And as you can see relative paths now work fine too.But be advised: The ini file lies in the x64 directory, the UpdateSource path still is relative to Setup.exe!</p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com0tag:blogger.com,1999:blog-7010594866139694603.post-52105410022491832982011-07-26T03:48:00.001-07:002011-07-26T03:48:05.372-07:00Last Man Standing and HADRON<p>Curiously I had the discussion about this twice already so I think it’s time to write it down… </p> <p><strong><font size="4">Warning: This is a constellation I consider purely hypothetical. I know it works (because I tested it…), I also know that it’s supported, but I would think twice before actually doing it…</font></strong></p> <h3>Scenario:</h3> <p>You have two datacenters, one for primary operations, the other one for disaster recovery only. In both DCs you have a SAN that you consider 100% reliable. On each side you have a SQL cluster and the DBs are mirrored (Async) between those clusters. Like this:</p> <p><img src="http://www.mrc.at/images/HA1.jpg" width="517" height="264"></p> <p>The quorum model for both clusters is set to use a quorum disk. Why? Well, here comes the point of the action: Because you want a “Last man standing” configuration in your primary DC, meaning that even if two nodes go down, the DBs should still remain up. </p> <p>Easy so far… Now bad bad Microsoft brings out SQL Server DENALI, which includes features that are WAY cool, and you really really really need those in your environment. (Features like Readable Secondary or Backup from Secondary…) Now what? Those features need HADRON… So how do you convert this scenario to HADRON?</p> <h3>Wishlist solution</h3> <p>What you would like to have is everything the way it was, just replace the cluster at the primary DC with a HADRON cluster (sorry, “AlwaysOn Availability Group” this is called now.) That’s OK, quite easy in fact, if it wasn’t for the Async Mirror and the limitation of HADRON that all members of the Availability Group need to be in the same cluster… OK, so here is what you come up at first:</p> <p><img src="http://www.mrc.at/images/HA2.jpg" width="509" height="265"></p> <p>Now you have the DBs in the primary datacenter on local drives, and still use a SQL cluster in the disaster recovery site. Cool feature by the way… Connecting an AG to a cluster instance. In our scenario this bears the advantage that you only need to transfer the bits once to your DR site. </p> <p>At this point you have all you wanted from a SQL point of view. HADRON is on, so you have Readable Secondaries, Backup on Secondary, etc, all the cool features that DENALI brings. </p> <p>You just have one problem: How do you setup a quorum device for that cluster? You could of course do majority nodeset (as it is recommended by Microsoft for multisite clusters.) But this would spoil the idea of a last man standing configuration in the primary site… And you can’t use a quorum disk anymore as you don’t have a SAN that all nodes can see. So what to do?</p> <h3>The way to do it</h3> <p>When I first came up with that idea the others around the table called me nuts… And somehow I can’t blame them..</p> <p><img src="http://www.mrc.at/images/HA3.jpg" width="507" height="285"></p> <p>The not so simple solution looks like this: There is a SAN in the primary DC, which is considered 100% reliable. What I did is to setup a clustered Micorosft iSCSI Target on the three nodes in the primary DC. (Way cool too by the way, the iSCSI Target is available for free now from Microsoft…) Now I present an iSCSI Lun through that target using the SAN disks as physical storage for it. Next I attach the LUN to all cluster nodes, including the nodes in the DR site. Voila… Now I have a cluster shared disk again on all nodes which I can use as a quorum device for the whole cluster… </p> <p>The effect: I got a last man standing configuration in the primary DC again. The only downside is that if the primary DC goes down my cluster in the DR site also goes down. As bringing the DB online at the DR site means manual intervention anyway this is quite a small drawback… You just have to know that there is now one more thing to do in that case: ForceQuorum on that nodes… </p> <p>Now decide for yourself if that’s mad or not. I agree with the guys that it is. </p> <p>As a sidenote: Special thanks to Mike Steineke, David Smith and Thomas Grohser. They had their fingers in this solutions as well. </p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com1tag:blogger.com,1999:blog-7010594866139694603.post-58041382768987367852011-07-25T22:46:00.001-07:002011-07-25T22:46:01.252-07:00SlipStreaming<p>It took me a while to find the time for that, so sorry for the delay… This post is about how to have one installation run of SQL Server install not only the product itself, but also a Service Pack and a Cumulative Update package. Given the fact that every installation takes a reboot this method can save lots of time, not to mention lots of work. As always there are very good knowledge base articles about that and this post is not supposed to make them obsolete. I will reduce the post to just the plain basics. And give you a walkthrough on how to do it in a few minutes:</p> <ol> <li>You need is a plain installation media. (I usually copy the files from a DVD to a harddrive folder to start that.)</li> <li>You need to unpack the service pack. For that I create myself a “PCU” folder in the same directory as the installation media. Then I run the unpack like this: “SQLServer2008R2SP1-KB258583-x64-ENU.exe /x:<em><BaseDirectory></em>\PCU /Quiet”</li> <li>Same goes for the cumulative update. I normally use the CU folder. The commandline is the same, just of course with the other exe…</li> <li>Of course you can do this again with –x86 as well as –ia64 versions, having them extract into the same directory if you need the slipstream to run on all platforms.</li> <li>Now you need to update two files in the the base image: Setup.exe and Microsoft.SQL.Chainer.PackageData.dll. You should always use the latest version there, so normally the one from the CU. Be advised that the Chainer DLL is in the x64 directory (as well as in the x86 and ia64, so if you slipstream multiple processor architectures you have to replace all of the chainer DLLs.)</li> <li>Last thing: Update DefaultSetup.ini in the x64 directory (and x86, ia64 as before) to include the updates. The line looks like that: PCUSource=”C:\Install\SQL2008R2\PCU”. (That’s the line for the service pack. For the cumulative update it’s called CUSource.) Notice that I used full qualified path names. You can of course use relative paths as well, but I had some difficulties with those in the past, so I changed it. </li></ol> <p>That’s it… So how do you get Setup now to use the slipstream? Well, just click on Setup.exe… Everything else will just work…</p> <p>And now for the lazy ones around: Here is the script I use for slipstreaming: (Notice that %1 is the target directory where the plain SQL Server copy is.)</p> <p>ECHO Unpacking Service Pack...<br>ECHO x86...<br>SQLServer2008R2SP1-KB2528583-x86-ENU.exe /x:%1\PCU /quiet<br>ECHO x64...<br>SQLServer2008R2SP1-KB2528583-x64-ENU.exe /x:%1\PCU /quiet<br>ECHO IA64...<br>SQLServer2008R2SP1-KB2528583-IA64-ENU.exe /x:%1\PCU /quiet</p>ECHO SlipStreaming Service Pack...<br> <p>robocopy %1\PCU %1 Setup.exe >NUL 2>NUL<br>robocopy %1\PCU %1 Setup.rll >NUL 2>NUL</p> <p>ECHO x86...<br>robocopy %1\pcu\x86 %1\x86 /XF Microsoft.SQL.Chainer.PackageData.dll >NUL 2>NUL<br>ECHO x64...<br>robocopy %1\pcu\x64 %1\x64 /XF Microsoft.SQL.Chainer.PackageData.dll >NUL 2>NUL<br>ECHO IA64...<br>robocopy %1\ia64 %1\ia64 /XF Microsoft.SQL.Chainer.PackageData.dll >NUL 2>NUL</p> <p>REM Currently no CU for R2 SP1<br>GOTO EndCU</p> <p>ECHO Unpacking Cumulative Update...<br>ECHO x86...<br>SQLServer2008R2-KB2534352-x86 /x:%1\CU /quiet<br>ECHO x64...<br>SQLServer2008R2-KB2534352-x64 /x:%1\CU /quiet<br>ECHO IA64...<br>SQLServer2008R2-KB2534352-IA64 /x:%1\CU /quiet</p>ECHO SlipStreaming Cumulative Update...<br> <p>robocopy %1\CU %1 Setup.exe >NUL 2>NUL<br>robocopy %1\CU %1 Setup.rll >NUL 2>NUL</p> <p>ECHO x86...<br>robocopy %1\CU\x86 %1\x86 /XF Microsoft.SQL.Chainer.PackageData.dll >NUL 2>NUL<br>ECHO x64...<br>robocopy %1\CU\x64 %1\x64 /XF Microsoft.SQL.Chainer.PackageData.dll >NUL 2>NUL<br>ECHO IA64...<br>robocopy %1\CU\ia64 %1\ia64 /XF Microsoft.SQL.Chainer.PackageData.dll >NUL 2>NUL</p> <p>:EndCU</p> <p>ECHO Updating DefaultSetup.ini...<br>robocopy . %1\x86 defaultsetup.ini >NUL 2>NUL<br>robocopy . %1\x64 defaultsetup.ini >NUL 2>NUL<br>robocopy . %1\ia64 defaultsetup.ini >NUL 2>NUL<br></p> <p>And that’s it. Just to be complete, here is the complete DefaultSetup.ini I am using:</p> <p>;SQLSERVER2008 Configuration File<br>[SQLSERVER2008]<br>PID="<em><add your product key here></em>"<br>PCUSOURCE="C:\Install\SQL2008R2\PCU"<br>;CUSOURCE="C:\Install\SQL2008R2\CU"<br>SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS<br>ASCOLLATION=SQL_Latin1_General_CP1_CI_AS</p> <p>Please note that CUSource is commented out as there is no CU for SQL 2008R2 SP1 right now.</p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com0tag:blogger.com,1999:blog-7010594866139694603.post-53596402551456160232011-05-24T00:16:00.001-07:002011-05-24T00:16:12.567-07:00SQL Server–Automated Setup<p>I know that there are many articles in Microsofts knowledge base about how to do automatic installation of SQL Server, but as I get pounded with this question time and again I figured it might be a worthy topic to start a blog. So here it is… Automatic SQL Setup, reduced to the ultimately necessary steps: (Actually it’s only one step.)</p> <p>SETUP </p> <blockquote> <p>/SQLSvcAccount=<SQL Server User> </p> <p>/SQLSvcPassword=<SQL User password> </p> <p>/AGTSvcAccount=<SQL Agent User> </p> <p>/AGTSvcPassword=<SQL Agent password> </p> <p>/INSTANCENAME=MSSQLServer</p> <p>/ConfigurationFile=AutoConfig.ini </p> <p>/IAcceptSQLServerLicenseTerms </p> <p>/SQLSysAdminAccounts=”%USERDOMAIN%\%USERNAME%”</p></blockquote> <p>Not that complicated now, is it?</p> <p>OK, maybe I need to clarify some things here:</p> <ol> <li>The Parameter INSTANCENAME obviously holds the name of a named instance. If you specify MSSQLServer setup will install the default instance.</li> <li>What the hell is AutoConfig.ini and how do you create it? Well, you can of course follow the pages of books online and create one yourself, but I would not recommend it, as there is a far easier method: On a clean machine run through the setup wizard with the configuration you like to have. After Setup has finished you will find a ConfigurationFile.ini in the C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\<Timestamp> directory. All you have to do from there is change either “Quiet” or “QuietSimple” to true and you are done. </li> <li>Just to clarify: “Quiet” means that setup will run silent, without any UI. “QuietSimple” means that no questions are asked, but the progress of Setup is still displayed in the setup wizard style. </li></ol> <p>Now… This is how you do Setup automatically… So, if you can do this now, wouldn’t it be nice if setup would allow you to also install the latest Service Pack and Cumulative Update pack in the same run? Well, you can do that too, but that’s another story. (One that I will for sure tell in a short while.)</p> Rickhttp://www.blogger.com/profile/03863747925691630849noreply@blogger.com0