2

I have 1 TB Dynamic Axapta DB on the SQL Server 2012. I have 128 gigabytes of memory.

Version : Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)   Oct 19 2012 
13:38:57   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) 
on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) 

Lock memory pages is active.

SQL Server Max memory settting : 115 GB

I am looking at the sql server on task manager and I see that the sql server is using all the memory. But looking at the Performance Counter, the server has free memory. Why sql server not using the all memory? Therefore, the highest type of wait on sql server is PAGEIOLATCH_SH

How do i fix this problem?

enter image description here

For the comment : enter image description here

Scenario:

10:30 PM - Database Cache Memory 30 GB - Free Memory: 75 GB

10:40 PM - Database Cache Memory 25 GB - Free Memory: 80 GB

10:50 PM - Database Cache Memory 30 GB - Free Memory: 75 GB

I have free memory, but sql server deleted records from the cache partition. I am wrong?

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters 
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Database Pages';

;WITH src AS
(
SELECT 
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767 
THEN 'Resource DB' 
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3), 
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC; 

There are a few minutes between results:

enter image description here enter image description here

After the SP4 upgrade:

enter image description here

12
  • 3
    How did you reach to the conclusion that this is a problem ?. Please add the output of select * from sys.dm_os_process_memory in the question. It is completely normal for SQL Server to use memory and task manager should never be referred for SQL Server memory consumption
    – Shanky
    Apr 2, 2018 at 8:29
  • If you see column physical_memory_in_use_kb it is coming 110.2 GB. This is physical memory used by SQL Server, max server memory is 115 G so how can you say SQL Server is not using all memory the difference is just 5G. Moreover SQL Server will only use memory it requires, if not required it would not use all.
    – Shanky
    Apr 2, 2018 at 8:44
  • Thanks for comment, I added result and problem detail on question. I know, completely normal for SQL Server to use all memory. What i would like to say, why 80 GB of free memory, you see performance counter? Apr 2, 2018 at 8:49
  • 1
    You should first apply SQL Server 2012 Sp4 this seems like something unusual. Moreover always remember if you see anything unusual related to memory first make sure SQL Server is patched to latest SP. I will wait for your update
    – Shanky
    Apr 2, 2018 at 9:07
  • I will upgrade sql as soon possible and share result. Thank you for help. Apr 2, 2018 at 10:28

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.