memory

 --BUFFER CACHE HIT RATIO PERCENTAGE 

 

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio  

FROM sys.dm_os_performance_counters  a  

JOIN  (SELECT cntr_value, OBJECT_NAME  

    FROM sys.dm_os_performance_counters    

    WHERE counter_name = 'Buffer cache hit ratio base'  

        AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON  a.OBJECT_NAME = b.OBJECT_NAME  

WHERE a.counter_name = 'Buffer cache hit ratio'  

AND a.OBJECT_NAME = 'SQLServer:Buffer Manager' 

 

--PAGE LIFE EXPECTANCY VALUE 

 

--(max memory in GB) / 4 * 300 

 

--Buffer pool memory in GB / 4 ) x 300 

  

--So what's a "good" PLE for a read-mostly operation 

-- Here's a handy formula I use to get a decent estimate: 

--PLE threshold = ((MAXBP(MB)/1024)/4)*300 

 

 

SELECT object_name, counter_name, cntr_value 

FROM sys.dm_os_performance_counters 

WHERE [object_name] LIKE '%Buffer Manager%' 

AND [counter_name] = 'Page life expectancy' 

 

--Output: buffer cache ratio should be above 97 

 

SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)],  

       available_physical_memory_kb/1024 AS [Available Memory (MB)],  

       total_page_file_kb/1024 AS [Total Page File (MB)],  

               available_page_file_kb/1024 AS [Available Page File (MB)],  

               system_cache_kb/1024 AS [System Cache (MB)], 

       system_memory_state_desc AS [System Memory State] 

FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE); 

  

-- You want to see "Available physical memory is high" 

-- This indicates that you are not under external memory pressure  

--------------------------------------------------------------------------- 

-- SQL Server Process Address space info  (Query 36) (Process Memory) 

-- (shows whether locked pages is enabled, among other things) 

SELECT physical_memory_in_use_kb/1024 AS [SQL Server Memory Usage (MB)], 

       large_page_allocations_kb, locked_page_allocations_kb, page_fault_count,  

               memory_utilization_percentage, available_commit_limit_kb,  

               process_physical_memory_low, process_virtual_memory_low 

FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE); 

  

-- You want to see 0 for process_physical_memory_low 

-- You want to see 0 for process_virtual_memory_low 

-- This indicates that you are not under internal memory pressure 

 

--If the SQLServer Total Server Memory (KB) counter is less than the Target Server Memory (KB) counter. 

--Total Server Memory < Target Server Memory. 

--Then this means that SQL Server does not have enough memory to run efficiently  

SELECT [counter_name], [cntr_value] 

FROM sys.dm_os_performance_counters 

WHERE 

        [object_name] LIKE '%Memory Manager%' 

AND [counter_name] IN ('Total Server Memory (KB)', 'Target Server Memory (KB)')

-- Calculate ticks to timestamp 
SELECT @ticksNow = OSI.cpu_ticks / CONVERT(float, OSI.cpu_ticks) 
      ,@ticksMs = cpu_ticks
FROM sys.dm_os_sys_info AS OSI; 
 
;WITH util AS 
   (SELECT RBS.Rc.value('(./Record/@id)[1]', 'bigint') AS RecordID 
          ,RBS.Rc.value('(//SystemHealth/SystemIdle)[1]', 'bigint') AS SystemIdle 
          ,RBS.Rc.value('(//SystemHealth/ProcessUtilization)[1]', 'bigint') AS ProcessUtil 
          ,RBS.Rc.value('(//SystemHealth/MemoryUtilization)[1]', 'bigint') AS MemoryUtil 
          ,RBS.Rc.value('(//SystemHealth/PageFaults)[1]', 'bigint') AS PageFaults 
          ,RBS.Rc.value('(//SystemHealth/UserModeTime)[1]', 'bigint') AS UserModeTime 
          ,RBS.Rc.value('(//SystemHealth/KernelModeTime)[1]', 'bigint') AS KernelModeTime 
          ,RBS.EventStamp 
    FROM (SELECT ORB.[timestamp] AS EventStamp 
                ,CONVERT(XML, ORB.record) AS Rc  
          FROM sys.dm_os_ring_buffers AS ORB 
          WHERE ORB.ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
          ) AS RBS 
    ) 
SELECT UT.RecordID 
      ,DATEADD(ms, -1 * (@ticksNow - UT.EventStamp), GETDATE()) AS EventTime 
      ,UT.SystemIdle 
      ,UT.ProcessUtil       
      ,UT.MemoryUtil 
      ,UT.PageFaults 
      ,UT.UserModeTime 
      ,UT.KernelModeTime 
FROM util AS UT 
ORDER BY UT.RecordID DESC;