SQL Server Parse DBCC MemoryStatus using Powershell

A few days back I was resolving a memory issue which caused sql server to stop responding. I was able to figure out the issue by looking into SQL Server Parse DBCC MemoryStatus output. It was the full text search which was taking lot of memory. However, doing this I realised that examining DBCC MemoryStatus output is tedious and time taking and what if it can be parsed into a table. It will be very easy to find memory culprit then. So, here is a powershell script to parse DBCC MemoryStatus.

The script is very simple and has inline comments so it won’t be difficult to understand.

<#
 Function to parse DBCC MEMORYSTATUS
 Author:  Ahmad Osama
 Date:  19/05/2013
 This script is provided "AS IS" with no warranties, and confers no rights.
#>
 
function Parse-DBCCMemoryStatus
{
param(
    [string]$inst,[string]$database
    )
             
            # drop and create DBCCMEMORYSTATUS table
            $oConn = New-Object -TypeName System.Data.SqlClient.SqlConnection
            $oConn.ConnectionString = "Server=" + $inst+ ";Database=" + $database + 
                                ";Integrated Security=True"
 
            $oConn.Open()
            $Cmd = New-Object -TypeName system.data.sqlclient.sqlcommand
            $sSQL = "IF OBJECT_ID('DBCCMEMORYSTATUS') IS NOT NULL 
                       BEGIN
                       DROP TABLE DBCCMEMORYSTATUS 
                       END
                       Create table DBCCMemoryStatus
                       (
                           Sno int identity,
                           ObjectType varchar(100),
                           ObjectsubType varchar(100),
                           ObjectValue int,
                           TimeStamp datetime
                       )"
            $cmd.Connection = $oConn
            $Cmd.CommandText = $sSQL
	            $Cmd.ExecuteNonQuery()
	 
            # execute dbcc memorystatus and get result in a dataset
            $cmd = New-Object -TypeName system.data.sqlclient.sqlcommand
            $cmd.Connection = $oConn
            $Cmd.CommandText = "DBCC MEMORYSTATUS"
            $dataSet = new-object "System.Data.DataSet" "DBCCMemoryStatus"
            $dataAdapter = new-object "System.Data.SqlClient.SqlDataAdapter" $cmd
            $dataAdapter.Fill($dataSet) | Out-Null
             
            $insertquery=new-object System.Text.StringBuilder
             
            #iterate through datatable in datasets
            foreach($datatable in $dataset.Tables)
            {
                $dtrowcount = $datatable.Rows.Count;
                # get the Memory object type - "Memory Manager etc.."
                $objectType = $datatable.Columns[0].ColumnName
                for ($counter = 0;$counter -le $dtrowcount-1; $counter++)
                {
                    # get sub type - "VM Reserverd.. etc"
	                    $objectsubtype = $datatable.Rows[$counter][0]
                    # get the object type value - Column "KB"
                    $objectValue = $datatable.Rows[$counter][1]
                     
                    # append the insert queries for each of datatable.
                    $insertquery.Append("INSERT INTO DBCCMEMORYSTATUS(ObjectType,ObjectsubType,ObjectValue,TimeStamp) 
                        VALUES('$objectType','$objectsubtype','$objectValue',GetDate())");
                         
                }
                 
            }
             
            # insert records in dbcc memorystatus table.
            $cmd = New-Object -TypeName system.data.sqlclient.sqlcommand
            $cmd.Connection = $oConn
            $Cmd.CommandText = $insertquery.tostring()
            $Cmd.ExecuteNonQuery()   
}
	#pass server and database to execute.
Parse-DBCCMemoryStatus "AHMAD-PC\SQL2005" "master"

Hope this helps in solving memory issues.

 
Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook