Wednesday, August 31, 2016

Parsing "DBCC MEMORYSTATUS" without using PowerShell

When I wanted to research memory problem on a server and started to dig deeper into "DBCC MEMORYSTATUS" command.Very useful links to understand that command were from Microsoft:
https://support.microsoft.com/en-us/kb/271624
https://support.microsoft.com/en-us/kb/907877

During the research I've faced two problems:
1. I had to wait several seconds to get the full result set.
2. I had to scroll down and shuffle 115 different data sets to find the counter I want.

To eliminate both these problem all these different counters have to be in the same table/data set.
That will make research easier and data can be stored or compared with the base line.

So in order to achieve that we have to parse results of "DBCC MEMORYSTATUS".

I've did a quick search over the internet and found several parsing solutions, but all of them are based on PowerShell.
There is no problem with PowerShell for me, but the whole Idea seems wrong.
It is like if I'm asking for driving direction I can get the answer only in Chinese.

Being in frustration, I've decided to make it available in native SQL language.
So, here is the script:
SET NOCOUNT ON
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 = 1
 , @i SMALLINT = 1
 , @m SMALLINT = (SELECT MAX(ID) FROM #tbl_MemoryStatusDump)
 , @CurSet VARCHAR(100)
 , @CurMeasure VARCHAR(20)
 , @Divider TINYINT
 , @CurCounter VARCHAR(100)
 , @CurValue VARCHAR(20);

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 += 1;
END
GO
SELECT * FROM #tbl_MemoryStatus
GO


Side notes:


1. Script uses "xp_cmdshell" command, which can be disabled on your server. In case you've got to the point when you are using "DBCC MEMORYSTATUS" command you have to have enough privileges to turn it on/off. Also, there are plenty of resources in the Internet on "how to enable xp_cmdshell".

2. Within "xp_cmdshell" command I use trusted connection "-E" and default local server: "-S localhost". These parameters can be changed adjusting for your instance/environment.

3. I've tested it on SQL Server 2014, but I'm pretty sure the script is supposed to work from 2008 to 2016. For SQL Server 2005 it would require some adjustments.

4. For the Value column I've used "MONEY" data type, which is limiting amount of "Available Virtual Memory" by 838 TB. There is possibility to use BIGINT, but "Memory Broker Clerk (Buffer Pool)" returns decimal values and that might be the problem. If you'd like you can use value data type as "DECIMAL(38,6)", but that I think would be the extreme case, which you might like to catch.

1 comment:

  1. Thanks, that was super useful in a late-night troubleshooting session.

    ReplyDelete