January 13, 2011 at 7:23 am
Hi Jeffrey,
The max memory setting is 226304. We set it at 221 GB leaving 35 GB for the OS.
-Roy
January 13, 2011 at 7:28 am
Paul, The system_cache_kb is currently 593464. Unfortunately all the memory counters are kind of useless since I did a controlled reboot of the Server.
-Roy
January 13, 2011 at 7:34 am
sqlbuddy123 (1/12/2011)
What is the Size of the Page File ?Try to disable any unnecassary or unused windows services on the server and see it it frees up some memory on the box.
You can also see if any other windows processes are using more memory from the Task Manager.
Also Non Bufferpool Memory components of SQL Server could be consuming much of that memory based on the high server activity .
Also try to use Optimize for Adhoc workload option, which will greatly reduce the memory consumption, if a lot of adhoc queries are being submitted to the server.
Page file size is quite low compared to the physical RAM. We have only 20 GB of pagefile. Even though it is recommended to have Pagefile almost equal to or greater than physical RAM, we set it up this way. I read quite a bit of White papers stating that for SQL Server not to have a big page file. Basically because if the SQL Server is paging, that means we have a big problem.
We have made sure that there are no unwanted services running on the box.
-Roy
January 13, 2011 at 7:35 am
Roy Ernest (1/13/2011)
Paul, The system_cache_kb is currently 593464. Unfortunately all the memory counters are kind of useless since I did a controlled reboot of the Server.
Much better than an uncontrolled reboot 😛 🙂
Monitor that figure...it's 593MB already. Maybe have a chat with your server configuration guys about it if Windows caching gets out of hand. IIRC there are some interesting issues around Windows Server 2008 in this area. I forget the details. Sorry.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 13, 2011 at 7:38 am
george sibbald (1/12/2011)
have you used sysmon to track memory usage by process? Use private bytes and working set counters for all instances under the process object.If you see an instance slowly increasing there is your memory leak. AV is a possible candidate.
The problem with Sysmon is that it does not collect more data than Performance counters does. We are already using performance monitor which is less intrusive on the server than Sysmon.
-Roy
January 13, 2011 at 7:47 am
Paul, I will keep an eye out on that figure. I hope it does not grow out of hand pretty quickly. One good thing is that we do not see any IO bottle necks. The guy who set up the server spend nearly 4 months just to find the right configuration for the HW setup including IO.
We are planning on a Hardware upgrade since we are expecting a growth by 40%.
My aim is to increase the physical RAM to a very high amount so that the whole DB can reside in memory if needed. 🙂 Currently it is a HP DL 580 G5. I am thinking of going to the DL 700 Series.
-Roy
January 13, 2011 at 8:02 am
Roy Ernest (1/13/2011)
george sibbald (1/12/2011)
have you used sysmon to track memory usage by process? Use private bytes and working set counters for all instances under the process object.If you see an instance slowly increasing there is your memory leak. AV is a possible candidate.
The problem with Sysmon is that it does not collect more data than Performance counters does. We are already using performance monitor which is less intrusive on the server than Sysmon.
perfmon\sysmon - same thing.
are you tracking the process object counters? Dead giveaway to anything slowly and linearly consuming memory,especially now you have just rebooted.
---------------------------------------------------------------------
January 13, 2011 at 8:08 am
Thanks George. I will keep track of it.
-Roy
January 13, 2011 at 10:04 am
Hi Roy,
[SQLServer:Memory Manager][Target Server Memory (KB)] 221.000
[SQLServer:Memory Manager][Total Server Memory (KB)] 29.599
Why this disparity ? SQL wants to use 221 GB. But SQL is using only 29.5 GB.
But the total memory consumption on the server is round 255 GB. There clearly indicates that there ar some other processes on the server that are using this additional memory.
This is the problem if you don't lock the SQL Pages in Memory. SQL Server will have to starve for memory during the busiest activity on SQL Server.
Also the size of the Page file should be atleast 1.5 to 2 times than of the Physical RAM.
This is because other processes use the Page File instead of the actual physical RAM when SQL is using that 221 GB of RAM. So if Lock Pges in Mem is not assigned to SQL then SQL Pages have to be paged out and the other processes will be using the actual Physical RAM instead of using the pge file.
Thank You,
Best Regards,
SQLBuddy
January 13, 2011 at 10:10 am
sqlbuddy123 (1/13/2011)
Hi Roy,[SQLServer:Memory Manager][Target Server Memory (KB)] 221.000
[SQLServer:Memory Manager][Total Server Memory (KB)] 29.599
Why this disparity ? SQL wants to use 221 GB. But SQL is using only 29.5 GB.
But the total memory consumption on the server is round 255 GB. There clearly indicates that there ar some other processes on the server that are using this additional memory.
...
He said he just restarted SQL Server, it probably just hadn't allocated that memory yet. SQL Server only adds memory when it needs it.
January 13, 2011 at 10:57 am
Oops.. I missed the Restart.
Thank You,
Best Regards,
SQLBuddy
January 13, 2011 at 10:58 am
Micheal, You are right. I restarted the server about 7 hrs ago. Currently the Total Server memory is 35.400. It is increasing.
Regarding pagefile size, there are lots of confusing ideas out there. For normal application and file servers, you set the size as 1 to 1.5 times that of physical RAM. In our case you are talking about 400GB of page file size. But for a server that hosts only SQL Server, there are some white papers that say not to go that high if you have very high Physical RAM.
I did not see any paging when I ran the counters at the busiest time. That is around 13K batch request per second. There are arguments for and against Lock pages in memory. Everyone says if you see paging on SQL server, you should have lock pages in memory. Otherwise leave it as it is.
I probably will set Lock Pages in memory after I have finished analyzing what is eating up the remaining 35 GB. But the disadvantage of setting up Lock Page in memory is the start up time will be higher since it enables large Page Extensions if I am not mistaken.
-Roy
January 13, 2011 at 5:26 pm
Roy Ernest (1/13/2011)
Regarding pagefile size, there are lots of confusing ideas out there. For normal application and file servers, you set the size as 1 to 1.5 times that of physical RAM. In our case you are talking about 400GB of page file size. But for a server that hosts only SQL Server, there are some white papers that say not to go that high if you have very high Physical RAM.
I agree. A large page file is not required for a dedicated SQL Server. A well-configured server like that would hardly ever use the page file. Some installations I have seen ran quite happily without a paging file at all - though I don't personally recommend it. A much smaller paging file, (say 8-16GB) would be perfectly adequate.
I did not see any paging when I ran the counters at the busiest time. That is around 13K batch request per second. There are arguments for and against Lock pages in memory. Everyone says if you see paging on SQL server, you should have lock pages in memory. Otherwise leave it as it is.
The point about locking pages is that it prevents the OS ever paging out SQL Server's working set. All it takes is one occasion when something unexpected starts requesting large chunks of memory - I have seen at least one large international website brought down by this. Sensibly configured lock pages in memory and max server memory safeguards against this scenario. I always reverse the question: describe the advantages of not enabling locked pages!
I probably will set Lock Pages in memory after I have finished analyzing what is eating up the remaining 35 GB. But the disadvantage of setting up Lock Page in memory is the start up time will be higher since it enables large Page Extensions if I am not mistaken.
Only if you deliberately enable trace flag 834. See http://blogs.msdn.com/b/psssql/archive/2009/06/05/sql-server-and-large-pages-explained.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 13, 2011 at 5:37 pm
Just noticed the title of this thread is memROY issues! :laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 14, 2011 at 5:17 am
I did not know that I had a typo in my thread description..... LOL. Well, it suits this thread.. 🙂
-Roy
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply