Lock Pages in Memory

Microsoft.SQLServerAppliance.MDW.DBEngineAllocatedPagesInMemory (UnitMonitor)

This monitor detects that the "Lock pages in memory" policy is granted for SQL Server service account on Microsoft Data Warehouse Appliance.

Knowledge Base article:

Summary

This monitor detects that the "Lock pages in memory" policy is not granted for SQL Server service account on Microsoft Data Warehouse Appliance.

Causes

An unhealthy state of this monitor means that Lock Pages in memory policy is not granted for SQL Server Log On Account.

Resolutions

To enable the lock pages in memory option:

On the Start menu, click Run. In the Open box, type gpedit.msc.
The Group Policy dialog box opens.
On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Select the User Rights Assignment folder.
The policies will be displayed in the details pane.
In the pane, double-click Lock pages in memory.
In the Local Security Policy Setting dialog box, click Add.
In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.
Restart SQL Server windows service to apply changes.

Element properties:

Target

Microsoft.SQLServer.2008.DBEngine

Parent Monitor

System.Health.ConfigurationState

Category

ConfigurationHealth

Enabled

False

Alert Generate

True

Alert Severity

Error

Alert Priority

Normal

Alert Auto Resolve

True

Monitor Type

Microsoft.SQLServerAppliance.MDW.DBEngineAllocatedPagesInMemoryType

Remotable

True

Accessibility

Public

Alert Message

Lock Pages

The "Lock pages in memory" policy is not granted for SQL Server Log On Account on Microsoft Data Warehouse Appliance {0}

RunAs

Default

Source Code:

<UnitMonitor ID="Microsoft.SQLServerAppliance.MDW.DBEngineAllocatedPagesInMemory" Accessibility="Public" Enabled="false" Target="SQL2008Disco!Microsoft.SQLServer.2008.DBEngine" ParentMonitorID="Health!System.Health.ConfigurationState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServerAppliance.MDW.DBEngineAllocatedPagesInMemoryType" ConfirmDelivery="false">

  <Category>ConfigurationHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServerAppliance.MDW.DBEngineAllocatedPagesInMemory.AlertMessage">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Error</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</AlertParameter1>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Good" MonitorTypeStateID="Good" HealthState="Success"/>

    <OperationalState ID="Bad" MonitorTypeStateID="Bad" HealthState="Error"/>

  </OperationalStates>

  <Configuration>

    <IntervalSeconds>3600</IntervalSeconds>

    <SyncTime/>

    <TimeoutSeconds>300</TimeoutSeconds>

    <ConnectionString>$Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ConnectionString$</ConnectionString>

  </Configuration>

</UnitMonitor>