[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

max memory set to high

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
pma111
Asked:
pma111
3 Solutions
 
Felix LevenSenior System and DatabaseadministratorCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Here's the command for get the available memory:
SELECT * FROM sys.dm_os_sys_memory
0
 
Scott PletcherSenior DBACommented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now