Solved

sql server 2012 MEMORYCLERK_SQLCLR takes most memory

Posted on 2014-09-22
4
1,099 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 51

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 51

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.

630 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