TempDB growth due to version store on AlwaysOn secondary server

  • ramya.sqlservr

    Ten Centuries

    Points: 1021

    Comments posted to this topic are about the item TempDB growth due to version store on AlwaysOn secondary server

  • David.Poole

    SSC Guru

    Points: 75199

    Great article, well written. I'd hope for something this clear if I was on call.

    Any idea what caused spid 484 to hang around?

  • tcronin 95651

    SSCertifiable

    Points: 6735

    I believe at one of the Microsoft sessions here in St. Louis it was also mentioned that statistics for querying the RO node also was stored in tempdb

  • Jeff Moden

    SSC Guru

    Points: 995114

    David.Poole (11/7/2016)


    Great article, well written. I'd hope for something this clear if I was on call.

    Any idea what caused spid 484 to hang around?

    That was going to be my question, as well. It would seem that, whatever that was, was the cause of this problem. That means the root cause of this problem is knowing what SPID 484 was doing, what caused it to hang, and how to prevent it from happening again.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Robin35

    SSCertifiable

    Points: 6471

    I'm having the same issue but couldn't find the exact spid that caused the tempdb growth. i killed the sleeping sessions.

    Also, we used to get corrupted tempdb stats on read only databases in always on group. every time we get that issue, we have to drop the corrupted stats and query optimizer will re-create them again. We reached out to Microsoft support and they are working on a bug fix.

    But for version store space growth in tempdb , i'm not sure if that was because of temporary stats ? Also, there is only one database that has RCSI enabled out of 15 other other databases that are part of always on. I have just enabled RCSI on 3TB database yesterday because of blocking issues. not sure how this is going to behave, although we have more than a TB space on tempdb but still after looking at the version store space yesterday (growth was 750GB), i'm kind of concerned. 

    Any help on this is greatly appreciated.

    thanks

  • ramya.sqlservr

    Ten Centuries

    Points: 1021

    Robin35 - Saturday, February 18, 2017 12:15 PM

    I'm having the same issue but couldn't find the exact spid that caused the tempdb growth. i killed the sleeping sessions.

    Also, we used to get corrupted tempdb stats on read only databases in always on group. every time we get that issue, we have to drop the corrupted stats and query optimizer will re-create them again. We reached out to Microsoft support and they are working on a bug fix.

    But for version store space growth in tempdb , i'm not sure if that was because of temporary stats ? Also, there is only one database that has RCSI enabled out of 15 other other databases that are part of always on. I have just enabled RCSI on 3TB database yesterday because of blocking issues. not sure how this is going to behave, although we have more than a TB space on tempdb but still after looking at the version store space yesterday (growth was 750GB), i'm kind of concerned. 

    Any help on this is greatly appreciated.

    thanks

    Hi Robin,

    which version of SQL Server are you using ? I have not seen version store growing huge because of temporary readonly stats in our servers so far. Have you checked the longest transaction running time - performance counter ? Is it showing a bigger value by any chance ?

    Thanks,
    Ramya

  • ramya.sqlservr

    Ten Centuries

    Points: 1021

    ramya.sqlservr - Saturday, February 18, 2017 11:51 PM

    Robin35 - Saturday, February 18, 2017 12:15 PM

    I'm having the same issue but couldn't find the exact spid that caused the tempdb growth. i killed the sleeping sessions.

    Also, we used to get corrupted tempdb stats on read only databases in always on group. every time we get that issue, we have to drop the corrupted stats and query optimizer will re-create them again. We reached out to Microsoft support and they are working on a bug fix.

    But for version store space growth in tempdb , i'm not sure if that was because of temporary stats ? Also, there is only one database that has RCSI enabled out of 15 other other databases that are part of always on. I have just enabled RCSI on 3TB database yesterday because of blocking issues. not sure how this is going to behave, although we have more than a TB space on tempdb but still after looking at the version store space yesterday (growth was 750GB), i'm kind of concerned. 

    Any help on this is greatly appreciated.

    thanks

    Hi Robin,

    which version of SQL Server are you using ? I have not seen version store growing huge because of temporary readonly stats in our servers so far. Have you checked the longest transaction running time - performance counter ? Is it showing a bigger value by any chance ?

    Thanks,
    Ramya

    Also, below query can help find the current active transactions that are using the version store currently.

    select CONVERT (varchar(30), getdate(), 121) AS runtime,a.*,b.kpid,b.blocked,b.lastwaittype,b.waitresource,b.dbid,b.cpu,b.physical_io,b.memusage,b.login_time,b.last_batch,b.open_tran,b.status,b.hostname,b.program_name,b.cmd,b.loginame,request_id
    from sys.dm_tran_active_snapshot_database_transactions a
    inner join sys.sysprocesses b
    on a.session_id = b.spid

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

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