none
Version Store and memory RRS feed

  • Question

  • I am stuck with a software which keeps transactions open on purpose!!!  As a result, my version store fills up to astronomical proportions.

    To quantify the situation, the transaction may stay open for weeks (until we stop the service of the application to cleanly stop the transaction) and the version store can reach over 100GB which is unsustainable.

    Versions store being in tempdb, is there a way to know if and how much of the memory is occupied by the version store. (quantify the impact)  i.e. if in addition to the knowledge that I am losing precious space on my LUN, can I quantify my waste of memory due to this ridiculous situation?

    • Edited by Antoine F Friday, June 5, 2015 3:21 PM
    Friday, June 5, 2015 3:06 PM

All replies

  • How long are transactions held open?

    How large is your version store growing?

    select version_store_reserved_page_count * 8 version_store_kb
    FROM tempdb.sys.dm_db_file_space_usage

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, June 5, 2015 3:24 PM
  • right now: 111802MB (3 times 38162176KB).

    the app never closes the transaction.  I believe it is their genius idea to manage concurency by willingly generating a lock.

    Friday, June 5, 2015 3:38 PM
  • Yeah,  they'll need to fix that.  Basically you need to avoid having a very-long-running transaction that prevents version store cleanup.  But not every transaction prevents version store cleanup.

    What isolation level is the long-running transaction in?

    Did the long-running transaction create any row versions?

    Do you have more details on what the app is trying to do and how it's currently implemented?  There may be a reasonable workaround for them.

    David


    David http://blogs.msdn.com/b/dbrowne/




    Friday, June 5, 2015 5:03 PM
  • the app never closes the transaction. 

    I believe it is their genius idea to manage concurency by willingly generating a lock.

    I'd pay a dollar to hear more about this crazy scheme!

    What version of SQL Server are you on?  I'll bet they think this is a roll-your-own Hekaton!  Which it is not, for a dozen reasons.  I'll bet none of the app logic even works right.  Is this in production?

    Josh

    Friday, June 5, 2015 5:18 PM
  • There are two servers, where we are using always on.  On the secondary replica, always on enforces snapshot isolation.  So we are in trouble there.  It is part of the specifications of always on.  I remove the database from alwayson which kills the transaction on the secondary and frees the space.

    On the primary however, master uses snapshot isolation. 

    My understanding is that

    1. connections made through master (default database) end up being is snapshot isolation and doing row versionning indempendently of queried database.
    2. No matter isolation level the transaction was generated from.  Version store is kept for as long as the oldest transaction present on the instance.  So even if the transaction is not in snapshot isolation, it would cause the row versionning to accumulate.

    What I am unsure of however is wether master is in snapshot isolation as part of the always on requirement.

    I do not have details on the app except for the fact that the provider tells us to not kill the transaction except by following a procedure which involves stopping the web services/interrupting IIS.  I am being told it is to prevent simultaneous access to the app.  So I believe they use the transaction as a mean create and hold a lock.

    This single application is causing trouble for the whole SQL instance. My solution would be to remove it from this server or not to use it but I would need solid arguments.

    This is why I would like is to quantify the impact, apply pressure.  This is why I am looking at wether the row versionning usage of memory/impact can be quantified.

    Friday, June 5, 2015 5:34 PM
  • "connections made through master (default database) end up being is snapshot isolation and doing row versionning indempendently of queried database."

    I don't believe this is the case. 

    Is the version store growing on the primary or the secondary?  Is the secondary readable?

    >Version store is kept for as long as the oldest transaction present on the instance.

    Not always.  If the transaction is in SNAPSHOT then it may need row versions from when the transaction started.  But in READ COMMITTED SNAPSHOT isolation you wouldn't need the older row versions.  And other isolation levels never touch the version store.

    But certainly if the very-long-running transaction is in SNAPSHOT isolation, then row versions from any database with ALLOW_SNAPSHOT_ISOLATION cannot be cleaned up. 

    >My solution would be to remove it from this server

    Apps like that may need to be hosted in a dedicated instance.  You can install a small secondary instance on the servers dedicated for that app.   It would have it's own memory, own TempDb and could only hurt itself.

    David


    David http://blogs.msdn.com/b/dbrowne/



    Friday, June 5, 2015 6:08 PM
  • This is why I would like is to quantify the impact, apply pressure.  This is why I am looking at wether the row versionning usage of memory/impact can be quantified.

    I think you've done that!

    But it's still a crazy architecture.

    Is it possible that you are either their very first customer, or are by far their largest installation? 

    Does the software have its own user group where you can ask about similar situations?

    I've been in both those situations, we were using one package on about 100x the data it was ever designed to handle and we were able to get by, but it took some doing.

    Josh

    Friday, June 5, 2015 10:19 PM