Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

new 64-bit Sql 2005 Server - integrity check completes then memory spikes 75% and doesnt lower Expand / Collapse
Author
Message
Posted Tuesday, August 18, 2009 10:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 3, 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
Posted Tuesday, August 18, 2009 1:20 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
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
Posted Wednesday, August 19, 2009 2:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 3, 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
Posted Wednesday, August 19, 2009 7:56 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
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
Posted Wednesday, August 19, 2009 8:22 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, 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
Post #773562
Posted Wednesday, August 19, 2009 10:35 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 3, 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
Posted Wednesday, August 19, 2009 10:49 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
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
Posted Thursday, August 20, 2009 11:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, August 20, 2009 11:22 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:00 AM
Points: 4,410, Visits: 6,281
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
Posted Thursday, August 20, 2009 11:30 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, 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
Post #774444
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse