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.
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 value
A table would log the Memory usage if you schedule this script as a job from SQL agent. Please find the image below for reference
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.
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