x64 Memory Usage - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

x64 Memory Usage

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2007-12-11 : 12:17:47

I have setup 2 x64 SQL boxes now that are having problems with memory. Both boxes were pretty much setup with the defaults on windows 2003 R2 64 bit server, only changes were file locations etc.

On both servers the memory usage continues to climb over the course of a few days until all physical and virtual memory is used and the server comes to it's knees. A reboot will stabilize it for a few days while the memory usage ramps up again.

The sqlservr.exe process does not report much memory but the commit charge in the performance tab of task manager shows the maxed out value. I thought the sqlservr.exe process only didn't report the correct memory figure when AWE is enabled which it isn't.

I thought as a default, SQL server would dynamically allocate memory as needed and had internal mechanisms that would not use more than the physical ram available. The one server has 16GB and I even set the max SQL memory usage to 12GB but still my total memory usage would get out of hand. SQL Server is the only application on these boxes.

Am I making some incorrect assumptions and need to change the way SQL is setup? Any suggestions?

Thanks
Scott

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-11 : 22:38:26

Sql shouldn't use more memory than physical ones on the server, you can double check it in perfmon.

new4lct
Starting Member

1 Post

Posted - 2007-12-13 : 22:02:07

I am currently having the same issue as Scott. My box is a Windows 2003 R2 x64 with SP2 and SQL 2005 x64 with SP2. Scott, did you reboot the server after changing the max SQL memory usage to 12GB? I think you might have to restart for the setting to take affect. What is the setting on the virtual memory on the server?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-13 : 23:06:25

Did you check sql memory usage in perfmon?

Scott
Posting Yak Master

145 Posts

Posted - 2007-12-14 : 10:51:10

The one box has enterprise edition so I have used AWE and lock pages in memory and that seems to have resolved that problem. My other server is standard so can't use lock pages in memory and the it seems all the sql memory is paged out.

http://weblogs.asp.net/omarzabir/archive/2007/10/19/a-significant-part-of-sql-server-process-memory-has-been-paged-out-this-may-result-in-performance-degradation.aspx
Seems to indicate the same problem. Once a day I am clearing the cache an the box stays up but the performance is horrible and this is obviously not a long term solution.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-14 : 22:13:53

Does sql service account have 'lock pages in memory' user rights on the server?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources