Problème
ce script de surveillance PowerShell collectera des données au niveau de l’instance pour tous les serveurs SQL qui ont été enregistrés pour cette solution de surveillance.
Solution
Ce module rassemblera des informations sur l’instance SQL Server, telles RAM ,PLE,cache Hit ration ,CPU l. Il se connectera à chaque serveur de LA TABLE Serveurs et capturera les données pour chacune de ces instances. La connexion à chaque instance SQL Server sera basée sur les paramètres de la table
Ce module rassemblera des informations sur l’instance SQL Server, telles RAM ,PLE,cache Hit ration ,CPU l. Il se connectera à chaque serveur de LA TABLE Serveurs et capturera les données pour chacune de ces instances. La connexion à chaque instance SQL Server sera basée sur les paramètres de la table Serveurs.
Le script PowerShell qui crée les objets ci-dessus et insère des données dans la table nomé CPU_Memory_Usage
Import-Module SqlServer
$server = "COSxxxxxxx"
$inventoryDB = "DBA_TOOLS"
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(10) 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(30) 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 '%%'
) 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
}
Si nous interrogeons la table CPU_Memory_Usage , nous pouvons voir les données qui ont été collectées
Bonne surveillance de Vos instances
DBA SQL Server BLOG 