SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Memory Management Explained


SQL Server Memory Management Explained

Author
Message
SQLBOT
SQLBOT
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1932 Visits: 836
Lucas Beris (9/20/2011)
Hi, great article! I have a doubt when you say:
When the Current counter is larger than your Available counter it indicates that you are paging to disk
Isn´t part of the current memory already used by my process, that's why the available memory is lower? And maybe my process is not swapping to disk?

Thanks,
LKZ:


thanks for the question.
I believe I meant to write :

When the Current counter is larger than your Total counter


I'll need to edit the article later.

Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
SQLBOT
SQLBOT
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1932 Visits: 836
GregoryLPalmer (9/20/2011)
Nice article, I noticed one issue. You refer to the program you're running as process monitor in the article but I believe it is actually Process Explorer. Lost some time tracking down that issue since I haven't used either one in a long time. If you want to throw in a link, it is here on the TechNet site. I also noticed that the interface has changed in the new version.



Good Catch Greg.
Critical Error.
I will update it later


Yes I intended to say "Process Explorer".
http://technet.microsoft.com/en-us/sysinternals/bb896653

I almost always say the wrong one when I mean the other.Blush

Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
jerome.landis
jerome.landis
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 97
Yes, i have the situation where the current memory is 17.8GB and the total mem is 16.6GB.

How do i correct this situation?


Question 2# on your screen shot, the page faults delta is 13891, I guess you had an issue before the screen shot? How did you fix or create the issue?
SQLBOT
SQLBOT
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1932 Visits: 836
jerome.landis (9/20/2011)
Yes, i have the situation where the current memory is 17.8GB and the total mem is 16.6GB.

How do i correct this situation?


Question 2# on your screen shot, the page faults delta is 13891, I guess you had an issue before the screen shot? How did you fix or create the issue?



answer #1
The gut instinct is to reduce the amount of memory that the machine is using (maybe give SQL Server less using the max server memory setting... check the page life expectancy on your instances).
Either that or install more memory. you are in a 'bad place' right now. I would imagine someone has noticed some performance issues before now... although keep monitoring the situation... it could be that you were running dbcc checkdb and reindixing and updating statistics all at once on 3 different instances... check to see how much is system cache, how much SQL is using You'll discover your course as you discover more information. Feel free to private message me if you have some other questions on the topic.

#2. Page faults are normal, but the screenshot was from my laptop, not a server. I don't know what I was doing around then. It looks like I was working hard! Smile

Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
SQLBOT
SQLBOT
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1932 Visits: 836
giles.middleton (9/20/2011)

I believe there's an error in the article, circa 4,700,000 K is not 4.7MB, it's 4.7GB.


"The Available counter will show how many KB are available to the host;400 MB is probably as low as you would want to see it. The image above shows ~4.7 MB in the Available counter"

Typo, thanks!

I'll get it fixed.

Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
jerome.landis
jerome.landis
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 97
Thanks, I did reduce the max memory in SQL 2008R2sp1, and the current memory still grew back to 17GB. It appears that no matter what I put in for the max mem, we still go back into that paging issue. Yes, some users have said it is slow. The current mem stays pegged at 17.8 all day and even at night when no one is around.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63923 Visits: 18570
Thanks for the article Craig. I added this one to the briefcase.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Caruncles
Caruncles
SSChasing Mays
SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)

Group: General Forum Members
Points: 609 Visits: 244
Good stuff! Though not necessarily related, I have found the practice of allocating a small hard drive space for OS and installed programs can come back to bite you after a server gets some age. As we have installed updates to the C: drive, we have run into space issues, and in fact, have stopped installing updates. I expect this will cause pagefile problems eventually if the problem keeps growing. The 2GB of memory on the '03 server doesn't seem nearly as big as it did back when it was purchased!

Wallace Houston
Sunnyland Farms, Inc.

"We must endeavor to persevere."
kurt.yang
kurt.yang
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 302
One thing that I don't quite understand...

The last query in your article
kurt.yang
kurt.yang
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 302
One thing that I don't quite understand...

The last query in your article,
----------------------------------------------
WITH MPAPlans
AS (SELECT plan_handle
, SUM(size_in_bytes) / 1024 / 8 AS numPages
FROM sys.dm_exec_cached_plans
GROUP BY plan_handle)
--------------------------------------------------
The execution plans should be mainly cached in buffer pool. So I think the CTE actually sum all the cache bytes from buffer pool and MPA. So the query result is not exactly how much space that the execution plans taking in MPA, right?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search