We've MSSQL database serving JIRA and Confluence. SQL runs on Windows server 2008 R2 standard in a VM that has 2 vCPUs (vmware). We see that it crashed with below error; restarting the VM did the trick. After investigating, it was recommended to set an optimum value for "max worker threads". Currently it is set to 512.
1) Does lowering this value has any impact?
2) Does Atlassian has any recommendations for 'max worker threads" configuration on MS SQL?
3) And, what are the things I should keep in mind while configuring this value?
Error Log output:
2017-05-02 19:55:49.89 Server New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 60 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 0%. System Idle: 0%.
2017-05-02 19:56:50.13 Server New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 120 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 0%. System Idle: 0%.
2017-05-02 19:57:50.32 Server New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 180 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 0%. System Idle: 0%.
