Memory-optimized table indexes in SQL 2014

  • Dr. Diana Dee

    SSCarpal Tunnel

    Points: 4232

    Comments posted to this topic are about the item Memory-optimized table indexes in SQL 2014

  • Ron McCullough

    SSC Guru

    Points: 63877

    Nice question - learned something new today Thanls

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Dr. Diana Dee

    SSCarpal Tunnel

    Points: 4232

    Ron - I am happy that you learned something.

    Diana

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Nice question, thanks!

    Regards,

    IgorMi

    Igor Micev,
    My blog: www.igormicev.com

  • steve.jacobs

    SSCommitted

    Points: 1830

    I am really looking forward to memory optimized tables and indexes. I have been waiting for this for what seems like forever. 😉 Thanks for the question.

    Steve

  • Dr. Diana Dee

    SSCarpal Tunnel

    Points: 4232

    IgorMi and Steve - You are welcome!

    Diana

  • TomThomson

    SSC Guru

    Points: 104773

    Nice question. I found that thinking about it led me into all sorts of side tracks (which didn't stop after I'd reached an answer) some of which leave me quite confused and looking for more documentation - which is exactly the efect a question should have to maximise its boost to my learning about new things.

    I wonder whether the structures used will introduce performance issues when row length is significantly shorter than the hardware cache line length - and if it will, does that matter. Obviously it doesn't matter much if scans in a particular order are rare and accesses to small clusters of adjacent records are also rare, and both of those conditions tend to be true in (most??) OLTP applications, and besides that it may be that cache line length is most often short enough to eliminate the issue, but was that part of the design philosophy here? The remark somewhere in the documentation that all indexes can be considered as covering seems to indicate that it was, but I don't recall seeing anything to that effect in early documentation on 2014. Actually I don't know what typical cache line lengths are on the processors windows runs on these days so it may be a complete non-issue.

    Tom

  • TomThomson

    SSC Guru

    Points: 104773

    Nice question. I found that thinking about it led me into all sorts of side tracks (which didn't stop after I'd reached an answer) some of which leave me quite confused and looking for more documentation - which is exactly the efect a question should have to maximise its boost to my learning about new things.

    I wonder whether the structures used will introduce performance issues when row length is significantly shorter than the hardware cache line length - and if it will, does that matter. Obviously it doesn't matter much if scans in a particular order are rare and accesses to small clusters of adjacent records are also rare, and both of those conditions tend to be true in (most??) OLTP applications, and besides that it may be that cache line length is most often short enough to eliminate the issue, but was that part of the design philosophy here? The remark somewhere in the documentation that all indexes can be considered as covering seems to indicate that it was, but I don't recall seeing anything to that effect in early documentation on 2014. Actually I don't know what typical cache line lengths are on the processors windows runs on these days so it may be a complete non-issue.

    Tom

  • Hany Helmy

    SSChampion

    Points: 13488

    Definitely learnt something new today, thanks Diana.

    Hany

  • Dr. Diana Dee

    SSCarpal Tunnel

    Points: 4232

    Tom - What is the "hardware cache line length"? Do you know of a reference I can read about that?

    Thanks

    Diana

  • Dana Medley

    SSCertifiable

    Points: 6764

    Thanks for the great question. I learned something today.



    Everything is awesome!

  • Thomas Abraham

    SSChampion

    Points: 10761

    Thanks for the question. Interesting stuff, even if it will be 2020 before I get to use 2014 in a production environment.

    I note that under "Index Count" it says:

    Do not create an index that you rarely use

    I would think that would be obvious. But, ...

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Ed Wagner

    SSC Guru

    Points: 286979

    Good question. They look like they have promise for limited applications, but it'll be quite a while until I get to use them, too.

  • Revenant

    SSC-Forever

    Points: 42467

    Sent me scrambling for BOL... Thanks, Diana!

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    It took me a lot of time to find the correct answer for this one. I knew the two index types - but under different names ("hash" and "range"). Most seearches sent me to pages that used those same terms. Just as I was almost ready to give up, pick two, and see what the intended answer was, I stumbled upon the same page referenced in the explanation.

    I'm already looking forward to seeing the final names of these features (and the number of name changes yet to come until release) 😉


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply