SQL-Scripts/SAM - Server Overview/Server Profile.sql at master · springbootexamples/SQL-Scripts · 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
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
USE zzPerfMon
GO

/*Declare variables*/
DECLARE
	@UsedDataGB INT,
	@UsedLogGB INT,
	@TotalMemoryGB INT,
	@NUMACount INT,
	@CPUCount INT,
	@MaxSQLMemory INT,
	@Ratio VARCHAR(100)

/* 1. Total Disk Space */
Set @UsedDataGB=(select CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00))
FROM master.sys.master_files
where type<>1
)

Set @UsedLogGB=(select CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00))
FROM master.sys.master_files
where type=1
)

/* 2. Total Physical Memory GB */
create table #SVer(ID int,  Name  sysname, Internal_Value int, Value nvarchar(512))
insert #SVer exec master.dbo.xp_msver

SET @TotalMemoryGB=(SELECT Internal_Value/1024
FROM #SVer
WHere Name = 'PhysicalMemory')

drop table #SVer


/* 3. Disk space to memory ratio */
SET @ratio=(SELECT CAST(@UsedDataGB/@TotalMemoryGB AS VARCHAR(100))+':1')


/* 4. Memory assigned to SQL */
SET @MaxSQLMemory=(select CAST(value_in_use as int)/1024
FROM sys.configurations
WHERE name ='max server memory (MB)')


/* 5. Number of NUMA Nodes */
SET @NUMACount=(select max(parent_node_id)+1
from sys.dm_os_schedulers
where status ='VISIBLE ONLINE')


/* 6. Schedulers per NUMA */
SET @CPUCount=( SELECT max(scheduler_id)+1
from sys.dm_os_schedulers
where status ='VISIBLE ONLINE')


/***
/* 7. Disk and CPU Aggregates*/
;WITH disk AS (
SELECT	SUM(sub.DiskMin) AS DiskMin,
		SUM(sub.DiskMax) AS DiskMax,
		SUM(sub.DiskAvg) AS DiskAvg,
		MachineName
FROM (
SELECT
		 MachineName ,
		MIN(CounterValue) AS DiskMin,
        MAX(CounterValue) AS DiskMax,
        AVG(CounterValue) AS DiskAvg
FROM    dbo.CounterDetails cdt
        INNER JOIN dbo.CounterData cd ON cdt.CounterID = cd.CounterID
        INNER JOIN dbo.DisplayToID d ON d.GUID = cd.GUID
WHERE
        ObjectName in ('LogicalDisk')
        AND cdt.CounterName IN( 'Disk Writes/sec','Disk Reads/sec')
        AND cdt.InstanceName = '_Total'
GROUP BY MachineName ,
        CounterName ,
        InstanceName ,
        DisplayString ) sub
		GROUP BY MachineName
		)
, cpu AS
(SELECT
		 MachineName ,
		MIN(CounterValue) AS cpuMin,
        MAX(CounterValue) AS cpuMax,
        AVG(CounterValue) AS cpuAvg
FROM    dbo.CounterDetails cdt
        INNER JOIN dbo.CounterData cd ON cdt.CounterID = cd.CounterID
        INNER JOIN dbo.DisplayToID d ON d.GUID = cd.GUID
WHERE
        ObjectName in ('Processor')
        AND cdt.CounterName IN( '% Processor Time')
        AND cdt.InstanceName = '_Total'
GROUP BY MachineName ,
        CounterName ,
        InstanceName ,
        DisplayString )
SELECT
	disk.MachineName,
	@UsedDataGB   AS 'Total Data Space GB',
	@UsedLogGB   AS 'Total Log Space GB',
	@TotalMemoryGB AS 'Total Memory GB',
	@MaxSQLMemory  AS 'Max SQL Server Memory GB',
	@NUMACount	   AS 'NUMA Count',
	@CPUCount	   AS 'CPU Count',
	@Ratio 		   AS 'Data GB to Memory GB Ratio',
	DiskMin as 'Min IOPS',
	DiskMax as 'Max IOPS',
	DiskAvg as 'Avg IOPS',
	CPUMin  as 'Min CPU',
	CPUMax  as 'Max CPU',
	CPUAvg  as 'Avg CPU'
FROM disk
JOIN cpu ON disk.MachineName=cpu.MachineName
***/