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
121
| Import-Module SqlServer
$server = "xxxxxx"
$inventoryDB = "xxxxx"
#This is the definition of the table that will contain the values for each instance you wish to collect information from
$resourcesUsageTable = "
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'CPU_Memory_Usage' AND xtype = 'U')
CREATE TABLE CPU_Memory_Usage(
[server] [varchar](128) NOT NULL,
[max_server_memory] [int] NOT NULL,
[sql_memory_usage] [int] NOT NULL,
[physical_memory] [int] NOT NULL,
[available_memory] [int] NOT NULL,
[system_memory_state] [varchar](255) NOT NULL,
[page_life_expectancy] [int] NOT NULL,
[cache hit ratio] NUMERIC(10,6) NOT NULL,
[cpu_usage_30] [int] NOT NULL,
[cpu_usage_15] [int] NOT NULL,
[cpu_usage_10] [int] NOT NULL,
[cpu_usage_5] [int] NOT NULL,
[data_sample_timestamp] [datetime] NULL
) ON [PRIMARY]
"
#Make sure you create this table in your central environment, where you wish to gather the information from all the desired instances
$instances = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query $resourcesUsageTable
#/* Récupérer List Serveur Pour leur parcours */
$instanceLookupQuery = "SELECT name FROM servers"
$instances = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query $instanceLookupQuery
$resourcesQuery = "
WITH SQLProcessCPU
AS(
SELECT TOP(30) SQLProcessUtilization AS 'CPU_Usage', ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS 'row_number'
FROM (
SELECT
record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization],
[timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%'
) AS x
) AS y
)
SELECT
SERVERPROPERTY('SERVERNAME') AS 'Instance',
(SELECT value_in_use FROM sys.configurations WHERE name like '%max server memory%') AS 'Max Server Memory',
(SELECT physical_memory_in_use_kb/1024 FROM sys.dm_os_process_memory) AS 'SQL Server Memory Usage (MB)',
(SELECT total_physical_memory_kb/1024 FROM sys.dm_os_sys_memory) AS 'Physical Memory (MB)',
(SELECT available_physical_memory_kb/1024 FROM sys.dm_os_sys_memory) AS 'Available Memory (MB)',
(SELECT system_memory_state_desc FROM sys.dm_os_sys_memory) AS 'System Memory State',
(SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Manager%' AND [counter_name] = 'Page life expectancy') AS 'Page Life Expectancy',
(SELECT CAST(
(
SELECT CAST (cntr_value AS BIGINT)
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio'
)* 100.00
/
(
SELECT CAST (cntr_value AS BIGINT)
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
) AS NUMERIC(6,3)
))as [cache hit ratio] ,
(SELECT AVG(CPU_Usage) FROM SQLProcessCPU WHERE row_number BETWEEN 1 AND 30) AS 'SQLProcessUtilization30',
(SELECT AVG(CPU_Usage) FROM SQLProcessCPU WHERE row_number BETWEEN 1 AND 15) AS 'SQLProcessUtilization15',
(SELECT AVG(CPU_Usage) FROM SQLProcessCPU WHERE row_number BETWEEN 1 AND 10) AS 'SQLProcessUtilization10',
(SELECT AVG(CPU_Usage) FROM SQLProcessCPU WHERE row_number BETWEEN 1 AND 5) AS 'SQLProcessUtilization5',
GETDATE() AS 'Data Sample Timestamp'
"
Try
{
#Parcours Lists serveur par Foreach
foreach ($instance in $instances){
Write-Host "Fetching CPU/RAM information for instance" $instance.name
$results = Invoke-Sqlcmd -Query $resourcesQuery -ServerInstance $instance.name -ErrorAction Stop -querytimeout 30
#Insert statement
if($results.Length -ne 0){
$insert = "INSERT INTO CPU_Memory_Usage VALUES"
foreach($result in $results){
$insert += "
(
'"+$result['Instance']+"',
"+$result['Max Server Memory']+",
"+$result['SQL Server Memory Usage (MB)']+",
"+$result['Physical Memory (MB)']+",
"+$result['Available Memory (MB)']+",
'"+$result['System Memory State']+"',
"+$result['Page Life Expectancy']+",
"+$result['cache hit ratio']+",
"+$result['SQLProcessUtilization30']+",
"+$result['SQLProcessUtilization15']+",
"+$result['SQLProcessUtilization10']+",
"+$result['SQLProcessUtilization5']+",
GETDATE()
),
"
}
#Perform the INSERT in the central table
Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $server -Database $inventoryDB
}
}
Write-Host "Done!"
}
catch
{
write-host "Error de connectivité" $instance.name
} |
