Today, I have one very simple question based on following image. A full disclaimer is that I have no idea why it is like that. I tried to reach out to few of my friends who know a lot about SQL Server but no one has any answer.
Here is the question:
If you go to server properties and click on Advanced you will see the following screen. Under the Parallelism section if you noticed there are four options:
- Cost Threshold for Parallelism
- Locks
- Max Degree of Parallelism
- Query Wait
I can clearly understand why Cost Threshold for Parallelism and Max Degree of Parallelism belongs to Parallelism but I am not sure why we have two other options Locks and Query Wait belongs to Parallelism section. I can see that the options are ordered alphabetically but I do not understand the reason for locks and query wait to list under Parallelism.
Here is the question for you – Why Locks and Query Wait options are listed under Parallelism section in SQL Server Advanced Properties?
Please leave a comment with your explanation. I will publish valid answers on this blog with due credit.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
Memory-intensive queries (such as those involving sorting and hashing) are queued when there is not enough memory available to run the query. The query wait option specifies the time, in seconds (from 0 through 2147483647), that a query waits for resources before it times out. The default value for this option is -1. This means the time-out is calculated as 25 times the estimated query cost.
(Ref.
The locks option sets the maximum number of available locks, thereby limiting the amount of memory the SQL Server Database Engine uses for them. The default setting is 0, which allows the Database Engine to allocate and deallocate lock structures dynamically, based on changing system requirements.
(Ref.
These options are given in Parallelism category but I think these are useful also for without Parallelism…
Hi,
I am not sure about this, but i think that when we are having a server with few resources or we want to restrict no of users or security seasons then we use these options.
With “Locks” option, By default it’s having value “0”. It denotes that no of lock are dynamic.
we can set a maximum limit of locks. This will help us for control the workload on database.
Another option “Query Wait”, by default it’s -1. Its means that the time-out is calculated as 25 times the estimated query cost. Now, let suppose a estimated query having cost (a query which used to dump server or slow down server) is more than others. In this condition, “Query Wait” will take a huge amount of time. With “Query Wait” option, we can get ride of this problem.
I came to this conclusion after digging some articles.
I hope this will help you.
Thanks
vinay Kumar
From my knowledge, these options overrides the locks limit and query wait time for the parallel operations ONLY. I guess when there are many threads for a parallel operation, more locks might be needed to allow the parallelism. To control the locks, we may use this option under parallelism. In the similar way, the timeout also gets applied to control the time out of the parallel operations incase the parallel operations takes longer than expected.
It seems to me “locks” are equivalent to “semaphores”, meant to throttle the thread count.
I know this is not the right place to ask this but I don’t know where to do it. How do you supervise with software an SQLServer’s DBA so his/her unrestricted functions could be as well be restricted/supervised. I mean: It is not possible that all security in the whole world is rendered to a single person or a group of people with unrestricted access to your confidential data.
I am not sure whether it is correct or not. i will provide what i have in my mind about this: As per MAXDROP every thread will be assign to some NUMA Node so if we have less nodes or high requests so that process or request will wait to get the numa memory so thay have mention this options like lock and query wait ‘s which will occures for a specific exicution…
Locks and Query Wait are options for server level parallelism, while Cost Threshold for Parallelism and Max Degree of Parallelism are for query level parallelism.
i change locks value to 5000 and now i can’t set it back to defaul value “0”.
You could change it by using the query specified here “https://msdn.microsoft.com/en-us/library/ms190253.aspx
This is a part of deadlock report where some of the session are waiting on CXPACKET wait.
There is a max number of parallel executions allowed by MSSQL after which there is wait to acquire a execution thread.