Go Premium for a chance to win a PS4. Enter to Win

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

sql server 2012 MEMORYCLERK_SQLCLR takes most memory

we have a sql 2012 server, and recently we experience periodically page life downgrades to 0 problem. The frequency is every a few hours.
When I checked memory usage, MEMORYCLERK_SQLCLR takes most of the memory, more than MEMORYCLERK_SQLBUFFERPOOL, the details is in attached picture.

And I used profiler to check what is running in SQL Server while page life suddenly downgrades to 0, and I found only log shipping backup processes are running at the time.. Is it log shipping caused problem? Though the log backup running at the time is for very small database (under 200MB)... The proflier and performance data screenshot is also attached.

Appreciate for your kindly help, any idea is appreciated a lot!
memory-usage.jpg
Profiler-perf.jpg
0
xuewei shi
Asked:
xuewei shi
  • 2
  • 2
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Hi xuewei,

This is todays result?
For now it's only having the highest amount of memory reserved, not used.
Do you remember that long running SP that you showed me in another question? I think it might uses CLR as I can't figure any other option here. Unfortunaly the only way to clean MEMORYCLERK_SQLCLR  is restarting the service.
0
 
xuewei shiAuthor Commented:
Good day Vitor! Yes this is today's result. Now still about 12GB is reserved for CLR, so does it mean this 12GB memory cannot be used by SQL buffer pool, is my understanding right? Thank you!
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm afraid that's correct.
For now are you having performance issues? If not, wait for the next reboot and check again the memoryclerk.
0
 
xuewei shiAuthor Commented:
Thank you so much Vitor for your help!!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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