<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
»
SQL Server 2005
»
Administering
»
new 64-bit Sql 2005 Server - integrity check...
16 posts, Page 1 of 2
1
2
»»
new 64-bit Sql 2005 Server - integrity check completes then memory spikes 75% and doesnt lower
Rate Topic
Display Mode
Topic Options
Author
Message
oraculum
oraculum
Posted Tuesday, August 18, 2009 10:00 AM
SSC Journeyman
Group: General Forum Members
Last Login: Wednesday, October 03, 2012 6:35 AM
Points: 97,
Visits: 501
Hi Guru's,
Hope you can help.
We have a new 64-bit Win 2003 SP2 server, with SQL Server 2005 SP3 installed.
Running for a few weeks and we are performing some test migrations.
Tets maintenance plans were created last week and the integrity check on one db ran on Sat 15th 10:00am. This completed successfully at 10:05am.
Using our Idera Diagnostics Manager I have noticed today that the SQL Memory Used is over 75% constantly. I have gone back through the history viewer and it was recorded at 10:06am on Sat 15th that Memory Jumped from 40% to 75%, at that time Disk Queue length for G (data drive) also spiked to 623.
I have checked logs, event viewing and nothing showing up apart from the above dbcc command that completed as stated. At 10:06 when this state change was repoted there were no active or user sessions on the server at all.
Disk queue length dropped back to normal by 10:12 the next snapshot from Idera, but memory is still at 75%.
Not sure what has caused this? any idea anyone?
We are migrating our Prod dbs over next week and need to know what has caused this.
Thanks
Oraculum
Post Attachments
Idera_Memory_Consumption.doc
(
17 views,
250.00 KB
)
Post #772861
Jeffrey Williams 3188
Jeffrey Williams 3188
Posted Tuesday, August 18, 2009 1:20 PM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Monday, June 10, 2013 12:13 PM
Points: 4,319,
Visits: 9,217
SQL Server will allocate memory up to the max memory setting and keep that memory. It does not release the memory. What you are seeing is normal behavior.
On x64 hardware - you want to make sure you set max memory and, if you are running Enterprise Edition of SQL Server you need to set lock pages in memory policy for the service account running SQL Server (Group Policy Editor).
Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Post #773014
oraculum
oraculum
Posted Wednesday, August 19, 2009 2:08 AM
SSC Journeyman
Group: General Forum Members
Last Login: Wednesday, October 03, 2012 6:35 AM
Points: 97,
Visits: 501
Hi, you say this is normal behaviour but no other server holds this memory as 'used'. All our servers have min and max memory set and lock pages in memory setting.
All other servers may spike on SQL Server Memory Used %, but they always release and it drops back below the defined threshold for the alert system.
There are no users or activity on this new server as its being setup for migration next week.
I am trying to find out why at 10:06 the memory jumped to 75% and is still showing 75% used, no change. This compared to all our other servers is not normal behaviour at all...
The allocation of memory is fine i see this on all servers, but keeping this memory as 'used' is not surley..?
We are rebooting anyway today, so I guess i will have to see if this happens again..
Oraculum
Post #773270
Jeffrey Williams 3188
Jeffrey Williams 3188
Posted Wednesday, August 19, 2009 7:56 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Monday, June 10, 2013 12:13 PM
Points: 4,319,
Visits: 9,217
SQL Server will only release memory back to the OS when the OS needs that memory. On dedicated SQL Server machines - that will never occur. If you have a max memory defined, SQL Server will not take more than the max memory setting for the buffer pool - but does take more than that, just not a lot more.
I have never seen SQL Server ever release memory once it has allocated it, and I wouldn't want it to - because databases perform much better with a lot of memory.
Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Post #773523
SQLBOT
SQLBOT
Posted Wednesday, August 19, 2009 8:22 AM
SSChasing Mays
Group: General Forum Members
Last Login: Monday, February 18, 2013 3:22 PM
Points: 626,
Visits: 835
Exactly.
Your other SQL Instances are having memory stolen from them by the OS which is under memory pressure.
My favorite SQL Server memory article of all time says that
SQL Server 2005 does respond to memory pressure both when lock pages in memory is enabled and disabled
http://blogs.msdn.com/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx
It makes no difference whether it's 32 or 64 bit, either
However, the official docs and Slava Oks are at odds with this.
Locking pages in memory is not required on 64-bit operating systems.
(from
http://technet.microsoft.com/en-us/library/ms190730%28SQL.90%29.aspx
)
I did indicate that on 64 bit platform we recommend to enable SQL Server to use locked pages in memory
(from
http://blogs.msdn.com/slavao/archive/2005/11/15/493019.aspx
)
I use lock pages in memory on both x64 and ia64.
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 #773562
oraculum
oraculum
Posted Wednesday, August 19, 2009 10:35 AM
SSC Journeyman
Group: General Forum Members
Last Login: Wednesday, October 03, 2012 6:35 AM
Points: 97,
Visits: 501
Still think i'm being misunderstood, the memory allocated is 24GB this is fine and as expected as its the Max Server Memory...
The problem is that 75% of that 'allocated' memory is constantly being used i.e. 18GB is in use now by sql server, even though nothing is running. If you look at perfmon logs the sql memory in use never drops on this server..
Server 32GB
SQL Allocated 24GB
SQL In Use 18GB ??
OS is not using more than a couple ever, and it has 8GB to itself anyhow...
Oraculum
Post #773732
Jeffrey Williams 3188
Jeffrey Williams 3188
Posted Wednesday, August 19, 2009 10:49 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Monday, June 10, 2013 12:13 PM
Points: 4,319,
Visits: 9,217
oraculum (8/19/2009)
Still think i'm being misunderstood, the memory allocated is 24GB this is fine and as expected as its the Max Server Memory...
The problem is that 75% of that 'allocated' memory is constantly being used i.e. 18GB is in use now by sql server, even though nothing is running. If you look at perfmon logs the sql memory in use never drops on this server..
Server 32GB
SQL Allocated 24GB
SQL In Use 18GB ??
OS is not using more than a couple ever, and it has 8GB to itself anyhow...
Nope, you are not being mis-understood. That is normal behavior for SQL Server - and the 18GB of usage is for the buffer pool. SQL Server reads the data from the database into memory - and accesses that data from memory. The more memory that is available, the more SQL Server will allocate for the buffer pool and load.
The fact that you have set the max memory for the server to 24GB - and SQL Server is only using 18GB at this point tells me that you might not need more than 18GB of memory for this instance of SQL Server.
Again, that is normal behavior and expected for SQL Server.
Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Post #773739
jcosta
jcosta
Posted Thursday, August 20, 2009 11:06 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, August 18, 2010 1:39 PM
Points: 7,
Visits: 102
Jeffrey,
On x64 hardware, Microsoft says that the "Lock pages in memory" right is not necessary. From this Technet article:
http://technet.microsoft.com/en-us/library/ms190730.aspx
"Locking pages in memory is not required on 64-bit operating systems."
What makes you recommend it on x64?
Post #774421
TheSQLGuru
TheSQLGuru
Posted Thursday, August 20, 2009 11:22 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 11:35 AM
Points: 3,672,
Visits: 5,172
Jeffrey Williams (8/19/2009)
SQL Server will only release memory back to the OS when the OS needs that memory. On dedicated SQL Server machines - that will never occur. If you have a max memory defined, SQL Server will not take more than the max memory setting for the buffer pool - but does take more than that, just not a lot more.
I have never seen SQL Server ever release memory once it has allocated it, and I wouldn't want it to - because databases perform much better with a lot of memory.
Several incorrect statements there. It can occur on a SQL Server only box that the OS asks for memory back. Also SQL Server does give memory back, and you DO want it to or else your server could suffer incredibly bad performance or even literrally stop execution if the OS has a need for RAM that cannot be served.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #774434
SQLBOT
SQLBOT
Posted Thursday, August 20, 2009 11:30 AM
SSChasing Mays
Group: General Forum Members
Last Login: Monday, February 18, 2013 3:22 PM
Points: 626,
Visits: 835
jcosta (8/20/2009)
Jeffrey,
On x64 hardware, Microsoft says that the "Lock pages in memory" right is not necessary. From this Technet article:
http://technet.microsoft.com/en-us/library/ms190730.aspx
"Locking pages in memory is not required on 64-bit operating systems."
What makes you recommend it on x64?
Read my post above. Microsoft itself can't make up its mind on this.
Lock Pages also only prevents other apps (like another SQL Server Instance) from taking memory.
The OS can take whatever it wants.
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 #774444
« Prev Topic
|
Next Topic »
16 posts, Page 1 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.
