Home
  •  

MSSQL 2014: User Resource Pool Memory Consumption (\%)

Microsoft.SQLServer.2014.CollectionRule.UserResourcePool.MemoryConsumptionPercent (Rule)

Collects amount of memory used by the resource pool, expressed as a percentage of memory available for In-Memory tables for the given Resource Pool.

Knowledge Base article:

Summary

The rule collects amount of memory used by the pool, expressed as a percentage of memory available for In-Memory tables for the given Resource Pool.

SQL Server In-Memory OLTP uses more memory and in different ways than SQL Server does. It is possible that the amount of memory you installed and allocated for In-Memory OLTP becomes inadequate for your growing needs. If so, you could run out of memory.

Overrideable Parameters

Name

Description

Default Value

Enabled

 

Yes

Generates Alerts

 

No

Interval (seconds)

The recurring interval of time in seconds in which to run the workflow.

900

Script Delay (milliseconds)

This parameter sets the delay between consecutive T-SQL queries executed by the workflow. This may help to reduce the footprint generated by the workflow in case of large number of target objects. Please advise with Microsoft Support before changing this parameter.

0

Synchronization Time

The synchronization time specified by using a 24-hour format. May be omitted.

 

Timeout (seconds)

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

300

Element properties:

TargetMicrosoft.SQLServer.2014.UserResourcePool
CategoryPerformanceCollection
EnabledTrue
Alert GenerateFalse
RemotableTrue

Member Modules:

ID Module Type TypeId RunAs 
DS DataSource Microsoft.SQLServer.2014.DataSource.PoolMemoryConsumptionPerf Default
WriteToDB WriteAction Microsoft.SystemCenter.CollectPerformanceData Default
WriteToDW WriteAction Microsoft.SystemCenter.DataWarehouse.PublishPerformanceData Default

Source Code:

<Rule ID="Microsoft.SQLServer.2014.CollectionRule.UserResourcePool.MemoryConsumptionPercent" Target="SQL2014Core!Microsoft.SQLServer.2014.UserResourcePool" Enabled="true">

  <Category>PerformanceCollection</Category>

  <DataSources>

    <DataSource ID="DS" TypeID="Microsoft.SQLServer.2014.DataSource.PoolMemoryConsumptionPerf">

      <IntervalSeconds>900</IntervalSeconds>

      <SyncTime/>

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

      <SqlInstanceName>$Target/Host/Host/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.ServerRole"]/InstanceName$</SqlInstanceName>

      <ObjectName>SQL User Resource Pool</ObjectName>

      <CounterName>User Resource Memory Consumption (%)</CounterName>

      <InstanceName/>

      <Value>$Data/Property[@Name='PoolUsedMemoryPercent']$</Value>

      <PoolID>$Target/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.ResourcePool"]/PoolID$</PoolID>

      <TimeoutSeconds>300</TimeoutSeconds>

      <ScriptDelayMsec>0</ScriptDelayMsec>

    </DataSource>

  </DataSources>

  <WriteActions>

    <WriteAction ID="WriteToDB" TypeID="SC!Microsoft.SystemCenter.CollectPerformanceData"/>

    <WriteAction ID="WriteToDW" TypeID="SCDW!Microsoft.SystemCenter.DataWarehouse.PublishPerformanceData"/>

  </WriteActions>

</Rule>