"Heath" <He***@discussions.microsoft.com> wrote in message
news:63AE3AFA-2BF2-4B9E-B205-3139AFE2F6FB@microsoft.com...
> I’m trying to develop a couple of scripts to identify your instance is
> under
> memory pressure.   I’ve identified the following two scripts.
>
> I’m wondering if these two scripts do in fact identify memory pressure?
>
> Also does anyone know what “RESOURCE_MEM_STEADY” means.  Also I’m assuming
> that “RESOURCE_MEMPHYICAL_LOW” means SQL Server needs to reduce its memory
> due to a memory pressure, where as “RESOURCE_MEMPHYSICAL_HIGH” means SQL
> Server is growing its memory. Are my assumptions correct or do I have it
> backwards?
>
>
> Script 1: Using Ring Buffer
>
> The following script will return information about the last few time SQL
> Server had to manage memory, based on Approx_EventTime.  The more frequent
> the occurrence of Approx_EventTime the more memory pressures SQL Server is
> under.  Ideally you do not want to see a very recent time stamp.
> declare @ts_now bigint
> select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from
> sys.dm_os_sys_info
>
>
> select Timestamp,
> dateadd(ss, -1 * (@ts_now - [timestamp])/1000 , GetDate()) as
> Approx_EventTime,
>        Notification, AWEMemory, SinglePagesMemory,
> MultiplePagesMemory,CachedMemory
> from (
> select
>  record.value('(./top/Record/ResourceMonitor/Notification)[1]',
> 'char(100)') as Notification,
> record.value('(./top/Record/MemoryNode/AWEMemory)[1]', 'bigint') as
> AWEMemory,
> record.value('(./top/Record/MemoryNode/SinglePagesMemory)[1]', 'bigint')
> as SinglePagesMemory,
> record.value('(./top/Record/MemoryNode/MultiplePagesMemory)[1]',
> 'bigint') as MultiplePagesMemory,
> record.value('(./top/Record/MemoryNode/CachedMemory)[1]', 'char(100)') as
> CachedMemory,
> cast (timestamp as bigint) timestamp
> from (
> select timestamp, convert(xml, '<top>'+record+'</top>') as record
> from sys.dm_os_ring_buffers
> where ring_buffer_type = N'RING_BUFFER_RESOURCE_MONITOR'
>             ) as x
> ) as y
> order by timestamp desc
>
>
>
> Script 2: Using rounds_count
>
> The following script returns that last time SQL Server performed a sweep
> of
> the different memory caches.  If the rounds_count is increasing each time
> this script is run and  “time of sweep” is always close to the current
> time,
> then SQL Server is cleaning out the cache frequently.  Ideally you do not
> want to see a very recent “time of sweep”.
>
>
> declare @current_tick bigint
> declare @ticks_per_ms bigint
> select @current_tick = cpu_ticks, @ticks_per_ms = cpu_ticks_in_ms from
> select name, rounds_count,removed_all_rounds_count,
>  dateadd(ms,-1*(@current_tick -last_tick_time) / convert(float,
> @ticks_per_ms),getdate()) [time of sweep]
> from
>  sys.dm_os_memory_cache_clock_hands
> where
>  rounds_count > 0
>  and removed_all_rounds_count > 0
> order by [time of sweep]desc
>
>

"Uri Dimant" wrote:

> Hi
> First thing I would doing to check Page life expectancy counter in Perfmon
> (should not be <300)
>
> "Heath" <He***@discussions.microsoft.com> wrote in message
> news:63AE3AFA-2BF2-4B9E-B205-3139AFE2F6FB@microsoft.com...
> > I’m trying to develop a couple of scripts to identify your instance is
> > under
> > memory pressure.   I’ve identified the following two scripts.
> >
> > I’m wondering if these two scripts do in fact identify memory pressure?
> >
> > Also does anyone know what “RESOURCE_MEM_STEADY” means.  Also I’m assuming
> > that “RESOURCE_MEMPHYICAL_LOW” means SQL Server needs to reduce its memory
> > due to a memory pressure, where as “RESOURCE_MEMPHYSICAL_HIGH” means SQL
> > Server is growing its memory. Are my assumptions correct or do I have it
> > backwards?
> >
> >
> > Script 1: Using Ring Buffer
> >
> > The following script will return information about the last few time SQL
> > Server had to manage memory, based on Approx_EventTime.  The more frequent
> > the occurrence of Approx_EventTime the more memory pressures SQL Server is
> > under.  Ideally you do not want to see a very recent time stamp.
> > declare @ts_now bigint
> > select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from
> > sys.dm_os_sys_info
> >
> >
> > select Timestamp,
> > dateadd(ss, -1 * (@ts_now - [timestamp])/1000 , GetDate()) as
> > Approx_EventTime,
> >        Notification, AWEMemory, SinglePagesMemory,
> > MultiplePagesMemory,CachedMemory
> > from (
> > select
> >  record.value('(./top/Record/ResourceMonitor/Notification)[1]',
> > 'char(100)') as Notification,
> > record.value('(./top/Record/MemoryNode/AWEMemory)[1]', 'bigint') as
> > AWEMemory,
> > record.value('(./top/Record/MemoryNode/SinglePagesMemory)[1]', 'bigint')
> > as SinglePagesMemory,
> > record.value('(./top/Record/MemoryNode/MultiplePagesMemory)[1]',
> > 'bigint') as MultiplePagesMemory,
> > record.value('(./top/Record/MemoryNode/CachedMemory)[1]', 'char(100)') as
> > CachedMemory,
> > cast (timestamp as bigint) timestamp
> > from (
> > select timestamp, convert(xml, '<top>'+record+'</top>') as record
> > from sys.dm_os_ring_buffers
> > where ring_buffer_type = N'RING_BUFFER_RESOURCE_MONITOR'
> >             ) as x
> > ) as y
> > order by timestamp desc
> >
> >
> >
> > Script 2: Using rounds_count
> >
> > The following script returns that last time SQL Server performed a sweep
> > of
> > the different memory caches.  If the rounds_count is increasing each time
> > this script is run and  “time of sweep” is always close to the current
> > time,
> > then SQL Server is cleaning out the cache frequently.  Ideally you do not
> > want to see a very recent “time of sweep”.
> >
> >
> > declare @current_tick bigint
> > declare @ticks_per_ms bigint
> > select @current_tick = cpu_ticks, @ticks_per_ms = cpu_ticks_in_ms from
> > select name, rounds_count,removed_all_rounds_count,
> >  dateadd(ms,-1*(@current_tick -last_tick_time) / convert(float,
> > @ticks_per_ms),getdate()) [time of sweep]
> > from
> >  sys.dm_os_memory_cache_clock_hands
> > where
> >  rounds_count > 0
> >  and removed_all_rounds_count > 0
> > order by [time of sweep]desc
> >
> >
>
>
>

