Solved

sql server 2012 MEMORYCLERK_SQLCLR takes most memory

Posted on 2014-09-22
4
908 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 46

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 46

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now