Solved

sql server 2012 MEMORYCLERK_SQLCLR takes most memory

Posted on 2014-09-22
4
1,002 Views
Last Modified: 2014-09-23
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
Comment
Question by:xuewei shi
  • 2
  • 2
4 Comments
 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40338889
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
 

Author Comment

by:xuewei shi
ID: 40339212
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
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40339536
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
 

Author Closing Comment

by:xuewei shi
ID: 40340638
Thank you so much Vitor for your help!!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question