CODEWITHSUNDEEP
sqlsql-server
kirana Gunasinghe
kirana Gunasinghe

Reputation: 11

SQL Server 2012 Memory

I am trouble shooting an issue with our production server, I will give you some background about the problem.

We have SQL server 2012 standard = SP2+CU6 and Java / hibernate from the app side. 64GB memory and allocated 55GB to SQL server.

We have recently done some API changes and noticed this behavior.

The PLI is dropping (some times) to about 2 mins and when I check the buffer pool size it drops to about 4GB, during this time I can noticed PAGEIOLATCH_SH wait type which is expected because of the buffer pool issue.

My question is why SQL drops the buffer pool size to 4GB( max memory is 55GB) and how do I trouble shoot this issue. This issue only happening some times and the wait times increasing only during this time.

Used the sys.dm_os_memory_clerks but it doesn't match the total memory allocated to the sql server

Please help me with some advice to pinpoint the issue. is there any way to find the total memory usage in the system and find the missing memory ?

Upvotes: 1

Views: 177

Answers (1)

Hack-R
Hack-R

Reputation: 23200

Is there any way to find the total memory usage in the system and find the missing memory ?

Yes, there is:

-- To get the total physical memory installed on SQL Server
SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
    ,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
    ,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
    ,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
    ,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
    ,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
    ,[system_memory_state_desc] AS [System_Memory_State_Desc]
FROM [master].[sys].[dm_os_sys_memory]

--To get the minimum and maximum size of memory configured for SQL Server.
SELECT [name] AS [Name]
    ,[configuration_id] AS [Number]
    ,[minimum] AS [Minimum]
    ,[maximum] AS [Maximum]
    ,[is_dynamic] AS [Dynamic]
    ,[is_advanced] AS [Advanced]
    ,[value] AS [ConfigValue]
    ,[value_in_use] AS [RunValue]
    ,[description] AS [Description]
FROM [master].[sys].[configurations]
WHERE NAME IN ('Min server memory (MB)', 'Max server memory (MB)')

Upvotes: 2

Related Questions