3

We had a performance issue that we solved by restarting the sql server service. That's obviously not the way we want to continue handling our performance issues.

When a sql server performs better after restart the probable causes to me are

  1. wrong cached query plans
  2. memory pressure

To rule out wrong query plans being cached query plans we first dropped the query cache with

DBCC FREEPROCCACHE;

but the problem persisted.

I am still not sure about memory pressure. Page life expectancy goes down during the bad performance period to 50s. But cache hit ratio is 100%.

Are the numbers suggesting memory pressure here or are there other possible reasons?

I put a beautiful red line where we restarted the sql server service.

At the time of the issue we did not have more requests than usual.

enter image description here

enter image description here enter image description here enter image description here

2
  • 1
    IMO this question is better suited for Database Administrators. Feb 6, 2019 at 9:51
  • 1
    Just a comment that cache hit ratio is basically a useless perf counter since it doesn't include read-ahead (physical) reads. I.e., you can have loads and loads of physical reads and cache hit ratios can still show a 100% hit rate! Feb 6, 2019 at 11:11

1 Answer 1

5

If you want to know why it's better after you restart, you need to track what was running when all those lines got squiggly.

When you clear the plan cache, it doesn't help queries currently executing, and it doesn't guarantee that the next plan you cache will be good. I'd advise against doing that until you know exactly what's going on.

Take a look at the part of your monitoring tool that tells you which queries were running when you had a problem. If your monitoring tool doesn't tell you that, it's time to find a new one.

Next time it's happening, use a script like sp_WhoIsActive to catch currently executing queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.