SQL Server 2019 session query Status keeps switching from Running to Runnable for multiple hours and doesn't seem to finish?

Question

Hello, we have SQL Server 2019 on a VM and recently notice a session query Status keeps switching from Running to Runnable and that the "Wait Type" field shows nothing.

Seems like it runs daily throughout the entire day (over 12 hours) which end user mentions it doesn't complete.

My research so far query is...frequently being scheduled to execute but is not consistently getting CPU time...frequent transition between these states suggests CPU contention or scheduling pressure

Possible Solutions

• Check if CPU is overloaded and optimize workloads.
• Reduce parallelism if excessive CPU contention occurs (MAXDOP settings).
• Review and adjust Resource Governor settings if limits are affecting queries.
• Optimize queries to reduce CPU-intensive operations.
• If needed, scale up hardware resources (more CPUs or faster processors).

I ran the following:

SELECT * FROM sys.dm_os_schedulers WHERE scheduler_id < 255;

And totalling the "runnable_tasks_count" output column and is usually coming out to greater than 5 which believe is a bit high.

Am not sure if changing MAXDOP is the path since we've had this setting for years which had been doing well overall for this SQL Server (and to change it just for 1 query), can't increase CPU as that sounds like purchase more license cost, and some other things in above am scratching my head.

Being that it's on a VM is it possible that the underlying VM resources could be affecting...like not actually giving or taking away CPU/memory, VM maintenances, etc.. under the hood that we can't see on the surface through our SQL monitoring tool?

Sounds like in general if there are multiple concurrent queries and/or processes competing for CPU (and/or other resources) like too many people in line at the grocery store checking out at the same time...others will have to wait before the one who's in front finishes processing...Anyone have any further thoughts to help this session query?

Thanks in advance.

Answer 1

Playing with MAXDOP, resource governor etc is of course moot when it comes to the running query.

To deal with the running query, you need to gather information about it. My beta_lockinfo can help you with this. It will give you the query text, the actual execution plan so far, and it will show which locks it's holding, and if there is any blocking.

The actual execution plan can give you an hint if the query is near for completion, or if it may run for a few more years.

The output also includes the top 5 wait types for the session, which can give you an idea of what the process has problems with.