July 23, 2015 at 7:23 am
Hi,
We have a server in AWS with 16gb of Ram and 2 VCPU. I am seeing a strange job on one of my SQL servers to recycle the memory every day as below. I explained the team that this will clear the SQL server cache , plans etc when we do this and still they want to run this job daily in Prod. How can i believe them that this is worst process. Any ideas and thoughts would suggest it.
Also on the side-i am trying to find out the actual cause root issue as which process is consuming more memory and not releasing it. Found that MEMORYCLERK_SQLBUFFERPOOL is using 50% of Ram and there is some Non Bufferpool memory usage by some process and not releasing the memory. I want to know how much memory it is being using and what queries?. Any advice?
DECLARE @UsedRAM INT
SELECT @UsedRAM =
physical_memory_in_use_kb/1024
FROM sys.dm_os_process_memory;
SELECT @UsedRAM
IF @UsedRAM > 10000
BEGIN
EXEC sys.sp_configure N'max server memory (MB)', N'3000'
RECONFIGURE WITH OVERRIDE
WAITFOR DELAY '00:00:15';
EXEC sys.sp_configure N'max server memory (MB)', N'12000'
RECONFIGURE WITH OVERRIDE
END
July 23, 2015 at 7:33 am
Queries don't use much memory. The SQLBufferPool clerk is the data cache, the plan cache and a few other caches. It should be the highest memory consumer as that's the stuff that SQL caches for performance.
The nonbuffer memory should be fairly low. It's the thread stacks, CLR memory, backup buffers and a few other things
SQL will not reduce it's memory usage unless someone sets max server memory really low. This is by design and is the documented behaviour. You should expect SQL to take as much memory as it's allowed and not release it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2015 at 8:27 am
Thanks Gila for the response.
So currently on my Prod server i have 30Gb Ram and i set the max server config to 23552 Mb and Min server config to 1024MB. And running the memoryclerks query i see only 14.6GB being used by SQLBufferPOOL and 3GB by CacheStore_SQLCP.
So do you suggest to run the above mentioned job to reduce the max server memory on the server daily ?
Also i summed up the all the memory usage and its coming to 18GB- so i am curious to find out where is the remaining 23552MB-18261MB= approximately 5GB?
There is a Linked server openrowset query running on the server daily night to fetch data from MYSQL and i am suspecting it to be consuming the remaining memeory and not releasing it. How can i find that process memory consumption? also does it come under Buffer pool or Nonbuffer?
July 23, 2015 at 9:51 am
muthyala_51 (7/23/2015)
So do you suggest to run the above mentioned job to reduce the max server memory on the server daily ?
No. Absolutely not.
There is a Linked server openrowset query running on the server daily night to fetch data from MYSQL and i am suspecting it to be consuming the remaining memeory and not releasing it. How can i find that process memory consumption? also does it come under Buffer pool or Nonbuffer?
Non-buffer
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2015 at 10:19 am
Any suggestions?
July 23, 2015 at 10:21 am
@Gila- So is there any query to find which process is consuming memory in NONbufferpool?
July 23, 2015 at 10:37 am
No, because like the buffer pool it's not per-process. You should be able to use the memory clerks or other memory DMV to identify what area is using a lot of memory, but it won't be a single spid.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply