Solved

Help reviewing memory consumption SQL Server 2008 R2

Posted on 2014-10-13
12
195 Views
Last Modified: 2014-10-29
I'd appreciate help reviewing memory consumption on a production SAP CRM database server.  

Attached is an export of Memory Consumption report from SQL.  Does anything stand out to you?

Buffer Pages distribution appears to be huge.  

This server was restarted early Saturday morning.
Memory-Consumption---10132014-349-PM---S
0
Comment
Question by:PetEdge
  • 6
  • 4
  • 2
12 Comments
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40379307
I can't read the file. Which file type is that?

As I know for SAP is that you should follow their recommendations for configuring SQL Server since they don't follow the best practices but their own engineers recommendations.
0
 

Author Comment

by:PetEdge
ID: 40379644
Attached file is .xls.    Attaching again as first one appears to be corrupt.

Agreed regarding SAP SQL server configuration.  However, I have doubts about our support/implementation team's sql skills, so would like to learn more.  Thank you for looking.
Memory-Consumption---10132014-349-PM---S
0
 

Author Comment

by:PetEdge
ID: 40379652
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:PetEdge
ID: 40379657
Memory usage by components:memory-usage-by-components.jpg
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40379728
I can't help you without knowing the configuration of your server.
It's MSSQL 2008R2 right? Which edition? 32bit or 64bit? And what about the Operating System?
How much RAM and CPU? It's physical or virtual machine?
It's dedicated to SQL Server? If not, what's running more on the box?

NOTE: Still can't read the Excel file.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 333 total points
ID: 40379986
Overall it doesn't look too bad, except for the sudden losses of memory over the past 7 days.  Periodically something on the box is forcing SQL to give up big chunks of memory, and that is terrible for performance.

Could you run the script below and post the results?

Btw, Vitor, save the file with an extension of ".xls" to view it.


SET NOCOUNT ON

IF OBJECT_ID('tempdb.dbo.#mem_usage') IS NOT NULL
    DROP TABLE #mem_usage
CREATE TABLE #mem_usage (
    id int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Description varchar(200) NOT NULL,
    is_a_counter bit NOT NULL DEFAULT 0,
    show_KB bit NOT NULL DEFAULT 0,
    show_MB bit NOT NULL DEFAULT 1,
    show_GB bit NOT NULL DEFAULT 1,
    mem_KB bigint NULL,
    mem_MB AS mem_KB / 1024.0,
    mem_GB AS mem_KB / 1048576.0,
    Comments varchar(500) NULL DEFAULT ''
    )

IF OBJECT_ID('tempdb.dbo.#performance_counters') IS NOT NULL
    DROP TABLE #performance_counters

DECLARE @sql_page_size int
DECLARE @performance_instance_name varchar(128)
DECLARE @number_of_dashes_in_breaker_lines int

SET @sql_page_size = 8192 --SQL currently uses only 8K pages
SET @number_of_dashes_in_breaker_lines = 120

SELECT *
INTO #performance_counters
FROM sys.dm_os_performance_counters
--SELECT * FROM sys.dm_os_performance_counters
 

-- Get SQL Server instance name
SELECT @performance_instance_name = LEFT([object_name], (CHARINDEX(':',[object_name])))
FROM #performance_counters
WHERE counter_name = 'Buffer cache hit ratio'

-- Capture Memory usage details
INSERT INTO #mem_usage ( Description )
SELECT 'Memory usage details for: ' + @@SERVERNAME + ' (' + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - ' + SUBSTRING(@@VERSION, CHARINDEX('X',@@VERSION),4) + ' - ' + CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'

