How to Log and Monitor Your Server’s Memory Usage without Using Third Party Tools

Stop buying third party tools to monitor Memory usage on SQL Servers. Using this TSQL Script, you can log server’s Memory usage. You can also set email alerts accordingly.

Why DBA should Monitor Memory Usage?

Generally a DBA’s mailbox will be flooded with emails whenever end users are unable to work on a server or if all the task are taking too much time. With a quick check at the Server’s resource usage, you can determine that the reason for this problem is the server’s Memory. The CPU usage might look normal but if the memory usage is at its maximum, the SQL Server’s performance will drastically decrease.

You can easily identify which database in your SQL instance is consuming memory with the help of several scripts that are available on the internet. Nevertheless it is a good practice to proactively monitor a SQL Server’s memory usage and avoid major performance issues. This will also help to ensure that enough memory is available on the Server for a SQL service.

SQL Script

This script will read and log the memory usage on a local machine. To collect the memory usage of a remote server, just replace the systeminfo in the script to “systeminfo [/s RemoteComputer /u Domain\User /p Password

Before using this script Please make sure that you have enabled cmdshell and DBmail in your SQL instance and update place holders in this script with correct server name, threshold value for memory usage, DBmail profile name and recipient list

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
RECONFIGURE;  
GO 
DECLARE @Count INT
DECLARE @pmv VARCHAR(1000), @pm varchar(1000), @am varchar(1000), @amv varchar(1000)
declare @pmvint float, @amvint float
declare @v_subject as varchar(1000)
create table temp1
(
c1 varchar(1000)
)
if not exists (select * from sysobjects where name='Memorylog' and xtype='U')
create table Memorylog (Log_Time datetime,Memory_percentage int);
insert into temp1
exec master.dbo.xp_cmdshell 'systeminfo'
SET @pm = (select * from temp1
where c1 like '%Total Physical Memory:%')
SET @am = (select * from temp1
where c1 like '%Available Physical Memory:%')
SET @Count = 0
SET @pmv = ''
set @amv = ''
WHILE @Count <= LEN(@pm)
BEGIN
IF SUBSTRING(@pm,@Count,1) >= '0'
AND SUBSTRING(@pm,@Count,1) <= '9'
BEGIN
SET @pmv = @pmv + SUBSTRING(@pm,@Count,1)
END
SET @Count = @Count + 1
END
SET @Count = 0
WHILE @Count <= LEN(@am)
BEGIN
IF SUBSTRING(@am,@Count,1) >= '0'
AND SUBSTRING(@am,@Count,1) <= '9'
BEGIN
SET @amv = @amv + SUBSTRING(@am,@Count,1)
END
SET @Count = @Count + 1
END
print @pmv
print @amv
set @pmvint = CAST(@pmv as float)
set @amvint = CAST(@amv as float)
print (((@pmvint - @amvint) / (@pmvint)) * 100)
insert into Memorylog values(getdate(),(((@pmvint - @amvint) / (@pmvint)) * 100))
select * from Memorylog
set @v_subject = 'Alert ! High Memory Usage on <YOUR SERVER NAME>. Current Memory% --> ' + convert(varchar,(((@pmvint - @amvint) / (@pmvint)) * 100))
if (((@pmvint - @amvint) / (@pmvint)) * 100) > 80 begin
EXEC msdb.dbo.sp_send_dbmail 
  @profile_name='<DBMAIL PROFILE NAME>',
  @recipients='RECIPIENT LIST. SEPARATE MULTIPLE RECIPIENTS BY ;',
  @subject= @v_subject,
  @body='Please check Memory Usage'
end
drop table temp1

As shown below, you will receive a sample email if the memory usage on your SQL Server jumps beyond the threshold valueEmail Alerts Of Memory Usage

A table would log the Memory usage if you schedule this script as a job from SQL agent. Please find the image below for referenceA Table Log The Memory Usage

Out of Memory may Cause Server Crash

When your server runs out of memory, it may crash and your databases on it may get damaged or corrupt. In such a case, a reliable solution for SQL database recovery is very important to DBAs. With an effective solution, you can easily recover from the data disaster and let everything get back on track again.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook damage and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.