Parse Output From DBCC MEMORYSTATUS

[Ulysses]

Is there any existing source code or T-SQL script that can place the output
from DBCC MEMORYSTATUS into a table?
If not...
I need to have the output from DBCC MEMORYSTATUS (where the output pattern
is 7 rows - see source example below) placed into tables with [1] the rows
from the first column of the output placed into columns and [2] the rows
from the n+1 column(s) of the output placed as row(values) for the column
(created in [1]). Each result table should look like this (ignore the |
which signifies a new column)

Table: memoryclerk_sqlgeneral
Columns: vm reserved | vm committed | awe allocated | sm reserved......
Rows: 0 | 0 | 0 | 0....

Source Example:
MEMORYCLERK_SQLGENERAL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2832
MultiPage Allocator 2544

(7 row(s) affected)

MEMORYCLERK_SQLBUFFERPOOL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 1636280
VM Committed 1636280
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 400

(7 row(s) affected)

MEMORYCLERK_SQLQUERYEXEC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 56

(7 row(s) affected)

Any help will be appreciated.

[Bob]

I had a crack at this, but it looks a bit tricky ! This solution is SQL2005
only.

DROP TABLE #tmp
GO
CREATE TABLE #tmp ( row_id INT IDENTITY PRIMARY KEY, description
VARCHAR(100), KB INT )
GO

INSERT INTO #tmp
EXEC ( 'DBCC MEMORYSTATUS' )
GO

-- Add columns
ALTER TABLE #tmp ADD group_no INT
ALTER TABLE #tmp ADD group_row_id INT
GO

-- Clean the input
UPDATE #tmp SET description = RTRIM(LTRIM(description))

-- All groups start with 'VM Reserved' etc
UPDATE #tmp SET group_row_id = 1
WHERE description In( 'VM Reserved', 'Stolen', 'Committed', 'TotalProcs',
'Resource', 'Grants', 'Overall Memory', 'Configured Units', 'Allocations' )
GO

;WITH cte AS
(
SELECT ROW_NUMBER() OVER( ORDER BY a.row_id ) AS group_no, MIN( a.row_id )
AS min_row_id, MIN( b.row_id ) - 1 AS max_row_id
FROM #tmp a CROSS JOIN #tmp b
WHERE a.group_row_id = 1
AND b.group_row_id = 1
AND b.row_id > a.row_id
GROUP BY a.row_id
)
UPDATE t
SET t.group_no = ISNULL( cte.group_no, x.group_no )
FROM #tmp t
LEFT JOIN cte ON t.row_id Between cte.min_row_id AND cte.max_row_id
CROSS JOIN ( SELECT MAX( group_no ) + 1 AS group_no FROM cte ) x

SELECT *
FROM ( SELECT group_no, description, KB FROM #tmp ) t
PIVOT ( SUM( KB ) For description In ( [VM Reserved], [VM Committed], [AWE
Allocated], [SM Reserved], [SM Commited], [SinglePage Allocator], [MultiPage
Allocator] ) ) pvt
WHERE group_no In ( SELECT group_no FROM #tmp GROUP BY group_no HAVING
COUNT(*) = 7 )
GO

I'm not sure if you can guarantee how many panes will return from DBCC
MEMORYSTATUS and what the headers will be. If you always know what it's
going to be then I guess you could create a table and link it to my group_no
column.

HTH
wBob

[Charles Wang [MSFT]]

Hi Ulysses,
In addition to Bob's kind suggestion, you can also use C# and ADO.NET to
first retrieve the results and then write the result to new tables. I write
some code snippets here for your reference.
================================================
DataSet ds = new DataSet();
SqlConnection cn=new
SqlConnection(@"server=myserver;database=AdventureWorks;integrated
security=SSPI");
SqlDataAdapter adp = new SqlDataAdapter("DBCC MEMORYSTATUS",
cn);
adp.Fill(ds);
for(int i=0;i<ds.Tables.Count;++i)
{
string strSQL = "IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].["+ ds.Tables[i].Columns[0].ColumnName
+"]') AND type in (N'U'))\n"
+"DROP TABLE [dbo].["+
ds.Tables[i].Columns[0].ColumnName +"]\n"
+"CREATE TABLE [" +
ds.Tables[i].Columns[0].ColumnName + "]\n (";
string strDefs = "", strValues = "";
foreach (DataRow row in ds.Tables[i].Rows)
{
strDefs +="["+ row[0].ToString() + "] int,";
strValues += row[1].ToString()+",";
}
strDefs = strDefs.TrimEnd(',');
strValues = strValues.TrimEnd(',');
strSQL += strDefs + ")\n";
strSQL += "INSERT INTO [" +
ds.Tables[i].Columns[0].ColumnName + "] VALUES(" + strValues + ")";

SqlCommand cmd = new SqlCommand(strSQL);
cn.Open();
cmd.Connection = cn;
try
{
cmd.ExecuteNonQuery();

}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
}
finally {
cn.Close();
}
==================================================

If you have any other questions or concerns, please feel free to let me
know. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msd...@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

[Charles Wang [MSFT]]

Hi Bob,
I am interested in this issue. Could you please let me know the issue
status? If you need further assistance, please feel free to let us know.

Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================

Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msd...@microsoft.com.
=========================================================

[Ulysses]

Thanks Bob I appreciate your time and efforts to help me. Your solution has
given me ideas as a starting point. Your solution is not exactly what I had
in mind but it is close. I will try to tweak your code to get closer to my
goal. As time permits I may post the final solution here.