--------
INSERT INTO #mem_usage ( Description )
SELECT REPLICATE('-', @number_of_dashes_in_breaker_lines)
INSERT INTO #mem_usage ( Description )
SELECT 'Memory visible to the Operating System'
INSERT INTO #mem_usage ( Description, mem_KB )
SELECT 'Physical Memory visible to the Operating System', CEILING(physical_memory_in_bytes / 1024.0)
FROM sys.dm_os_sys_info
--INSERT INTO #mem_usage ( Description, mem_KB )
--SELECT 'Virtual Memory visible to the Operating System', CEILING(virtual_memory_in_bytes / 1024.0)
--FROM sys.dm_os_sys_info
INSERT INTO #mem_usage ( Description, mem_KB )
SELECT 'max SQL Server memory (buffer pool), as set by sp_configure', CAST(value AS int) * 1024.0 AS mem_KB
FROM master.sys.configurations
WHERE name LIKE '%max%server%memory%'
--------
INSERT INTO #mem_usage ( Description )
SELECT REPLICATE('-', @number_of_dashes_in_breaker_lines)
INSERT INTO #mem_usage ( Description )
SELECT 'Buffer Pool Usage and Stats as of this Moment'
INSERT INTO #mem_usage ( Description, mem_KB )
SELECT 'Buffer Pool Committed', bpool_committed * 8 as BPool_Committed_KB
FROM sys.dm_os_sys_info
INSERT INTO #mem_usage ( Description, mem_KB )
SELECT 'Buffer Pool Commit Target', bpool_commit_target * 8 as BPool_Commit_Tgt_KB
FROM sys.dm_os_sys_info
INSERT INTO #mem_usage ( Description, mem_KB )
SELECT 'Buffer Pool Visible', bpool_visible * 8 as BPool_Visible_KB
FROM sys.dm_os_sys_info
INSERT INTO #mem_usage ( Description, mem_KB )
SELECT 'Total Memory used by SQL Server Buffer Pool as reported by Perfmon counters', cntr_value as mem_KB
FROM #performance_counters
WHERE counter_name = 'Total Server Memory (KB)'
INSERT INTO #mem_usage ( Description, mem_KB )
SELECT 'Memory needed as per current Workload for SQL Server instance', cntr_value as mem_KB
FROM #performance_counters
WHERE counter_name = 'Target Server Memory (KB)'
INSERT INTO #mem_usage ( Description, mem_KB )
SELECT 'Number of bytes in the buffer pool (includes database, free, and stolen)', cntr_value * @sql_page_size / 1024.0 as mem_KB
FROM #performance_counters
WHERE object_name = @performance_instance_name + 'Buffer Manager' AND counter_name = 'Total pages'
INSERT INTO #mem_usage ( Description, mem_KB )
SELECT 'Number of Data bytes in the buffer pool', cntr_value * @sql_page_size / 1024.0 AS mem_KB
FROM #performance_counters
WHERE object_name = @performance_instance_name + 'Buffer Manager' AND counter_name = 'Database pages'
INSERT INTO #mem_usage ( Description, mem_KB )
SELECT 'Number of Free bytes in the buffer pool', cntr_value * @sql_page_size / 1024.0 AS mem_KB
FROM #performance_counters
WHERE object_name=@performance_instance_name+'Buffer Manager' AND counter_name = 'Free pages'
INSERT INTO #mem_usage ( Description, mem_KB )
SELECT 'Number of Reserved bytes in the buffer pool', cntr_value * @sql_page_size / 1024.0 AS mem_KB
FROM #performance_counters
WHERE object_name =  @performance_instance_name + 'Buffer Manager' AND counter_name = 'Reserved pages'
INSERT INTO #mem_usage ( Description, mem_KB )
SELECT 'Number of Stolen bytes in the buffer pool', cntr_value * @sql_page_size / 1024.0 AS mem_KB
FROM #performance_counters
WHERE object_name = @performance_instance_name + 'Buffer Manager' AND counter_name = 'Stolen pages'
INSERT INTO #mem_usage ( Description, mem_KB )
SELECT 'Number of Plan Cache bytes in the buffer pool', cntr_value * @sql_page_size / 1024.0 AS mem_KB
FROM #performance_counters
WHERE object_name = @performance_instance_name + 'Plan Cache' AND counter_name = 'Cache Pages' AND instance_name = '_Total'
--
INSERT INTO #mem_usage ( Description )
SELECT 'Buffer Pool-Related Counts'
INSERT INTO #mem_usage ( Description, is_a_counter, mem_KB, show_GB, Comments )
SELECT 'Page Life Expectancy - Number of seconds a page will stay in the buffer pool without references' as [Page Life in seconds],
    1, cntr_value * 1024, 0, CASE WHEN (cntr_value > 300) THEN 'PLE is Healthy' ELSE 'PLE is not Healthy' END
FROM #performance_counters
WHERE object_name = @performance_instance_name + 'Buffer Manager' AND counter_name = 'Page life expectancy'
INSERT INTO #mem_usage ( Description, is_a_counter, mem_KB, show_GB )
SELECT 'Number of requests per second that had to wait for a free page' as [Free list stalls/sec],
    1, cntr_value * 1024, 0
FROM #performance_counters
WHERE object_name = @performance_instance_name + 'Buffer Manager' AND counter_name = 'Free list stalls/sec'
INSERT INTO #mem_usage ( Description, is_a_counter, mem_KB, show_GB )
SELECT 'Number of pages flushed to disk/sec by a checkpoint or other operation that require all dirty pages to be flushed' as [Checkpoint pages/sec],
    1, cntr_value * 1024, 0
FROM #performance_counters
WHERE object_name = @performance_instance_name + 'Buffer Manager' AND counter_name = 'Checkpoint pages/sec'
INSERT INTO #mem_usage ( Description, is_a_counter, mem_KB, show_GB )
SELECT 'Number of buffers written per second by the buffer manager''s lazy writer' as [Lazy writes/sec],
    1, cntr_value * 1024.0, 0
