forked from mtikoian/SQLDBA-SSMS-Solution
-
Notifications
You must be signed in to change notification settings - Fork 10
/
Copy pathCapture-CPU-Memory.sql
76 lines (69 loc) · 5.29 KB
/
Capture-CPU-Memory.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
-- https://www.sqlskills.com/blogs/jonathan/identifying-external-memory-pressure-with-dm_os_ring_buffers-and-ring_buffer_resource_monitor/
--exec sp_HealthCheck 2
use DBA;
go
alter procedure usp_collect_performance_metrics @verbose bit = 0
as
begin
set nocount on;
declare @current_time datetime2 = getdate();
--select * from sys.dm_os_sys_info
insert dbo.dm_os_sys_memory
select --'sys.dm_os_sys_memory' as RunningQuery,
@current_time as collection_time, cast(sm.total_physical_memory_kb * 1.0 / 1024 / 1024 as numeric(20,0)) as total_physical_memory_gb,
cast(sm.available_physical_memory_kb * 1.0 / 1024 / 1024 as numeric(20,2)) as available_physical_memory_gb,
cast((sm.total_page_file_kb - sm.available_page_file_kb) * 1.0 / 1024 / 1024 as numeric(20,0)) as used_page_file_gb,
cast(sm.system_cache_kb * 1.0 / 1024 /1024 as numeric(20,2)) as system_cache_gb,
cast((sm.available_physical_memory_kb - sm.system_cache_kb) * 1.0 / 1024 as numeric(20,2)) as free_memory_mb,
sm.system_memory_state_desc,
cast(((sm.total_physical_memory_kb-sm.available_physical_memory_kb) * 100.0) / sm.total_physical_memory_kb as numeric(20,2)) as memory_usage_percentage
--into dbo.dm_os_sys_memory
from sys.dm_os_sys_memory as sm
-- SQL Server Process Address space info (Query 6) (Process Memory)
-- (shows whether locked pages is enabled, among other things)
INSERT dbo.dm_os_process_memory
SELECT --'sys.dm_os_process_memory' as RunningQuery,
@current_time as collection_time,
physical_memory_in_use_kb/1024 AS [SQL Server Memory Usage (MB)],
page_fault_count, memory_utilization_percentage, available_commit_limit_kb,
process_physical_memory_low, process_virtual_memory_low,
locked_page_allocations_kb/1024 AS [SQL Server Locked Pages Allocation (MB)],
large_page_allocations_kb/1024 AS [SQL Server Large Pages Allocation (MB)]
--into dbo.dm_os_process_memory
FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);
--Total amount of RAM consumed by database data (Buffer Pool). This should be the highest usage of Memory on the server.
insert dbo.dm_os_performance_counters
Select --'performance_counters' as RunningQuery,
@current_time as collection_time,
buffer_pool_mb = (Select SUM(pages_kb)/1024 AS [SPA Mem, Mb] FROM sys.dm_os_memory_clerks WITH (NOLOCK) Where type = 'MEMORYCLERK_SQLBUFFERPOOL')
--Total amount of RAM used by SQL Server memory clerks (includes Buffer Pool)
, all_memory_clerks_mb = (Select SUM(pages_kb)/1024 AS [SPA Mem, Mb] FROM sys.dm_os_memory_clerks WITH (NOLOCK))
--How long in seconds since data was removed from the Buffer Pool, to be replaced with data from disk. (Key indicator of memory pressure when below 300 consistently)
,page_life_expectancy = (SELECT cntr_value FROM sys.dm_os_performance_counters WITH (NOLOCK) WHERE [object_name] LIKE N'%Buffer Manager%' AND counter_name = N'Page life expectancy' )
--How many memory operations are Pending (should always be 0, anything above 0 for extended periods of time is a very high sign of memory pressure)
,memory_grants_pending = (SELECT cntr_value FROM sys.dm_os_performance_counters WITH (NOLOCK) WHERE [object_name] LIKE N'%Memory Manager%' AND counter_name = N'Memory Grants Pending' )
--How many memory operations are Outstanding (should always be 0, anything above 0 for extended periods of time is a very high sign of memory pressure)
,memory_grants_outstanding = (SELECT cntr_value FROM sys.dm_os_performance_counters WITH (NOLOCK) WHERE [object_name] LIKE N'%Memory Manager%' AND counter_name = N'Memory Grants Outstanding' )
,total_server_memory_mb = CAST((SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (KB)' )*1.0/1024 AS decimal(20,0))
,target_server_memory_mb = CAST((SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Target Server Memory (KB)' )*1.0/1024 AS decimal(20,0))
--into dbo.dm_os_performance_counters
insert dbo.dm_os_ring_buffers
SELECT top 1 EventTime as collection_time,
CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization,
CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization
--into dbo.dm_os_ring_buffers
FROM ( SELECT record.value('(Record/@id)[1]', 'int') AS record_id,
DATEADD (ms, -1 * (ts_now - [timestamp]), GETDATE()) AS EventTime,
100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,
record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2,
100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,
record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2
FROM ( SELECT timestamp, CONVERT (xml, record) AS record, cpu_ticks / (cpu_ticks/ms_ticks) as ts_now
FROM sys.dm_os_ring_buffers cross apply sys.dm_os_sys_info
WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%'
) AS t
) AS t
ORDER BY EventTime desc;
end
go