Home
  •  

SQL Re-Compilation

Microsoft.SQLServer.2008.DBEngine.SQLReCompilationMonitor (UnitMonitor)

SQL Re-Compilation for 2008 DB Engine

Knowledge Base article:

Summary

SQL Re-Compilation monitor for SQL 2008 DB Engine

Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL Server detects the changes that invalidate an execution plan and marks the plan as not valid. A new plan must then be recompiled for the next connection that executes the query.

Causes

High number of recompilations comparing to compilations.

Resolutions

Start SQL Profiler and check SQL:StmtRecompile trace to identify root cause of issue.

Overrideable Parameters

Name

Description

Default Value

Alert Priority

Defines Alert Priority.

Normal

Alert Severity

Defines Alert Severity.

Warning

Enabled

Enables or disables the workflow.

No

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

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

Threshold

If the ratio between SQL Re-Compilation and SQL Compilation is greater than this threshold alert will be generated

25

Timeout (seconds)

Specifies the time the workflow is allowed to run before being closed and marked as failed.

200

Element properties:

TargetMicrosoft.SQLServer.2008.DBEngine
Parent MonitorSystem.Health.PerformanceState
CategoryPerformanceHealth
EnabledFalse
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.2008.DBEngine.SQLReCompilation.MonitorType
RemotableTrue
AccessibilityPublic
Alert Message
SQL DB 2008 SQL Re-Compilation is too high
SQL instance "{0}" SQL Re-Compilation on computer "{1}" is too high. See "alert context" tab for more details.
RunAsDefault

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.2008.DBEngine.SQLReCompilationMonitor" Accessibility="Public" Enabled="false" Target="SQL2008Core!Microsoft.SQLServer.2008.DBEngine" ParentMonitorID="SystemHealth!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2008.DBEngine.SQLReCompilation.MonitorType" ConfirmDelivery="false">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2008.DBEngine.SQLReCompilationMonitor.AlertMessage">

    <AlertOnState>Warning</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</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="Warning"/>

  </OperationalStates>

  <Configuration>

    <IntervalSeconds>300</IntervalSeconds>

    <TimeoutSeconds>200</TimeoutSeconds>

    <Threshold>25</Threshold>

    <NumSamples>6</NumSamples>

    <SyncTime/>

  </Configuration>

</UnitMonitor>