Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Monitor CPU and Memory usage for all SQL Server instances using PowerShell


By:   |   Last Updated: 2019-01-07   |   Comments (4)   |   Related Tips: More > Monitoring

Problem

Whether you have inherited a set of servers or eventually will support them in the future, you will want to know the resource consumption for each server. In this tip we will look at how to quickly get information regarding CPU and memory usage for each of your SQL Server instances.

Solution

You probably have heard of or seen several 3rd party solutions that have features to gather this information. The spirit of this series is aimed at providing a simple solution that you can use immediately if you have nothing in place to track resource usage in your environment.

To get started, we will look at the query we will use to gather this information, which will then be used in a PowerShell script so you can gather information easily from all of your instances.

Query to Gather CPU and Memory Information

This is the query we will use inside the PowerShell script, this can be run on its own in a query window.  Further down in the tip I explain each of the output columns and how to use this inside a PowerShell script.

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 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'

PowerShell Script to Gather CPU and Memory Information

Here is the complete code of the PowerShell script that gathers CPU and Memory usage information from the instances you specify.

To help things out, here is a sample table that you can use to populate the list of instances to check. If you already have a table with your list of instances you can use that instead and specify it in PowerShell code below.  After you create the table, you can add the list of instances.

CREATE TABLE instances (
   name varchar(100), 
   version varchar(100), 
   instance varchar(100) 
)

As usual with all the scripts I present, you must make certain modifications to some parameters to fit your environment.

$server = "XXX"
$inventoryDB = "XXX"

#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,
   [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

#Fetch all the instances under your care
/*
   This is an example of the result set that your query must return
   ########################################################################
   # name                     # version             # instance            #
   ########################################################################
   # server1.domain.net,45000 # SQL Server 2016 RTM # server1             #
   # server1.domain.net,45001 # SQL Server 2016 SP1 # server1\MSSQLSERVER1# 
   # server2.domain.net,45000 # SQL Server 2014 SP2 # server2             #
   # server3.domain.net,45000 # SQL Server 2014 SP1 # server3             #
   # server4.domain.net       # SQL Server 2012 SP3 # server4\MSSQLSERVER2#
   ########################################################################
				   
   Make sure that your result set only contains instances using SQL Server 2008 and beyond.
   The reason is that there are some System DMVs not available in SQL Server 2005 and below. 
*/

/*Put in your query that returns the list of instances as described in the example result set above*/
$instanceLookupQuery = "SELECT name, version, instance FROM instances" 
$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 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'
"

#For each instance, grab the CPU/RAM usage information
foreach ($instance in $instances){
   Write-Host "Fetching CPU/RAM information for instance" $instance.instance
   $results = Invoke-Sqlcmd -Query $resourcesQuery -ServerInstance $instance.name -ErrorAction Stop -querytimeout 30
   
   #Build the 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['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!" 

After you execute the code above, you will have an output that contains the following information for each instance.  This will get inserted into table CPU_Memory_Usage.  I'm showing the values of the fields in a vertical way because the width of the result set doesn't allow for a clear/legible visualization.

Instance server1\MSSQLSERVER1
Max Server Memory 2147483647
SQL Server Memory Usage (MB) 120
Physical Memory (MB) 7971
Available Memory (MB) 1237
System Memory State Available physical memory is high
Page Life Expectancy 721
SQLProcessUtilization30 25
SQLProcessUtilization15 20
SQLProcessUtilization10 10
SQLProcessUtilization5 10
Data Sample Timestamp 2018-11-16 17:31:47.927

Here's a brief description for each field (most of them are quite obvious):

  • Instance: The name of the SQL Server instance.
  • Max Server Memory: The current value set in the instance.
  • SQL Server Memory Usage (MB): How much memory the SQL Server process is using.
  • Physical Memory (MB): How much memory is usable by the OS.
  • Available Memory (MB): How much memory is available to be used in the entire server.
  • System Memory State: Brief descriptor of the state of the memory, in terms of usage/availability.
  • Page Life Expectancy: PLE at the time the data was sampled.
  • SQLProcessUtilization30, SQLProcessUtilization15, SQLProcessUtilization10 and SQLProcessUtilization5: When I'm using Linux, to take a look at the list of processes and load in the system, I have always liked to use htop. Htop has a section called "load average" that shows 3 values (1 minute load average, 5 minutes load average, 15 minutes load average). Therefore, SQLProcessUtilization30 shows the average CPU usage in the past 30 minutes (the exact same thing applies for the rest of the SQLProcessUtilizationX fields).
  • Data Sample Timestamp: This is simply the timestamp when the sample was captured.

Note: Max Server Memory limits only the buffer pool allocation. However, additional memory can be used to store the following (just to name a few):

  • COM objects
  • Extended Stored Procedures
  • SQLCLR
  • Memory allocated by Linked Servers

You can add as many counters as you want and/or modify the structure of the end result. Remember that the spirit of this tip is to give you a starting point in case you're not using any 3rd party tool or a custom script to monitor these basic values in your environment.

Next Steps
  • Ideally you will want to create a job and run this presented script every 30 minutes (or whatever you want). Remember to be careful about the amount of data you end up with, which will depend on the frequency of the job and the number of instances under your care.
  • You can also build a custom monitoring strategy, based on the data you collect.
  • Here's the official documentation from Microsoft for the DMV used in the script presented within this tip.
  • The next part of this series will focus on relevant information of all the disk drives, in each server under your care, that host database/transaction log data files.


Last Updated: 2019-01-07


next webcast button


next tip button



About the author
MSSQLTips author Alejandro Cobar Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, January 10, 2019 - 10:46:51 AM - Alejandro Cobar Back To Top

Hello Raju!

Let's assume that you have gone through my tip and that you are already collecting/storing the information in a table.

*Here are some useful resources, published by members of the MSSQLTips community, that can surely guide you through the process for getting email notifications:

https://www.mssqltips.com/sqlservertip/1100/setting-up-database-mail-for-sql-server/

https://www.mssqltips.com/sqlservertip/2551/automate-sql-server-monitoring-with-email-alerts/

https://www.mssqltips.com/sqlservertip/1523/how-to-setup-sql-server-alerts-and-email-operator-notifications/

If you don't find these good enough, you can wander around MSSQLTips to keep looking form even more resources available on this matter. I'm sure you will be able to pull it off because you are in the right place when it comes to finding information on "how to get things done in SQL Server".


Thursday, January 10, 2019 - 5:11:34 AM - RajuG Back To Top

Hi Alejandro Cobar,  I am new to sql server adminstration. In my environment I need to configure alert notification on production server. How do I can get mail notification on these information.


Tuesday, January 08, 2019 - 12:01:56 PM - Alejandro Cobar Back To Top

Interesting, thank you for your feedback on this.

Of course this approach won't be 100% accurate, but it can give you a rough idea (specially when you are absolutely not doing anything to keep track of these values in any way). I know there are dozens more ways to do the same thing, but I think it is nice to have 1 more around :)


Monday, January 07, 2019 - 4:59:07 PM - jeff_yao Back To Top

The issue with this approach is that sys.dm_os_ring_buffers's log of the CPU data has a granularity of 1 minute (i.e. 60 seconds), this may not good enough for those short lived CPU spikes. Glenn Berry has a blog post for this DMV here https://sqlserverperformance.wordpress.com/2010/04/20/a-dmv-a-day-%E2%80%93-day-21/


Learn more about SQL Server tools