September 25, 2009 at 6:28 am
We just did an upgrade from SQL Server 2000 SP4 to SQL Server 2005. We are running a 32 bit Enterprise Edition of SQL Server. The O/S is Windows Server 2003 x64. The server has 4x Quad core processors and 128 GB of RAM. We have AWE enabled and we set the MAX memory to 120 GB.
Strangely enough, Spotlight and DBCC Memorystatus are both reporting that SQL Server is only using 52.9 GB of memory. This is most likely because AWE is only allocating 52.9 GB of memory. We're concerned because before the upgrade, SQL Server 2000 was using 63.5 GB. In both cases, we have the service account setup to Lock Pages in Memory as well.
So far, the support gurus are telling us that the server only needs 52.9 GB of memory, so it only uses that much. But we are running an OLTP with over 400 GB of data. Just in one week, we do over 1.5 billion I/O's on the file group. I simply cannot believe that on a server as busy as ours, SQL Server would choose to leave so much memory sitting idle.
I would at least like to see SQL Server using what it was previously. Does anyone have an ideas?
September 25, 2009 at 8:25 am
A couple things to ask yourself and to check:
If you're on the 64-bit OS platform why did you not install SQL as 64-bit?
Did you grant the service account for SQL the "LOCK PAGES IN MEMORY" right? Even in a 64-bit environment you'll need to do this. You'll not need to configure AWE in a pure 64-bit environment.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
September 25, 2009 at 8:47 am
Thanks for the reply.
Unfortunately, we were forced to go 32 bit because our current ERP system is not supported on SQL Server 2005 64 bit. Ironically, 64 bit is supported on SQL Server 2008 though.
As for AWE in the operating system, we have not set anything in the boot.ini for AWE, because to our knowledge, it should not be required. Currently, Windows Server has no problems seeing the entire 128 GB. In fact, SQL Server can see the entire amount as well, but refuses to use more than 52.9 GB.
I should also add that this is a clustered instance as well. When we reboot or restart SQL Services, the memory usage always goes back to 52.9 GB.
September 25, 2009 at 10:30 am
Did you configure your memory in the server properties for SQL to use the full amount of RAM? You mentioned this is clustered? How many nodes and how many instances? How many instances, if more than 1, run on the same node at any time? I suggest configuring the instance to use full amount of RAM - 4GB (reserved for O/S) with no minimum value set. if only a single instance per node. In case of a failover with more than 1 instance running on same node go in and configure down the values for the time being until your clustered instances are failed back over properly.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
September 28, 2009 at 1:12 am
In this case, we have only one instance of SQL Server 2005 running on 2 nodes. Both servers have exactly the same specs. I also verified that the service account is setup to Lock Pages in Memory on both servers.
Over the weekend, I set the memory settings back to the default in 2005, so at the moment there is no ceiling on how high the memory usage could go. After a restart, the server came up using about 17.5 GB of memory. I ran 15 different select statements on our largest tables (over 100 GB of data), an exec sp_updatestats, and dbcc checkdb all at the same time. Over the course of about 20 minutes, the server steadily consumed memory up to 52.6 GB. From there, I could not get it to use anymore. It ran for another 10 minutes at 83% CPU and a disk queue of 700 on our SAN.
Another issue that has me concerned is that by right-clicking on the Server --> Properties --> Memory, the Server Memory Options are inaccessible (grayed out), except for the 'AWE Enabled' option. The minimum and maximum server memory can only be changed through SQL. I have been unable to pin down why this is. So far I can't find any other examples of this in our datacenter or on the internet.
I am thinking it may be an indication that something is wrong with our SQL Server installation...
September 29, 2009 at 3:15 am
You say the server has 128 GB memory installed and you have set SQL max memory to 120 GB. You are probably asking for more memory than is available.
When you start 32-bit SQL Server and ask for AWE memory, SQL will make only 1 attempt to get that memory. If the full amount requested is not available, SQL will fall back to dynamic memory allocation. It will NOT say 'you asked for 120 GB but only 98 GB is available so I will grab that', instead it says 'you asked for 120 GB but the full 120GB is not available so I will start with minimum memory and ask for more as I need it'. Try reducing your max memory parameter - you need to allow enough memory for Windows to work, plus any memory used by non-SQL Server processes.
If you are seeing that SQL Server is using 52.9 GB, then one of two things are occurring:
1) (most likely) SQL only needs 52.9 GB to run the workload. SQL does not choose to leave the remaining memory unused. It is not allocated because SQL Server has no need to use it.
2) You are running other software on the box (which includes Analysis Services, Reporting Services, etc) that is using the rest of your memory. In this case, SQL Server is only using 52.9 GB because that is all it can get hold of.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 30, 2009 at 6:11 am
I am leaning towards the fact that you simply do not need more memory to complete the workload. However to determine this you need to look at your perfmon counters or look at sys.dm_os_wait_stats.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
September 30, 2009 at 9:15 am
Thanks for your reply.
Would you recommend looking at any particular wait type in this view?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply