I would like to know what dmv do i need to check , currently my server is using 89% of the memory . i reduced the max available memory to 51GB for max server memory in sql server but in task manager it shows 57GB of memory is used by sql server instance .
is there a problem with indexes or any other wait types that i need to check. looking forward for your kind reply.
i executed the https://dba.stackexchange.com/questions/213010/memory-usage-by-sql-server
and got attached image , kindly look at it if any information is provided that will help .
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads],
qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],
FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);
November 11, 2019 at 11:02 am
SQL Server will slowly release the memory back to the OS. Has the memory usage reduced since you posted?
Thanks
From the documentation:
SQL Server might allocate more memory than the value specified in the max server memory setting. This behavior may occur when the Total Server Memory (KB) value has already reached the Target Server Memory (KB) setting (as specified by max server memory). If there is insufficient contiguous free memory to meet the demand of multi-page memory requests (more than 8 KB) because of memory fragmentation, SQL Server can perform over-commitment instead of rejecting the memory request.
Thomas Rushton
blog: https://thelonedba.wordpress.com
November 11, 2019 at 4:03 pm
SQL Server will slowly release the memory back to the OS. Has the memory usage reduced since you posted?
Thanks
thanks for your reply , the problem is that the memory allocated does not reduce and is not released , it looks like something is getting the memory and does not release it back some sort of memory leak. in the past i restart the sql server and the pc and the issue was resolved but with the passage of time the memory allocation got increased until it reach 89% of total server memory .
November 11, 2019 at 5:09 pm
thanks for your reply , as per the documentation provide in above link it says.
As more users connect and run queries, SQL Server acquires the additional physical memory on demand. A SQL Server instance continues to acquire physical memory until it either reaches its max server memory allocation target or the OS indicates there is no longer an excess of free memory; it frees memory when it has more than the min server memory setting, and the OS indicates that there is a shortage of free memory.
The buffer pool does not free any of the acquired memory until it reaches the amount specified in min server memory. Once min server memory is reached, the buffer pool then uses the standard algorithm to acquire and free memory as needed. The only difference is that the buffer pool never drops its memory allocation below the level specified in min server memory, and never acquires more memory than the level specified in max server memory.
there should be some way of setting max memory limit i will set the min server memory property tomorrow and see what will happen?
November 12, 2019 at 5:00 am
min server memory settings also did not work.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply