Post reply

Memory and performance issues

  • October 21, 2014 at 1:10 pm

    #291843

    Hi,

    yesterday our production server started acting quite strange.

    PLE dropped from hours to less than ten minutes. CPU utilization is constantly around 60% and tempdb is using more than 50% memory buffer pool. At the same time tempdb has grown from 1.6 Gb to 9 Gb. Tempdb has 97% free space all the time.

    Does anyone have where to look at why this happens? I cannot restart SQL server instance until Friday morning.

  • October 22, 2014 at 2:32 am

    #1753955

    What monitoring did you have running at the time it happened?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 22, 2014 at 3:06 am

    #1753964

    We are using Proactive DBA timeline for monitoring.

    Unfortunately I have no records for last week since I was on vacation and our support team made a version release and restarted sql server during it. I have some older ones and I am comparing to those.

  • October 22, 2014 at 3:10 am

    #1753965

    Ok, so what metrics/stats/info do you have for the period in question?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 22, 2014 at 4:40 am

    #1753988

    GilaMonster (10/22/2014)

    Ok, so what metrics/stats/info do you have for the period in question?

    I have nothing collected. I have been relying on Proactive DBA and it was shut down during the period.

    I can only see metrics in august and now.

    I think that this is related to spilling to tempdb. In august tempdb IO is around 10-30 ms and at the worse time it was around 3 s.

    At the moment IO is around 1.5 s.

    I added one missing index and changed one view to use UNION ALL instead of UNION.

  • October 22, 2014 at 4:50 am

    #1753991

    If you've got no stats for the problematic period, you're going to have a very hard time figuring anything out.

    First place I would have looked, with monitoring, would have been to see what was running against the DB that wasn't normally running, or was running with non-standard performance statistics

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

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