Techniques to Monitor SQL Server memory usage

Memory has a dramatic impact on SQL Server Performance. Fortunately, in SQL Server you can either use DMVs, , Extended Events, sp_server_diagnostics system procedure or SQL
Profiler to server memory usage and track down root cause of SQL Server memory
bottlenecks. In this article, I
will provide you high-level overview of these memory-related tools.

Dynamic management views

Dynamic management views were first introduced with SQL Server 2005.
They provide a wealth of information on server and database state, which is
useful in monitoring overall SQL Server health, identify the root cause of SQL
Server performance bottlenecks, and tune SQL Server instance or database
performance. The following are the list of dynamic management views that are
available in SQL Server 2008R2 and up which
you can use to obtain SQL Server memory usage information, for example, how
much memory is available to SQL Server instance, or how well SQL Server
instance is utilizing the available memory. 

In addition to this, SQL Server 2014 also has set of dynamic
management views that are specific to new for In-Memory OLTP engine.  You can
use these dynamic management views to monitor the memory-related usage
information that is specific to new In-Memory OLTP engine. For more
information, refer to the Memory-Optimized Table Dynamic Management Views
(Transact-SQL) article at http://msdn.microsoft.com/en-us/library/dn133203.aspx.

DBCC MEMORYSTATUS Command

MEMORYSTATUS is an undocumented DBCC command that provides a snapshot of the
current memory status of Microsoft SQL Server. This memory usage information is
not only useful for you to quickly identify the root cause of the memory
pressure on SQL Server, but also helps you analyze how well SQL Server is using
the allocated memory. Some of the information included in DBCC MEMORYSTATUS
outputs is still not available through dynamic management views, which I
mentioned earlier. That is why; Microsoft Customer Support Services team and
experienced database administrators still rely on this undocumented DBCC
command output to quickly troubleshoot Microsoft SQL Server memory consumption
issues.

The DBCC MEMORYSTATUS describes the distribution of 8k buffers among
various components of SQL Server. It gives you sets of measures that show how
memory is currently allocated in SQL Server. This information is particularly
useful, if you are troubleshooting issues that are related to SQL Server Memory
consumption. Its output contains the following sections:

  • Output contains seven sections:
  • Shows distribution of committed memory
  • Shows size and makeup of buffer pool
  • Describes makeup of the procedure cache
  • Shows distribution of Dynamic Memory Usage
  • Shows distribution of various global memory usage
  • Displays query memory grant information
  • Shows concurrent query optimization status
  • Displays the memory management info like VM Reserved, VM
    Committed, AWE Allocated, Reserved Memory, Reserved Memory In Use.
  • How the SQL Server buffer cache is divided up, including buffer
    activity.
  • Information about Memory Manager, Clerks, Buffer Manager, and
    number of other SQL Server processes that consume SQL Server memory

Syntax

DBCC MEMORYSTATUS

Sample Output

Parsing the Output from DBCC MEMORYSTATUS

The only problem with DBCC MEMORYSTATUS is that – it returns
multiple result-sets that are difficult to consume. Therefore, to simply its
output, I wrote a following PowerShell script that places the outputs from the
different result sets of DBCC
MEMORYSTATUS command into a dbo.DBCCMemoryStatus
user-table inside specified database.

        # Author: Basit A. Farooq
