2

I have been trying to use the Query Store in SQL Server as an easy way to identify queries running in parallel and the degree of parallelism. However, when I look at the max_dop field in sys.query_store_runtime_stats, I am getting some values that are way beyond the number of CPU's the instance has. I have looked at another instance and also found the erroneous information.

Since one instance is running SQL Server 2017 and the other is running 2019, it doesn't seem to be related to a particular version.

Has anyone else seen similar problems? Am I misinterpreting the column definition?

8
  • 2
    What is your SQL Server version and CU? On my SQL Server 2019 CU5 instance with 8 vcpus which has only been up a few days, i have 132453 query store runtime stats rows last_dop <= 8; 5 with last_dop > 8. All 5 of the queries with last_dop > 8 are metadata queries - queries of DMVs, system catalog tables, or information schema. – sqL_handLe Jan 13 at 3:52
  • 2
    Does the value exceed the number of visible schedulers? SELECT COUNT(*) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'; – Dan Guzman Jan 13 at 11:21
  • One of the versions is Microsoft SQL Server 2019 (RTM-CU8), the other is a 2017 instance. In both cases, the numbers being shown exceed the number of schedulers or CPU's by far. The queries themselves are not limited to system queries. I have found something interesting though. In all the cases where the numbers are over, the associated column in sys.query_store_plan called is_parallel_plan is actually set to 0. So I'd have to say this column has suspect data for sure. – Joe Lax Jan 13 at 20:59
  • hm. so is_parallel_plan is 0, but last_dop is a value too high to be expected on the system even for a parallel plan. is auto-update stats enabled? how about async? if auto update stats async is disabled but auto update stats is enabled i wonder if stats updates within optimizer activity before plan selection are going parallel and messing with the value that ends up in last_dop? – sqL_handLe Jan 16 at 19:51
  • given the order of magnitude difference between the number reported and the number of cpus' (over ten times as much) I have to think it's a bug at this point. – Joe Lax Jan 18 at 18:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.