Solved

Help reviewing memory consumption SQL Server 2008 R2

Posted on 2014-10-13
12
180 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 45

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
 

Author Comment

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

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:ScottPletcher
ScottPletcher 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 45

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:
ScottPletcher 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 45

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now