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.)

[SqlFunction]
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
            Result++;
        Input >>= 1; // Now shift the input so that the next bit is lowest
    }
    return (Int64) Result;
}

47 comments:

  1. Excellent blog post Rick, you beat me to it. I have just posted a blog entry that adds more data to support your test.

    You should see a pingback from my blog very soon

    ReplyDelete
    Replies
    1. Hello, I want to write a little review about the good work of doctor ehiaguna, who cured me from HSV 1&2 for just 2 week with his herbal medicine, I never believe I can be cured again and have a good life like others, I always regretted the day I got diagnose with this virus, I become lost of hope when my doctor told me there is no cure for it, Well I am so grateful for my helper who get me cured with his herbal medicine, I went online in search of anything that can help me because I can’t deal with it forever so I found dr ehiaguna email drehiaguna@gmail.com on a Facebook of someone who was cured by him,of the same herpes, so I quickly contacted him for help and explained all my pains to him, he told me not to worry about it there is a cure for real, I never believe until he sent me the herbs when I ordered for it and I got it within 5 working days that is how I took the medicine for 2 weeks and the following week I went for a test just to confirm I was 100% cured from this sickness what a miracle in my life, I am so happy right now, and forever greatful, you can also get in contact with him if you have such sickness through his whatsapp +2348073908953 you can  email him:: drehiaguna@gmail.com visit his website.  https://effectiveherbalcurehome.blogspot.com/  God bless you

      Delete
    2. Please share for others to know about the cure for herpes. I'm here to testify on how i was cured from HERPES 1&2 with the use of herbs. I got the herbs from Dr Osato and he gave me instructions on how to use them. I took the herbal product for a week and 5days after which I went for a blood test and the result was Negative. I waited for another two months and retested the result was still negative and my doctor told me I'm completely free from herpes1&2 and this Gladys my heart and i wish to share it for the benefit of those living with herpes1&2 to also get the herbs from Dr Osato and get cured just like me. You can contact Dr Osato on his email: osatoherbalcure@gmail.com or call him on his phone number and WhatsApp +2347051705853 also through his website https://osatoherbalcure.wordpress.com. Dr Osato also have the cure on the following diseases HERPES, HIV, HPV, CANCER, DIABETES, SHINGLES,VAGINAL INFECTION, HSV1&2 and so many more.

      Delete
    3. Dr Osato herbal medicine cures herpes within two weeks. I just tested negative for Genital herpes after using Dr Osato herbal meds for two weeks! To place an order and get yours today visit his website osatoherbalcure.wordpress.com or email him at OSATOHERBALCURE@GMAIL.COM or WhatsApp +2347051705853.

      Delete
  2. Actually Rick... How about this way to generate the sequence (if we assume you are a BIGINT):

    @@SPID * 10000000000 + [Next Sequence Number]

    ReplyDelete
  3. Nice idea Thomas, but I see two drawbacks:
    1) You loose IDs (as you can't predict a SPID), and this could also lead to duplicates if you are not extra carefull.
    2) SPIDs tend to group together, especially if your app uses connection pooling...

    ReplyDelete
  4. RSince you are adding a sequence and a large offset, it will not result in duplicates. The sequence is already unique, and offsetting it by 10B will only make it "non unique" if you have more than 10B inserts per SPID (unlikely?)

    The grouping together is exactly the idea of the SPID trick. To avoid page latch, you have to "split the key space". Bit flipping achieves this - but so does the SPID splitting. For SPID splitting, each 10B range will have its own little "subspace" that where inserts are linear (which is OK; because PAGELATCH only happens at concurrency, which you wont have per SPID, only per server).

    The SPID grouping should give you less fragmentation, but more importantly, less page splits (but it does require the assumption that you can "pre-partition the space" - your bitflip doesnt require that.

    ReplyDelete
  5. 10B inserts are not that unlikely... Two years ago someone told me that we would never need more than int16... now we are at int64 already...

    The point behind grouping is that if I have two SPIDs reused (e.g. 52 and 53) by two heavy load servers I get a grouping around those two spids, while every other "subspace" might be less congested.
    You are still right that this will give you good segmentation at less CPU cost, but when you need the best possible segmentation I still think I am a little ahead ;-)

    ReplyDelete
  6. Good point on the SPID grouping consuming part of the "key space" faster than others.

    Another options to consume the space evenly would be to use scheduler_id (since that is guaranteed to be approximately equal)

    Would be interesting to see how these methods measure up against each other on CPU and page splitting

    ReplyDelete
  7. Instead if looping, how does this attempts stand up? I know it's only 32-bit.

    unsigned int v; // 32-bit word to reverse bit order

    // swap odd and even bits
    v = ((v >> 1) & 0x55555555) | ((v & 0x55555555) << 1);
    // swap consecutive pairs
    v = ((v >> 2) & 0x33333333) | ((v & 0x33333333) << 2);
    // swap nibbles ...
    v = ((v >> 4) & 0x0F0F0F0F) | ((v & 0x0F0F0F0F) << 4);
    // swap bytes
    v = ((v >> 8) & 0x00FF00FF) | ((v & 0x00FF00FF) << 8);
    // swap 2-byte long pairs
    v = ( v >> 16 ) | ( v << 16);

    ReplyDelete
  8. Thanks for that input Unknown... Took me a while to wrap my head around it, but it is a really cool one. Recoded in Assembler it takes a little over 100 CPU cycles, which is another improvement over what I had. And it works with 64bit just as it does with 32...
    I think I will stresstest that and post it, if it should stand up.

    ReplyDelete
  9. Thank you sir. Can you please send me the complete code (CLR) for key generation using bit reversal.

    ReplyDelete

  10. it’s ok to show some appreciation and say ‘great post’
    Asp .NET developer

    ReplyDelete
  11. Really good information to show through this blog. I really appreciate you for all the valuable information that you are providing us through your blog. SQL server dba Online Course

    ReplyDelete
  12. I GOT CURED FROM HERPES VIRUS I was diagnosed of HERPES virus and i have tried all I can to get cured but all to no avail,,until I saw a post in a health forum about a herbalist man who prepare herbal medications to cure all kind of diseases including #HERPES virus,at first I doubted if it was real but decided to give it a try,when I contacted this herbalist via his email and he prepared a #HERPES herbal cure and sent it to me via DHL delivery company service, when I received this herbal medicine, he gave me step by direction on how to apply it,after applying the way I was instructed,I was totally cured of this deadly disease called HERPES, all thanks to Dr OGODO Email this great herbal doctor via his email Ogodoherbalhomesolution@gmail.com
    You can also WhatsApp him on +2349044680467
    HE CAN ALSO CURE SICKNESS LIKE
    {1}HIV/AIDS
    {2}DIABETES
    {3}EPILEPSY
    {4} BLOOD CANCER
    {5} HPV
    {6} ALS
    {7} HEPATITIS
    {8}LOVE SPELL
    {9} SICKLE AND ANEMIA
    {10}PENIS ENLARGEMENT
    Only DR OGODO Can Help You I Love You Father.
    You can check his website on his Facebook page.
    https://www.facebook.com/oogoherbalhomesolution/

    ReplyDelete
    Replies
    1. Hello, I want to write a little review about the good work of doctor ehiaguna, who cured me from HSV 1&2 for just 2 week with his herbal medicine, I never believe I can be cured again and have a good life like others, I always regretted the day I got diagnose with this virus, I become lost of hope when my doctor told me there is no cure for it, Well I am so grateful for my helper who get me cured with his herbal medicine, I went online in search of anything that can help me because I can’t deal with it forever so I found dr ehiaguna email drehiaguna@gmail.com on a Facebook of someone who was cured by him,of the same herpes, so I quickly contacted him for help and explained all my pains to him, he told me not to worry about it there is a cure for real, I never believe until he sent me the herbs when I ordered for it and I got it within 5 working days that is how I took the medicine for 2 weeks and the following week I went for a test just to confirm I was 100% cured from this sickness what a miracle in my life, I am so happy right now, and forever greatful, you can also get in contact with him if you have such sickness through his whatsapp +2348073908953 you can  email him:: drehiaguna@gmail.com visit his website.  https://effectiveherbalcurehome.blogspot.com/  God bless you

      Delete
  13. My health was horrible before I decided to try the Protocol Of taking Dr. Omola pure herbal mixture. I felt there was no hope for my health and I was to try the Protocol, thinking it wouldn’t work because I have visited so many hospital but same result. However, I was convinced by a Instagram friend to try the herbal medicine because I wanted to get rid of Herpes virus. The herbal mixture that was given to me was really quick and easy to take, within 2 week I was fully cured from Herpes. The herbal medicine really work and I will like to share this great doctor contact with you all email him dr.omolaherbalhome@gmail.com or WhatsApp +2348118116254 you won't regret it, I promise.

    ReplyDelete
  14. Thanks to Dr Aire for curing me of HPV2, Indeed you are great, your herbal medicine its real and 100% satisfactory, am glad to have patronized you. Once again thanks to you for the good work you have done in my life. i will advised anyone out here living with herpes should please contact him to get the herbal cure for herpes its 100% guarantee and work, with no side effects. I recommend his service for you kindly contact him to get your cure as well. On his email: drairehome@gmail.com Whatsapp: +2347036740271 

    ReplyDelete
  15. Please share for others to know about the cure for herpes. I'm here to testify on how i was cured from HERPES 1&2 with the use of herbs. I got the herbs from Dr Osato and he gave me instructions on how to use them. I took the herbal product for a week and 5days after which I went for a blood test and the result was Negative. I waited for another two months and retested the result was still negative and my doctor told me I'm completely free from herpes1&2 and this Gladys my heart and i wish to share it for the benefit of those living with herpes1&2 to also get the herbs from Dr Osato and get cured just like me. You can contact Dr Osato on his email: osatoherbalcure@gmail.com or call him on his phone number and WhatsApp +2347051705853 also through his website https://osatoherbalcure.wordpress.com. Dr Osato also have the cure on the following diseases HERPES, HIV, HPV, CANCER, DIABETES, SHINGLES,VAGINAL INFECTION, HSV1&2 and so many more.

    ReplyDelete
  16. People actually believe there is no cure for herpes due to what our medical Doctor's do always say to us..but now i know that there is a cure for it Through herbal remedy' i and my boyfriend was once haven herpes type 1&2 i contacted DR.ODUDU for herbal treatment and he prepared us some herbs which we takes for some couples of days, and we take it as he says before i am to known' i and my boyfriend was perfectly cure for it and never experience the Outbreaks again. please friends if you have herpes virus and you really want to get rid of it permanently, Kindly contact this great DR.ODUDU for herbal treatment and you will get rid of it.. truly truly home remedies works perfectly. EMAIL dr.oduduherbalhome@gmail.com cell phone contact +2348101571054 his whatsapp contact is +2348101571054 https://www.facebook.com/DR.ODUDU

    ReplyDelete
  17. Hey plz share this I am here to testify how a herbal helped me out with herbs and roots which he prepared for me in use of curing Herpes.natural remedy work perfectly I takes the product which he prepared for me for some few days before I am to know I was totally cured out of it. Please you need to reach him in any similar illness, his product is helpful and it heals perfectly...his info drdestinyspellhome@gmail.com his calling WhatSapp number +2348105074556 Communication with facebook page: https://web.facebook.com/Doctor-Destiny-106008014979860

    ReplyDelete
  18. Dr Osato herbal medicine cures herpes within two weeks. I just tested negative for Genital herpes after using Dr Osato herbal meds for two weeks! To place an order and get yours today visit his website osatoherbalcure.wordpress.com or email him at OSATOHERBALCURE@GMAIL.COM or WhatsApp +2347051705853.

    ReplyDelete
  19. Good day everyone, it's hard to believe that herbal medicine can cure herpes, I never believe that this will work, I have to spend a lot of money getting drugs from the hospital to keep me and my son healthy, it got to a point that I was waiting for death to come because I was broke, one day I hard about this great man called Dr. Abolo who is well know for Herpes, HIV, and cancer cure, I decided to email him I didn't believe him that much, I just wanted to give him a try, he replied to my mail and Needed some Information about me, then I sent them to him, he prepared herbal medicine and he gave my details to the Courier Office. they told me that in 4-6 days I will receive the package and after receiving it, I took the medicine as prescribed by him at the end of two weeks, he told me to go to the hospital for a check-up, and I went, surprisingly after the test the doctor confirm me Herpes simplex virus-negative, and my son and I thought it was a joke, I went to another hospital and was also negative, thank you for saving our life's, I promise I will always testify of your good works. if you are a herpes simplex virus patient, contact him and I am sure you will get cured, contact him on his Email@ draboloherbalhome@gmail.com or contact him on his whatSApp number +234744725611
    THESE ARE THE THINGS Dr. WILLIAMS CAN CURE
    . HERPES
    . HIV/AIDS
    . CANCER
    . DIABETES
    . LASSA FEVER
    . GONORRHEA
    . MENOPAUSE DISEASE
    . EPILEPSY

    ReplyDelete
  20. I am here to give my testimony
    about Dr babaiba who helped me.. i want to inform the public how i was cured from (HERPES SIMPLEX VIRUS) by salami, i visited different hospital but they gave me list of drugs like Famvir, Zovirax, and Valtrex which is very expensive to treat the symptoms and never cured me. I was browsing through the Internet searching for remedy on HERPES and i saw comment of people talking about how Dr babaiba cured them. when i contacted him he gave me hope and send a Herbal medicine to me that i took for just 2 weeks and it seriously worked for me, my HERPES result came out negative. I am so happy as i am sharing this testimony. My advice to you all who thinks that there is no cure for herpes that is Not true just contact him and get cure from Dr babaiba healing herbal cure of all kinds of sickness you may have like
    #CANCER,
    #DIABETES,
    #HIV
    #AIDS,
    #Herpes
    #Std
    #URINARYTRACT #INFECTION,
    #CANCER,
    #IMPOTENCE,
    #BARENESS
    #INFERTILITY
    #DIARRHEA
    #ASTHMA
    #SIMPLEXHERPES #GENITAL
    #COLDSORE
    . he also cure my friend from cervical #cancer Email: (drbabaibaherbalsolutiontemple@gmail.com) or call or WHATAPP +2348140046509 I am forever greaful ...

    ReplyDelete
  21. I have been suffering from a deadly disease (Hsv) for the past 2 years now, I had spent a lot of money going from one places to another, from churches to churches, hospitals have been my home every day residence. Constant checks up have been my hobby not until this faithful day, I was searching through the internet, I saw a testimony on how Dr Oyagu helped someone in curing his (Hsv) herpes disease, quickly I copied his email which is oyaguherbalhome@gmail.com just to give him a test I spoke to him, he asked me to do some certain things which I did, he told me that he is going to provide the herbal cure to me, which he did, then he asked me to go for medical checkup after some days after using the herbal cure, behold I was free from the deadly disease, he only asked me to post the testimony through the whole world, faithfully am doing it now, please brothers and sisters, he is great, I owe him in return. if you are having a similar problem just email him on oyaguherbalhome@gmail.com visit his website https://oyaguspellcaster.wixsite.com/oyaguherbalhome Or whatsapp him via +2348101755322

    ReplyDelete
  22. I already gave up on ever getting cured of HSV2 because I have try many treatment none of them work out for me I have be to different hospital they always tell me same thing there is no cure for herpes when i came across a post about Dr momoh, in the net from a lady called Rebecca I contacted him and he reassured me with his herbal medicine which I took according to the way he instructed, that how i was cured. I doubted at first because I have been to a whole lot of reputable doctors, tried a lot of medicines but none was able to cure me. so I decided to listen to him and he commenced treatment, and under two weeks I was totally free from Herpes. I want to say a very big thank you to DR momoh for what he has done in my life. feel free to leave him a message on email dr.momoh9@gmail.com and also WhatsApp him +2347083724098.. or visit his website https://drmomohherbaltreatment.wordpress.com. He can still be able to help you with this herbs medicine: 1...ALS CURE/DIABETES CURE/EPILESY/HPV CURE/LUPUS/HEPATITIS/CANCER/GOUT

    ReplyDelete
  23. I was diagnosed of herpes 3 years, and ever since then i have been taking treatment to prevent outbreaks, burning and blisters, but there was no improvement until i came across testimonies of Dr. VOODOO on how he has been curing different people from different diseases all over the world, then i contacted him. After our conversation he sent me the medicine which i took according to his instructions for up to 2 weeks. After completing the medication i went back to my doctor for another test and the virus was all gone and i was completely cured, since then i have not had any signs of outbreak. I'm so filled with joy. With herbal medication Herpes Virus is 100% curable. I refer Dr VOODOO to everyone out there with the virus Email: voodoospelltemple66@gmail.com Add Dr voodoo on whatsApp +2348140120719

    ReplyDelete
  24. I got Diagnosed with Herpes, I have be dealing with Herpes for the past 4 years. On till I got review online about natural cure people testifies how DR. Ehimare cure them with his herbal treatment. And I order the treatment, after taking it for 21 days I totally got cured with DR. Ehimare treatment. I'm recommending you diagnosis with herpes should get this treatment and be cured of it you can contact his email address via; drehimare3@gmail. com or you can also call or whatsapp +1 (267) 691-1087Dr. Ehimare also cure the following virus and many more... 1} Cancer 2} Diabetes 3} Hepatitis 4} fibroid 5} herpes 6} Lupus

    ReplyDelete
  25. Hello! I'm very excited to inform everyone that I'm completely cured from my HSV 1&2 recently. I have used Oregano oil, Coconut oil, Acyclovir, Valacyclovir, Famciclovir, and some other products and it's really helped during my outbreaks. but I totally got cured from my HSV with a strong and active herbal medicine ordered from a powerful Traditional Healer and it completely fought the virus from my nervous system and I was tested negative after 21 days of using the herbal medicine. I'm here to let you all know that herpes virus has a complete cure, I got rid of mine with the help of DR ODUDU a traditional Healer from west African and his herbal exploit. Contact him via email: dr.oduduherbalhome@gmail.com and his whatsapp and calling number is  +2348101571054 facebook page https://www.facebook.com/DR.ODUDU

    ReplyDelete
  26. People actually believe there is no cure for herpes due to what our medical Doctor's do always say to us..but now i know that there is a cure for it Through herbal remedy' i and my boyfriend was once haven herpes type 1&2 i contacted DR.ODUDU for herbal treatment and he prepared us some herbs which we takes for some couples of days, and we take it as he says before i am to known' i and my boyfriend was perfectly cure for it and never experience the Outbreaks again. please friends if you have herpes virus and you really want to get rid of it permanently, Kindly contact this great DR.ODUDU for herbal treatment and you will get rid of it.. truly truly home remedies works perfectly. EMAIL dr.oduduherbalhome@gmail.com cell phone contact +2348101571054 his whatsapp contact is +2348101571054 https://www.facebook.com/DR.ODUDU

    ReplyDelete