Why does sql server perform better after restart

Question

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

• Gerald Schneider: IMO this question is better suited for Database Administrators.
• Tibor Karaszi: 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!

Answer 1

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.