"Heath" <He***@discussions.microsoft.com> wrote in message
news:6AC2DA25-EADD-4E56-A20B-3C91FDA953D7@microsoft.com...
> Thanks for the advice on the page life expectency.  I probably should have
> note in the post that my page life expectency was low.
>
> What I'm trying to identify is ways to identify how often SQL Server is
> changing its memory allocations.  Meaning changing the amount of memory it
> is
> using meaning it is adjusting the "Target Server Memory" and "Total Server
> Memory" settings.  I assume there is some overhead in SQL Server having to
> constantly adjust the memory setting due to memory pressures.
>
> Is it always true that when the page life expectency is low that SQL
> Server
> is changing it's memory allocation frequently?
>
> "Uri Dimant" wrote:
>
>> Hi
>> First thing I would doing to check Page life expectancy counter in
>> Perfmon
>> (should not be <300)
>>
>> "Heath" <He***@discussions.microsoft.com> wrote in message
>> news:63AE3AFA-2BF2-4B9E-B205-3139AFE2F6FB@microsoft.com...
>> > I’m trying to develop a couple of scripts to identify your instance is
>> > under
>> > memory pressure.   I’ve identified the following two scripts.
>> >
>> > I’m wondering if these two scripts do in fact identify memory pressure?
>> >
>> > Also does anyone know what “RESOURCE_MEM_STEADY” means.  Also I’m
>> > assuming
>> > that “RESOURCE_MEMPHYICAL_LOW” means SQL Server needs to reduce its
>> > memory
>> > due to a memory pressure, where as “RESOURCE_MEMPHYSICAL_HIGH” means
>> > SQL
>> > Server is growing its memory. Are my assumptions correct or do I have
>> > it
>> > backwards?
>> >
>> >
>> > Script 1: Using Ring Buffer
>> >
>> > The following script will return information about the last few time
>> > SQL
>> > Server had to manage memory, based on Approx_EventTime.  The more
>> > frequent
>> > the occurrence of Approx_EventTime the more memory pressures SQL Server
>> > is
>> > under.  Ideally you do not want to see a very recent time stamp.
>> > declare @ts_now bigint
>> > select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from
>> > sys.dm_os_sys_info
>> >
>> >
>> > select Timestamp,
>> > dateadd(ss, -1 * (@ts_now - [timestamp])/1000 , GetDate()) as
>> > Approx_EventTime,
>> >        Notification, AWEMemory, SinglePagesMemory,
>> > MultiplePagesMemory,CachedMemory
>> > from (
>> > select
>> >  record.value('(./top/Record/ResourceMonitor/Notification)[1]',
>> > 'char(100)') as Notification,
>> > record.value('(./top/Record/MemoryNode/AWEMemory)[1]', 'bigint') as
>> > AWEMemory,
>> > record.value('(./top/Record/MemoryNode/SinglePagesMemory)[1]',
>> > 'bigint')
>> > as SinglePagesMemory,
>> > record.value('(./top/Record/MemoryNode/MultiplePagesMemory)[1]',
>> > 'bigint') as MultiplePagesMemory,
>> > record.value('(./top/Record/MemoryNode/CachedMemory)[1]', 'char(100)')
>> > as
>> > CachedMemory,
>> > cast (timestamp as bigint) timestamp
>> > from (
>> > select timestamp, convert(xml, '<top>'+record+'</top>') as record
>> > from sys.dm_os_ring_buffers
>> > where ring_buffer_type = N'RING_BUFFER_RESOURCE_MONITOR'
>> >             ) as x
>> > ) as y
>> > order by timestamp desc
>> >
>> >
>> >
>> > Script 2: Using rounds_count
>> >
>> > The following script returns that last time SQL Server performed a
>> > sweep
>> > of
>> > the different memory caches.  If the rounds_count is increasing each
>> > time
>> > this script is run and  “time of sweep” is always close to the current
>> > time,
>> > then SQL Server is cleaning out the cache frequently.  Ideally you do
>> > not
>> > want to see a very recent “time of sweep”.
>> >
>> >
>> > declare @current_tick bigint
>> > declare @ticks_per_ms bigint
>> > select @current_tick = cpu_ticks, @ticks_per_ms = cpu_ticks_in_ms from
>> > select name, rounds_count,removed_all_rounds_count,
>> >  dateadd(ms,-1*(@current_tick -last_tick_time) / convert(float,
>> > @ticks_per_ms),getdate()) [time of sweep]
>> > from
>> >  sys.dm_os_memory_cache_clock_hands
>> > where
>> >  rounds_count > 0
>> >  and removed_all_rounds_count > 0
>> > order by [time of sweep]desc
>> >
>> >
>>
>>
>>