0

We have reports of performance issues on one of our applications. I tried to have a look for anything obvious, but I'm an Oracle DBA, and my SQL Server knowledge is pretty limited - we don't have any SQL Server DBAs or performance experts in house. So apologies for dumb questions! Version is SQL Server Enterprise 2017

It's made more complicated by the fact we have a lot of different databases on this cluster - thought the affected application is by far the busiest.

What did jump out at me was the very high number of ACCESS_METHODS_DATASET_PARENT latch waits - it was 99.5% of the latch waits, with 330 seconds of wait, per minute across 10 cores. If I'm reading the documentation correctly, that latch is used to control parallel table scans. Each wait was very small,0.002 seconds, but we are getting a huge number of them.

I had spotted that the MAX DOP was set to zero, and our server team spotted that the cpu allocation (It's running under VMWARE) wasn't correct - it was 10 1 core VCpus rather than a 10 core single cpu. So we changed it overnight to match up with https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver16#recommendations and set MAXDOP to 8, with the vcpu changes. Also increased the cost threshold for Parallelism from the default 5 to 25. It didn't seem to have much effect (though the average wait time seems to have come down)

What's confusing me is

  • I'm not seeing parallel operations looking at 'expensive' queries real time via the activity monitor (Do Parallel scans happen 'under the hood'?)
  • I'd have expected to see very high CPU utilisation - in Oracle the CPU would 'spin' while waiting on the latch. Does SQL Server just work differently?
  • My gut reaction is that we shouldn't be using parallel query on what is mainly an OLTP system at all, I wouldn't in Oracle. But seems standard to just leave it in SQL Server and let it decide?

Questions,

  • Am I just looking at the wrong thing here, and it's normal for SQL Server?
  • If not, how do I find the queries responsible?
  • Should I be going after the cause, not the symptom here i.e why are we doing table scans in the first place?

Any suggestions, gratefully received!

Improve this question
6
  • Your gut reaction is correct, for OLTP systems you usually want to reduce parallelism. Higher cost threshold means less queries will be parallelized, and lower MAXDOP means less resources when they are parallelized. (MAXDOP 1 will eliminate all parallelism, but is a tad heavy handed).
    – StrayCatDBA
    Commented Apr 30 at 17:11
  • Agreed - your gut reaction is correct. We're running into the same problem, with a vended application that tries to do both OLAP and OLTP and succeeds in doing neither very well. They're brute-forcing it with columnstore indexes. They can be useful but bring their own overhead and quirks. We can't change their code, so I've used Resource Governor to limit some of the more insane queries. This can be handy, but if used wrong Resource Governor can cause even more problems. Read up on it (I want to say Oracle has something similar) and use it as a last resort.
    – S M
    Commented Apr 30 at 18:37
  • Sorry ran out of room... I'm curious, do your SQL Server instances show a lot of blocking? Also what's page life expectancy like? You can get these from the performance counters in Perfmon: SQLServer:General Statistics\Processes blocked and SQLServer:Buffer Manager\Page life expectancy.
    – S M
    Commented Apr 30 at 18:42
  • Thanks - I don't have direct server access to this, but I can get someone to setup the perfmon counters.
    – Carlovski
    Commented May 1 at 8:23
  • I'm concerned you looked at the latches, but not at wait stats overall (at least it is not mentioned). Latches are usually only part of overall waits in my experience. Take a look at this article as a starting point
    – rois
    Commented May 2 at 9:20

0

Reset to default

Your Answer

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