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


new 64-bit Sql 2005 Server - integrity check completes then memory spikes 75% and doesnt lower


new 64-bit Sql 2005 Server - integrity check completes then memory spikes 75% and doesnt lower

Author
Message
oraculum
oraculum
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 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
Attachments
Idera_Memory_Consumption.doc (29 views, 250.00 KB)
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30652 Visits: 10091
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 opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

oraculum
oraculum
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 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
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30652 Visits: 10091
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 opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

SQLBOT
SQLBOT
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3020 Visits: 836
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
oraculum
oraculum
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 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
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30652 Visits: 10091
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 opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

jcosta
jcosta
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 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?
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51191 Visits: 8789
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 on googles mail service
SQLBOT
SQLBOT
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3020 Visits: 836
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
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