Solved

max memory set to high

Posted on 2014-11-11
3
177 Views
Last Modified: 2014-11-26
I have just read this article:

http://www.brentozar.com/blitz/max-memory/

Does anyone has a specific query that can query what the min and max memory settings are currently configured to? It would be useful to also include in the query the amount of memeory the server has available
0
Comment
Question by:pma111
3 Comments
 
LVL 13

Assisted Solution

by:Felix Leven
Felix Leven earned 167 total points
ID: 40434941
SELECT name, value, value_in_use, [description] 
FROM sys.configurations
WHERE name like '%server memory%'
ORDER BY name OPTION (RECOMPILE);

Open in new window


Shows
max server memory (MB) and min server memory (MB)
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 167 total points
ID: 40434952
Here's the command for get the available memory:
SELECT * FROM sys.dm_os_sys_memory
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 166 total points
ID: 40435878
Be careful with sys.dm_os_sys_memory, as it shows only the entire server's RAM, not for just the current instance of SQL Server.

Here's a script that gives a good overview of that specific instance's memory usage.  I copied the underlying approach from a SQL blog but I modified the method of output to make it easier to read:


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, mem_KB )
SELECT 'min 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 '%min%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 (above 300)' ELSE 'PLE is not Healthy (not above 300)' 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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 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

13 Experts available now in Live!

Get 1:1 Help Now