# Date Created: 21/02/2014
# This script is provided "AS IS" with no warranties, and confers no rights.
Function Proccess_DBCCMemoryStatus
{
# Declare powershell script parameters
   Param
   (
[String] $SQLServer,
        [String] $DBName
   )
      Try
      {
$TsqlToExecute = "--Prepare create table statement
SET NOCOUNT ON;
   
IF EXISTS (SELECT *
FROM [sys].[objects]
WHERE [object_id] = OBJECT_ID(N'[dbo].[DBCCMemoryStatus]')
AND [type] IN (N'U'))
                              BEGIN
DROP TABLE [dbo].[DBCCMemoryStatus];
                              END
           
CREATE TABLE [dbo].[DBCCMemoryStatus]
([IDCol]                      [int] identity(1, 1) PRIMARY KEY
,[MemObjectType]              [varchar](512)
,[MemObjectName]              [varchar](512)
,[MemObjectValue]         [varchar](512)
,[MemObjectValueType]       [varchar](32)
,[CheckDate]                [datetime]);"
$TSqlInsertScriptBuilder = New-Object System.Text.StringBuilder
# Prepare the variable containing the connectionstring for the connection
$ConnectionString = "Data Source=$SQLServer;Integrated Security=SSPI;Initial Catalog=$DBName"
# Declare and initialize SqlConnection object
$DBConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
# Open database connection
            $DBConnection.Open()
# Declare SqlCommand object
$TSqlCommand = New-Object System.Data.SqlClient.SqlCommand;
# Set the SqlConnection used by this instance of the SqlCommand
$TSqlCommand.Connection = $DBConnection
$TSqlCommand.CommandTimeout = 15;
$TSqlCommand.CommandText = $TSqlToExecute
   
# Execute to the CREATE TABLE transact-sql script
$TSqlCommand.ExecuteNonQuery() | Out-Null
# Prepare DBCC MEMORYSTATUS command
$TSqlToExecute = "SET NOCOUNT ON; DBCC MEMORYSTATUS WITH TABLERESULTS;"
# Create a Dataset object to hold the DataTable from DBCCMemoryStatus
$SqlDataSet = New-Object "System.Data.DataSet" "DBCCMemoryStatus"
# Create a DataAdapter which you'll use to populate the DataSet with the results
$SqlDataAdapter = New-Object "System.Data.SqlClient.SqlDataAdapter" ($TsqlToExecute, $DBConnection)
$SqlDataAdapter.Fill($SqlDataSet) | Out-Null
# Iterate for every datatable inside DataSet object
foreach ($SqlDataTable in $SqlDataSet.Tables)
            {
$DataTableCount = $SqlDataTable.Rows.Count;
$MemObjectType = $SqlDataTable.Columns[0].ColumnName
$MemObjectValueType = $SqlDataTable.Columns[1].ColumnName
       
for ($i = 0; $i -le $DataTableCount-1; $i++)
                  {
$MemObjectName = $SqlDataTable.Rows[$i][0]
$MemObjectValue = $SqlDataTable.Rows[$i][1]
$TSqlInsertScriptBuilder.Append("INSERT INTO [dbo].[DBCCMemoryStatus]                                                 ([MemObjectType]
      ,[MemObjectName]
      ,[MemObjectValue]
      ,[MemObjectValueType]
      ,[CheckDate])
VALUES ('$MemObjectType', '$MemObjectName', '$MemObjectValue', '$MemObjectValueType', CURRENT_TIMESTAMP);");
}          
            }
   
$TsqlToExecute = "SET NOCOUNT ON; " + $TSqlInsertScriptBuilder.ToString()
$TSqlCommand.Connection = $DBConnection
$TSqlCommand.CommandTimeout = 150;
$TSqlCommand.CommandText = $TSqlToExecute
   
# Execute to copy results to DBCCMemoryStatus table
$TSqlCommand.ExecuteNonQuery() | Out-Null
      }
Catch [System.Exception]
      {
Write-Host "Oops, an error occurred that could not be resolved. For more information, see below:"
            $Error[1]
      }
    Finally
    {
# Close the database connection
$DBConnection.Close() | Out-Null
    }
}

The syntax to execute this PowerShell script is as follows:

Proccess_DBCCMemoryStatus
    "<Specify_SQL_Server_Instance_Name>"
    "<Specify_Database_Where_You_Want_To_Save_Results>"

For example, I executed on my local instance as follows:

 Proccess_DBCCMemoryStatus
    "W7LAPT427" "tempdb"

You can then query this table to find how SQL Server is consuming
memory and whether the server is experiencing a memory pressure.

Extended Events

Extended Events is an event infrastructure that is a highly scalable
and light weight performance monitoring system that uses very few system
resources. You can use system_health Session of an Extended Events to monitor memory related information
such as memory broker, memory node out of memory errors, and so on. For more
information about how to use it, refer to Use the system_health Session article
at http://msdn.microsoft.com/en-us/library/ff877955.aspx.

sp_server_diagnostics

This system stored procedure was first introduced with SQL Server
2012. It captures useful diagnostic data and health information about SQL
Server to detect potential failures. For example, you can use this stored procedure
to capture physical and virtual memory, buffer pools, pages, cache and other
memory objects. For more information about this stored procedure, see its
documentation at SQL Server Books Online site here.

SQL Server Profiler


SQL
Server Profiler
is a graphical user interface that captures SQL Server or
Analysis Services activities depending on the selected events. SQL Server
Profiler captures the events as a trace data, which can be saved to a local
file, network file or inside SQL Server table. You can use SQL Server Profiler
trace to capture memory-intensive queries and workload that is running on SQL
Server instance. You can review the captured information to identify the
memory-intensive queries and stored procedures. You can then optimize these
queries to consume less memory. 




Array

One Response to “Techniques to Monitor SQL Server memory usage”

  1. Nice article . I can learn lot. Thank you so much.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |