In-memory database

  • LLSQL42

    SSC Enthusiast

    Points: 183

    Hi,

    Currently i am processing records in SQL Server itself.

    Any recommended in-memory database, which is i can copy "defined tables & records" from main-SQL Server,

    to the in-memory database, so i can perform my process there? After done, i will insert the final records into SQL Server.

     

    Thus can reduce the load in SQL Server

  • This was removed by the editor as SPAM

  • LLSQL42

    SSC Enthusiast

    Points: 183

    I did find something below:-

     

    "Converting a Database to In-Memory OLTP"

    Can someone experience on below may guide:-

     

    1. Any third party isolated In-Memory tool for Window?

    2. Once choosen the In-Memory, article of how to load existing SQL Server Database (Certain Tables Only) to In-Memory OLTP?

    My intention is, only required to load specific tables and records, stored procedures, functions into In-Memory OLTP, then perform processing in OLTP.

     

    After complicated processing done, i will insert final row record into SQL Server,

    and in last, i will flush the In-Memory OLTP.

     

  • Grant Fritchey

    SSC Guru

    Points: 396384

    First up, look to your wait statistics. If you're not seeing IO_LATCH* waits as your number one wait (and number two, three, four...), chances are extremely high that going to In-Memory tables will not only not help your performance, but could in fact hurt performance.

    What problem are you trying to solve?

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    Ultimately memory optimised tables end up having to be persisted to disk and lord alone I've never tested them with bulk inserts and data manipulation

    Are you maybe talking about using table variables?

    Grant asked a good question - what are you solving? are you struggling with Disk I/O? CPU issues?

    if you are looking for a 3rd party tool, will it reside on different hardware? (really bad idea if you install it on the SQL server)

    If you are staging data ready to put it into Live then SQL is usually your friend, but it's not hard to knock up a c# tool to prep your data, or there is the option of SSIS on a cheap little server and then just "lift and shift" the data to Live

    MVDBA

  • LLSQL42

    SSC Enthusiast

    Points: 183

    Noted, thanks, replied.

    i found a alternative below

    Implementing SQL Server In-Memory OLTP.

    Anyone experience of above and can share practical implementation with sample

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    as I mentioned memory optimised tables need to be treated with care.

    what are you trying to fix? have you got stats that suggest your issues?

    MVDBA

  • Grant Fritchey

    SSC Guru

    Points: 396384

    So, please, I hope, you're testing this in a non-production environment, because, here's a bit of fun trivia, once you mark a database as in-memory, you can never undo that, even if you stop using the objects in any way. This is a rather tough topic and should be approached with caution.

    Please, help us help you. What problem are you trying to solve? What are your top waits? It's possible that the in-memory objects can help, but before we launch down the path of explaining implementations, it's best to be sure this is the right path.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    Grant Fritchey wrote:

    So, please, I hope, you're testing this in a non-production environment, because, here's a bit of fun trivia, once you mark a database as in-memory, you can never undo that, even if you stop using the objects in any way. This is a rather tough topic and should be approached with caution.

    Please, help us help you. What problem are you trying to solve? What are your top waits? It's possible that the in-memory objects can help, but before we launch down the path of explaining implementations, it's best to be sure this is the right path.

    +1

    I've been removing mem optimised tables from our databases for the last 12 months. they were put there because the sql was missing indexes and the developers went straight for "in memory" - I still can't drop the filegroup

    back in SQL2000 days we had DBCC Pintable which did the same thing (but in my opinion better)- there was a reason Microsoft dropped it, people were overusing it because of poor coding or lack of good DB design.

    MVDBA

  • LLSQL42

    SSC Enthusiast

    Points: 183

    Which in memory database server open source

    can recommend?

     

    Which is can connect to sql server and sync data to it?

    TARGIT IN-MEMORY is not open source,

    any similiar to it?

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    so you want open source - which means "free", beware of the licence- you may have to disclose your code which may be not in the interests of your company

    as grant suggested - what are you trying to fix? do the diagnostics before you try to fix

    MVDBA

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    I'm doing to be honest here, it looks like you are looking for a solution before you know what the problem actually is... we can't help you without details

    MVDBA

  • Grant Fritchey

    SSC Guru

    Points: 396384

    Here, rather than try to really help, I just googled "open source in memory database". Here you go.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 13 posts - 1 through 13 (of 13 total)

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