In that post I talk about a troubleshooting scenario that finally led to using DBCC MEMORYSTATUS to find one of the memory clerks (MEMORYCLERK_XE) consuming far too much memory on the instance. (In that case there was a runaway XEvents session burning lots of RAM).
Bottom line, you should troubleshoot which clerk was the busiest to find the source of the overall problem.
 |
| https://dominicantoday.com/wp-content/uploads/2017/09/a-4.jpg |
I told him about my previous situation and how looking at DBCC MEMORYSTATUS had led to my smoking gun, and it led to thinking about some way to collect and persist the MEMORYSTATUS data without someone watching live on the system.
Google led me to several variations of a #YouCanDoAnythingWithPowershell script, such as this one from Microsoft Premier Field Engineer (PFE) Tim Chapman (blog/@chapmandew) but I really wanted a Transact-SQL script I could play with myself.
More Google finally led me to a blog post and T-SQL script from the SQL Server Photographer Slava Murygin (blog/@slavasql) (follow his blog for scripts and great photos he takes at all types of SQL events)!
Slava's script translates one of the Powershell scripts into T-SQL, storing the output from DBCC MEMORYSTATUS in a temporary table for output. The one limitation of this for my needs is that it is an adhoc execution - great to run interactively but not useful to store over time.
It took a few steps to turn Slava's temp table implementation into one that stores the data in a permanent table so that it can be queried after the fact and over time.
A few notes: Slava's script relies on xp_cmdshell, and my modification still does. My modification stores data in a permanent table, which means it needs to reside in a permanent database. My script uses the "DBADatabase" including code to create it if it doesn't exist, but it is an easy find-replace to change that name if you'd like:
--
/*
Track DBCC MEMORYSTATUS data over time
Guts of query to parse DBCC results into a temp table from
http://slavasql.blogspot.com/2016/08/parsing-dbcc-memorystatus-without-using.html
Modified to store those results in a permanent table over time
Intended for use in a scheduled Agent Job but could be run manually as needed
Also modified to run on SQL 2005 (syntax changes)
Relies on xp_cmdshell
Stores data in the "DBADatabase" including creating the database if it doesn't pre-exist
If you wish to use an existing database or a different database
name simply Find-Replace for the string DBADatabase
*/
SET NOCOUNT ON
GO
IF DB_ID('DBADatabase') IS NULL /* Check if DBADatabase database exists - if not, create it */
BEGIN
EXECUTE ('CREATE DATABASE DBADatabase')
ALTER DATABASE DBADatabase SET RECOVERY SIMPLE;
ALTER AUTHORIZATION ON DATABASE::DBADatabase TO sa;
/* Read the current SQL Server default backup location */
DECLARE @BackupDirectory NVARCHAR(100)
EXEC master..xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'Software\Microsoft\MSSQLServer\MSSQLServer',
@value_name = 'BackupDirectory', @BackupDirectory = @BackupDirectory OUTPUT ;
EXECUTE ('BACKUP DATABASE DBADatabase to DISK = '''+@BackupDirectory+'\DBADatabase.bak'' WITH INIT')
PRINT 'CREATED DATABASE'
RAISERROR('Ensure that you add the DBADatabase database to backup / maintenance jobs/plans', 10, 1) WITH NOWAIT;
END;
GO
/* If Holding Table doesn't exist, create it */
IF OBJECT_ID('DBADatabase.dbo.DBCCMemoryStatus') IS NULL
CREATE TABLE [DBADatabase].[dbo].[DBCCMemoryStatus](
[Datestamp] [datetime] NOT NULL,
[DataSet] [varchar](100) NULL,
[Measure] [varchar](20) NULL,
[Counter] [varchar](100) NULL,
[Value] [money] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
IF OBJECT_ID('tempdb..#tbl_MemoryStatusDump') IS NOT NULL
DROP TABLE #tbl_MemoryStatusDump;
GO
IF OBJECT_ID('tempdb..#tbl_MemoryStatus') IS NOT NULL
DROP TABLE #tbl_MemoryStatus;
GO
CREATE TABLE #tbl_MemoryStatusDump(
ID INT IDENTITY(1,1) PRIMARY KEY
, [Dump] VARCHAR(100));
GO
CREATE TABLE #tbl_MemoryStatus(
ID INT IDENTITY(1,1),
[DataSet] VARCHAR(100),
[Measure] VARCHAR(20),
[Counter] VARCHAR(100),
[Value] MONEY);
GO
INSERT INTO #tbl_MemoryStatusDump([Dump])
EXEC ('xp_cmdshell ''sqlcmd -E -S localhost -Q "DBCC MEMORYSTATUS" ''');
GO
DECLARE @f BIT
, @i SMALLINT
, @m SMALLINT
, @CurSet VARCHAR(100)
, @CurMeasure VARCHAR(20)
, @Divider TINYINT
, @CurCounter VARCHAR(100)
, @CurValue VARCHAR(20);
SET @f=1
SET @m = (SELECT MAX(ID) FROM #tbl_MemoryStatusDump)
set @i = 1
WHILE @i < @m
BEGIN
SELECT @Divider = PATINDEX('% %',REVERSE(RTRIM([Dump])))
, @CurCounter = LEFT([Dump], LEN([Dump]) - @Divider)
, @CurValue = RIGHT(RTRIM([Dump]), @Divider - 1)
FROM #tbl_MemoryStatusDump WHERE ID = @i;
IF @f = 1
SELECT @CurSet = @CurCounter, @CurMeasure = @CurValue, @f = 0
FROM #tbl_MemoryStatusDump WHERE ID = @i;
ELSE IF LEFT(@CurCounter,1) = '(' SET @f = 1;
ELSE IF @CurCounter != 'NULL' and LEFT(@CurCounter,1) != '-'
INSERT INTO #tbl_MemoryStatus([DataSet], [Measure], [Counter], [Value])
SELECT @CurSet, @CurMeasure, @CurCounter, CAST(@CurValue as MONEY)
FROM #tbl_MemoryStatusDump WHERE ID = @i;
SET @i = @i + 1;
END
GO
/*Send data from temp table to permanent table */
INSERT INTO DBADatabase.dbo.DBCCMemoryStatus
SELECT
GETDATE() as Datestamp
, DataSet
, Measure
, Counter
, Value
FROM #tbl_MemoryStatus
/* Purge rows older than 96 hours to manage table size */
DELETE FROM DBADatabase.dbo.DBCCMemoryStatus
WHERE DATEDIFF(hh,DateStamp, GETDATE())>96
/*
SELECT *
FROM DBADatabase.dbo.DBCCMemoryStatus
WHERE counter = 'VM Reserved'
ORDER BY DateStamp DESC
*/
--
Running this statement interactively doesn't return any data - it just loads the data into DBADatabase.dbo.DBCCMemoryStatus. Running the commented-out SELECT at the bottom of the script as written will query that table for all rows of counter VM Reserved (virtual memory reserved) but there is much more data than that available if you modify the SELECT.
This query can be dropped into a SQL Agent job step as is and it will run - just like the interactive run it will create the database and permanent table if they don't exist and then store those nuggets of data into the permanent table for later use - you never know when you may need them!
 |
| https://memegenerator.net/img/instances/400x/57081575/winter-is-coming.jpg |
--
Hope this helps!
