Home
  •  

Thread Count

Microsoft.SQLServer.2012.DBEngine.ThreadCountMonitor (UnitMonitor)

Thread Count for 2012 DB Engine

Knowledge Base article:

Summary

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.

Causes

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.

Resolutions

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

Overrideable Parameters

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

Element properties:

TargetMicrosoft.SQLServer.2012.DBEngine
Parent MonitorSystem.Health.PerformanceState
CategoryPerformanceHealth
EnabledTrue
Alert GenerateTrue
Alert SeverityError
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.2012.DBEngine.ThreadCount.MonitorType
RemotableTrue
AccessibilityPublic
Alert Message
SQL DB 2012 Engine Thread Count is too high
SQL instance "{0}" Thread Count on computer "{1}" is too high. See "alert context" tab for more details.
RunAsDefault

Source Code:

<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>