<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Craig Outcalt
»
SQL Server Memory Management Explained
20 posts, Page 2 of 2
««
1
2
SQL Server Memory Management Explained
Rate Topic
Display Mode
Topic Options
Author
Message
SQLBOT
SQLBOT
Posted Tuesday, September 20, 2011 11:21 AM
SSChasing Mays
Group: General Forum Members
Last Login: Monday, February 18, 2013 3:22 PM
Points: 626,
Visits: 835
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
Post #1178111
SQLBOT
SQLBOT
Posted Tuesday, September 20, 2011 11:25 AM
SSChasing Mays
Group: General Forum Members
Last Login: Monday, February 18, 2013 3:22 PM
Points: 626,
Visits: 835
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.
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
Post #1178117
jerome.landis
jerome.landis
Posted Tuesday, September 20, 2011 3:19 PM
Grasshopper
Group: General Forum Members
Last Login: Thursday, April 25, 2013 5:32 AM
Points: 13,
Visits: 54
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?
Post #1178309
SQLBOT
SQLBOT
Posted Tuesday, September 20, 2011 6:10 PM
SSChasing Mays
Group: General Forum Members
Last Login: Monday, February 18, 2013 3:22 PM
Points: 626,
Visits: 835
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! :)
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
Post #1178377
SQLBOT
SQLBOT
Posted Tuesday, September 20, 2011 6:11 PM
SSChasing Mays
Group: General Forum Members
Last Login: Monday, February 18, 2013 3:22 PM
Points: 626,
Visits: 835
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
Post #1178379
jerome.landis
jerome.landis
Posted Tuesday, September 20, 2011 7:17 PM
Grasshopper
Group: General Forum Members
Last Login: Thursday, April 25, 2013 5:32 AM
Points: 13,
Visits: 54
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.
Post #1178390
SQLRNNR
SQLRNNR
Posted Tuesday, September 20, 2011 7:54 PM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754,
Visits: 12,337
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 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1178400
Caruncles
Caruncles
Posted Monday, October 10, 2011 11:00 AM
Valued Member
Group: General Forum Members
Last Login: Friday, May 03, 2013 2:29 PM
Points: 55,
Visits: 150
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."
Post #1188035
kurt.yang
kurt.yang
Posted Saturday, November 10, 2012 7:03 PM
Grasshopper
Group: General Forum Members
Last Login: Monday, March 11, 2013 7:35 PM
Points: 11,
Visits: 271
One thing that I don't quite understand...
The last query in your article
Post #1383410
kurt.yang
kurt.yang
Posted Saturday, November 10, 2012 7:16 PM
Grasshopper
Group: General Forum Members
Last Login: Monday, March 11, 2013 7:35 PM
Points: 11,
Visits: 271
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?
Post #1383411
« Prev Topic
|
Next Topic »
20 posts, Page 2 of 2
««
1
2
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
