SQLServerMonitoringSolution/sp_ossysmemory.sql at master · ash3rr/SQLServerMonitoringSolution · GitHub

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
USE [DBATools]
GO

/****** Object:  StoredProcedure [dbo].[sp_ossysmemory]    Script Date: 12/9/2021 4:19:31 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--drop procedure sp_ossysmemory
CREATE PROCEDURE [dbo].[sp_ossysmemory] @table bit = null
AS
SET NOCOUNT ON


IF @table = 1
BEGIN
INSERT INTO dbatools.dbo.os_sys_memory
SELECT sm.total_page_file_kb / 1024 as [Total_page_file_MB],
sm.available_page_file_kb / 1024 as [Available_page_file_MB],
sm.total_physical_memory_kb / 1024 as [Total_physical_memory_MB],
sm.available_physical_memory_kb / 1024 as [Available_physical_memory_MB],
sm.kernel_nonpaged_pool_kb / 1024 as [Kernal_nonpaged_pool_MB],
sm.kernel_paged_pool_kb / 1024 as [Kernel_paged_pool_MB],
sm.system_cache_kb / 1024 as [System_cache_MB],
sm.system_high_memory_signal_state,
sm.system_low_memory_signal_state,
sm.system_memory_state_desc,
getdate() as [MeasurementTime],
CONVERT(date,Getdate()) as [MeasurementDate]
--INTO dbatools.dbo.os_sys_memory
FROM sys.dm_os_sys_memory sm
END
ELSE
BEGIN

	SELECT sm.total_page_file_kb / 1024 as [Total_page_file_MB],
	sm.available_page_file_kb / 1024 as [Available_page_file_MB],
	sm.total_physical_memory_kb / 1024 as [Total_physical_memory_MB],
	sm.available_physical_memory_kb / 1024 as [Available_physical_memory_MB],
	sm.kernel_nonpaged_pool_kb / 1024 as [Kernal_nonpaged_pool_MB],
	sm.kernel_paged_pool_kb / 1024 as [Kernel_paged_pool_MB],
	sm.system_cache_kb / 1024 as [System_cache_MB],
	sm.system_high_memory_signal_state,
	sm.system_low_memory_signal_state,
	sm.system_memory_state_desc
	FROM sys.dm_os_sys_memory sm
	RETURN
END


GO