# SQL Server 2012 SP3 # server4\MSSQLSERVER2# This is an example of the result set that your query must return $instances = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query $resourcesUsageTable #Make sure you create this table in your central environment, where you wish to gather the information from all the desired instances IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'CPU_Memory_Usage' AND xtype = 'U') #This is the definition of the table that will contain the values for each instance you wish to collect information from The table, you can add the list of instances. If you already have a table with your list of instances youĬan use that instead and specify it in PowerShell code below.
To help things out, here is a sample table that you can use to populate the list Usage information from the instances you specify. Here is the complete code of the PowerShell script that gathers CPU and Memory
PowerShell Script to Gather CPU and Memory Information (SELECT AVG(CPU_Usage) FROM SQLProcessCPU WHERE row_number BETWEEN 1 AND 5) AS 'SQLProcessUtilization5', (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 15) AS 'SQLProcessUtilization15', (SELECT AVG(CPU_Usage) FROM SQLProcessCPU WHERE row_number BETWEEN 1 AND 30) AS 'SQLProcessUtilization30',
(SELECT FROM sys.dm_os_performance_counters WHERE LIKE '%Manager%' AND = 'Page life expectancy') AS 'Page Life Expectancy', (SELECT system_memory_state_desc FROM sys.dm_os_sys_memory) AS 'System Memory State', (SELECT available_physical_memory_kb/1024 FROM sys.dm_os_sys_memory) AS 'Available Memory (MB)', (SELECT total_physical_memory_kb/1024 FROM sys.dm_os_sys_memory) AS 'Physical Memory (MB)', (SELECT physical_memory_in_use_kb/1024 FROM sys.dm_os_process_memory) AS 'SQL Server Memory Usage (MB)', (SELECT value_in_use FROM sys.configurations WHERE name like '%max server memory%') AS 'Max Server Memory', SERVERPROPERTY('SERVERNAME') AS 'Instance', WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' Record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)', 'int') AS , Record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)', 'int') AS , SELECT TOP(30) SQLProcessUtilization AS 'CPU_Usage', ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS 'row_number'
0 kommentar(er)
