Thread Count for 2012 DB Engine
Usually SQL Server opens system thread for each query request, but if amount of threads exceeds specified max worker threads value, SQL Server pools the worker threads. When all worker threads are active with long running queries, SQL Server may appear unresponsive until a worker thread completes and becomes available. Though not a defect, this can sometimes be undesirable. The monitor analyzes amount of free threads and notifies if the amount is low.
An increase in the amount of work causing an increase in utilized threads, this could indicate that SQL Server is working under significant load or an excessive number of queries running in parallel.
Adjusting max worker threads is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
Thread pooling helps optimize performance when large numbers of clients are connected to the server. Usually, a separate operating system thread is created for each query request. However, with hundreds of connections to the server, using one thread per query request can consume large amounts of system resources. The max worker threads option enables SQL Server to create a pool of worker threads to service a larger number of query requests, which improves performance.
Configure the max worker threads Server Configuration Option
http://go.microsoft.com/fwlink/?LinkId=789186
Name | Description | Default Value |
Alert Priority | Defines Alert Priority. | Normal |
Alert Severity | Defines Alert Severity. | Error |
Cache Expiration Time | Specifies the maximum age of information from cache the workflow can use. May be omitted. | 43200 |
Enabled | Enables or disables the workflow. | Yes |
Generates Alerts | Defines whether the workflow generates an Alert. | Yes |
Interval (seconds) | The recurring interval of time in seconds in which to run the workflow. | 300 |
Minimum Free Threads Count | Data Source determine a threshold and thread count for each DB Engine process, alert will be generated if threshold minus current thread count is less or equal than free thread count | 10 |
Number of samples | Number of samples to average before compare it with threshold | 6 |
Synchronization Time | The synchronization time specified by using a 24-hour format. May be omitted. | 00:06 |
Timeout (seconds) | Specifies the time the workflow is allowed to run before being closed and marked as failed. | 200 |
Target | Microsoft.SQLServer.2012.DBEngine | ||
Parent Monitor | System.Health.PerformanceState | ||
Category | PerformanceHealth | ||
Enabled | True | ||
Alert Generate | True | ||
Alert Severity | Error | ||
Alert Priority | Normal | ||
Alert Auto Resolve | True | ||
Monitor Type | Microsoft.SQLServer.2012.DBEngine.ThreadCount.MonitorType | ||
Remotable | True | ||
Accessibility | Public | ||
Alert Message |
| ||
RunAs | Default |
<UnitMonitor ID="Microsoft.SQLServer.2012.DBEngine.ThreadCountMonitor" Accessibility="Public" Enabled="true" Target="SQL2012Core!Microsoft.SQLServer.2012.DBEngine" ParentMonitorID="SystemHealth!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2012.DBEngine.ThreadCount.MonitorType" ConfirmDelivery="false">
<Category>PerformanceHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.2012.DBEngine.ThreadCountMonitor.AlertMessage">
<AlertOnState>Error</AlertOnState>
<AutoResolve>true</AutoResolve>
<AlertPriority>Normal</AlertPriority>
<AlertSeverity>Error</AlertSeverity>
<AlertParameters>
<AlertParameter1>$Target/Property[Type="SQL!Microsoft.SQLServer.ServerRole"]/InstanceName$</AlertParameter1>
<AlertParameter2>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</AlertParameter2>
</AlertParameters>
</AlertSettings>
<OperationalStates>
<OperationalState ID="Normal" MonitorTypeStateID="Normal" HealthState="Success"/>
<OperationalState ID="Error" MonitorTypeStateID="Error" HealthState="Error"/>
</OperationalStates>
<Configuration>
<ServiceName>$Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ServiceName$</ServiceName>
<IntervalSeconds>300</IntervalSeconds>
<TimeoutSeconds>200</TimeoutSeconds>
<MinFreeThreadsCount>10</MinFreeThreadsCount>
<NumSamples>6</NumSamples>
<SyncTime/>
<CacheExpirationTime>43200</CacheExpirationTime>
</Configuration>
</UnitMonitor>