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.
- sys.dm_os_sys_info: Returns computer resources usage information
- sys.dm_os_memory_cache_counters: Returns run-time information about the cache entries allocated, their use, and the source of memory for the cache entries
- sys.dm_os_memory_nodes: Returns information about the memory nodes
- sys.dm_os_memory_cache_entries: Returns original and current cost of any cache entry
- sys.dm_os_memory_cache_hash_tables: Returns a row for each active cache in the instance of SQL Server
- sys.dm_os_memory_cache_clock_hands: Returns the status of each hand for a specific cache clock
- sys.dm_os_memory_clerks: Returns detailed information about all memory clerks, which SQL Server is currently consuming
- sys.dm_os_memory_objects: Returns information about the memory objects that are currently allocated by the instance of SQL Server
- sys.dm_os_performance_counters: Returns SQL Server specific performance counter information
- sys.dm_os_ring_buffers: Returns detailed information about system health. The information returned is useful to troubleshoot memory pressure.
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.
]]>