- How to know whether a thread is available for new request or currently busy with other requests.
You can use DMV sys.dm_os_schedulers to get this information. The column you have to refer is work_queue_count
. As per BOL it means
Number of tasks in the pending queue. These tasks are waiting for a worker to pick them up. Is not nullable.
You can use below query to check number of waiting taks for all online schedulers
select work_queue_count from sys.dm_os_scheduler where status='Visible online'
I am not sure what you are trying to achieve but below is what I got from this blog
select wt.session_id,
ot.task_state,
wt.wait_type,
wt.wait_duration_ms,
wt.blocking_session_id,
wt.resource_description,
es.[host_name],
es.[program_name]
FROM sys.dm_os_waiting_tasks wt
INNER JOIN sys.dm_os_tasks ot
ON ot.task_address = wt.waiting_task_address
INNER JOIN sys.dm_exec_sessions es
ON es.session_id = wt.session_id
WHERE es.is_user_process = 1
- I could see count more than 500 with suspended status.I dont see any blockings
A suspended is thread which is waiting for some resource and is currently not active. The wait can be I/O, network etc.. See the blog I have shared for more details.
- Can i use below query to find that i have worker thread starvation
No that is not the correct query.
Stop using sys.sysprocesses is legacy view and MS does not recommends it to use. Instead use sys.dm_exec_requests
You can get worker thread count for each scheduler using DMV sys.dm_os_scheduler
select current_workers_count from sys.dm_os_scheduler where status='visible online'
1. Should I have at least one logical processor per instance, given that I currently only have 4 logical processors for 6 instances and MAX_DOP set to 0?
That depends, how much CPU usage is each instance using on average? You can get this information from the default health session extended event that is running (assuming 2008+).
Four logical processors may be perfectly adequate for this workload - we won't know until we have data. Having said that, since each instance of SQL Server acts on its' own and has no idea of the other instances installed, I'm sure Windows isn't too happy about swapping threads.
I'd take a look at my wait stats dmv and see if we have a higher percentage of wait time on the signal_wait part which would indicate scheduling issues and possible contention between instances. Additionally I'd trend this with the cpu context switches to see if there is a weak or direct correlation with "bad times" and "good times" of instance health.
My gut, though, tells me that unless these are woefully underutilized instances that this server is bound for issues - whether they are happening now or in a few months from now.
2. If I had one logical processor per instance, should I leave MAX_DOP at 0 or limit each instance to MAX_DOP = 1?
MAXDOP just limits the number of logical processors ('schedulers') that a single parallel query may use during execution. There is nothing stopping SQL Server from running multiple parallel queries; in fact I've diagnosed and fixed this very issue a multitude of times for places that didn't even know they had a scheduling problem (it was looked at as a "blocking" problem).
Setting MAXDOP to 1 essentially makes all user queries single threaded. This, again, doesn't stop SQL Server from executing more than a single task at a time as it merely forces serial executions. This means each instance has four (4) visible online schedulers for a total of 4*x number of instances possible concurrent queries. Setting MAXDOP here doesn't solve the issue of being logically overburdened, not taking into account the actual setting of the virtual server as to whether or not it can run on hyper threaded (if enabled) cores. Yikes.
I'm not saying forcing MAXDOP to 1 is good or bad, just that we have no data to go on. Thus we won't know the impact of the setting. Again, though, I'd DEFINITELY not have it set to four (4)!
3. Seeing as it is a Standard Edition should I limit MAX_DOP to 4 as an alternative?
Well, it can't be higher than four (4), so it's already limited. This question, I believe, is closely coupled with the question above. Hopefully I've explained it to a satisfactory level of understanding.
If you want to get an idea (due to the small threshold for parallelism) of how many queries are being parallelized, you can check the execution plans dmv. This is a fairly expensive operation, so please do it off hours or times of little to no usage between ALL of the instances. Remember, what you do to one instance is going to be (roughly) done to all because of the shared nature of the server.
Best Answer
It seems there is a documentation error when the item was copied over from MSDN to Docs. What you see in Dorr's post is correct and is applicable back to 2012 SP4 + latest CUs.
I'll make the required documentation changes.The documentation has been updated.The reason you have a warning is because you have
pendingTasks="1"
. This could indicate an issue with a scheduler and should be investigated.In the same Docs article that you linked, there is a query that can be used to track tasks and their status. It'd be interesting to see if it's only tasks on a specific scheduler, only login tasks, etc.