Solved

Help reviewing memory consumption SQL Server 2008 R2

Posted on 2014-10-13
12
202 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
12 Comments
 
LVL 50

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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

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

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 50

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 50

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

690 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