FROM #performance_counters
WHERE object_name = @performance_instance_name + 'Buffer Manager' AND counter_name = 'Lazy writes/sec'
INSERT INTO #mem_usage ( Description, is_a_counter, mem_KB, show_GB )
SELECT 'Total number of processes waiting for a workspace memory grant' as [Memory Grants Pending],
    1, cntr_value * 1024, 0
FROM #performance_counters
WHERE object_name = @performance_instance_name + 'Memory Manager' AND counter_name = 'Memory Grants Pending'
INSERT INTO #mem_usage ( Description, is_a_counter, mem_KB, show_GB )
SELECT 'Total number of processes that have successfully acquired a workspace memory grant' as [Memory Grants Outstanding],
    1, cntr_value * 1024, 0
FROM #performance_counters
WHERE object_name = @performance_instance_name + 'Memory Manager' AND counter_name = 'Memory Grants Outstanding'
--------
INSERT INTO #mem_usage ( Description )
SELECT REPLICATE('-', @number_of_dashes_in_breaker_lines)
INSERT INTO #mem_usage ( Description )
SELECT 'Dynamic Memory Usage as of this Moment'
INSERT INTO #mem_usage ( Description, mem_KB )
SELECT '...for maintaining connections', cntr_value as mem_KB
FROM #performance_counters
WHERE counter_name = 'Connection Memory (KB)'
INSERT INTO #mem_usage ( Description, mem_KB )
SELECT '...for locks', cntr_value as mem_KB
FROM #performance_counters
WHERE counter_name = 'Lock Memory (KB)'
INSERT INTO #mem_usage ( Description, mem_KB )
SELECT '...for SQL cache', cntr_value as mem_KB
FROM #performance_counters
WHERE counter_name = 'SQL Cache Memory (KB)'
INSERT INTO #mem_usage ( Description, mem_KB )
SELECT '...for query optimization', cntr_value as mem_KB
FROM #performance_counters
WHERE counter_name = 'Optimizer Memory (KB)'
INSERT INTO #mem_usage ( Description, mem_KB )
SELECT '...for hash, sort and create index operations', cntr_value as mem_KB
FROM #performance_counters
WHERE counter_name = 'Granted Workspace Memory (KB)'
INSERT INTO #mem_usage ( Description, mem_KB )
SELECT '...consumed by cursors', cntr_value as mem_KB
FROM #performance_counters
WHERE counter_name = 'Cursor memory usage' AND instance_name = '_Total'
--------
INSERT INTO #mem_usage ( Description )
SELECT REPLICATE('-', @number_of_dashes_in_breaker_lines)


SELECT
    Description,
    --ISNULL(CASE WHEN show_KB = 1 THEN CAST(mem_KB AS varchar(30)) ELSE NULL END, '') AS Memory_KB,
    REPLACE(ISNULL(CASE WHEN show_MB = 1 THEN CAST(mem_MB AS varchar(30)) ELSE NULL END, ''), '.000000',
        CASE WHEN is_a_counter = 1 THEN '' ELSE '.000000' END) AS Memory_MB,
    ISNULL(CASE WHEN show_GB = 1 THEN CAST(mem_GB AS varchar(30)) ELSE NULL END, '') AS Memory_GB,
    Comments
FROM #mem_usage
ORDER BY id
0
 

Author Comment

by:PetEdge
ID: 40380163
Hi Scott,

Results are below:
PED-memory-usage-details.jpg
0
 

Author Comment

by:PetEdge
ID: 40380173
Vitor,

MSSQL 2008R2, 64bit.  
Virtual machine.  32768 MB Memory.
CPU:  Quad 2.9 Ghz Intel
It's dedicated to SQL Server.  

Regards,
Karen
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40380196
How many vCPU's?
With that Max Memory set for SQL Server I think you won't have memory pressure for a while.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 333 total points
ID: 40380451
SQL Server is using all the RAM you've allowed it for buffers, 26GB, out of 32GB total on the box.

It looks as if something else running on the box uses significant memory at certain times forcing SQL to release memory, and thus go below 26GB, then SQL reacquires RAM to get back to 26GB.  Acquiring and releasing memory are huge overhead.

Look for this text in the SQL Server error log:
A significant part of sql server process memory has been paged out.
For example:
EXEC xp_readerrorlog 0, 1, 'A significant part of sql server process memory has been paged out'
0
 

Author Comment

by:PetEdge
ID: 40382006
Scott - I didn't find that but did determine the root cause; as part of a restart of another server, the SQL server service on this server is being stopped and started.

Vitor - Quad 2.9 Ghz Intel vCPUs.  Thank you for the input on the max memory setting.

Should I be at all concerned about the stolen buffer pages distribution?
0
 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 167 total points
ID: 40382037
Yes, stolen buffer should be your main focus.
Since the server only has a vCPU I would consider to attribute it one more.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question