Memory fragmentation?
-
I have a situation where after the SQL Server is operational for about a week, then steadily slows down. If I reboot the server, query performance is back to normal levels.
I think I may have a situation of memory fragmentation. If so, what tools / performance counters should I be looking at to determine memory fragmentation for SQL Server, and what steps should I take to resolve and mitigate it?
I'm running SQL Server 2005 SP2 x64 with Windows Server 2003 SP1 x64.
-
Could you post the memory details. IS AWE used in the server? IS static memory being used? Size of each database.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Yes, I have enabled the local policy for "lock pages in memory" for the SQL service account and have selected AWE enabled for the instance. I have minimum server memory set for 1024 MB and maximum server memory set for 13107 MB. Total memory on the server is 16379 MB.
I have 11 user databases installed.
Database1 - 158 MB
Database2 - 28323 MB
Database3 - 164975 MB
Database4 - 12903 MB
Database5 - 113804 MB
Database6 - 23 MB
Database7 - 3 MB
Database8 - 3 MB
Database9 - 11 MB
Database10 - 554 MB
Database11 - 38 MB
System databases and sizes...
Master - 9 MB
Model - 3 MB
MSDB - 550 MB
TempDB - 15000 MB
-
If your running x64 there is no reason for AWE to be enabled. That's a 32 bit hack.
-
For more then 16GB RAM you have to use /PAE switch and not AWE. The link given below can be more useful.
http://www.sql-server-performance.com/awe_memory.asp
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com - "If your running x64 there is no reason for AWE to be enabled. That's a 32 bit hack."
Actually, thats not exactly true... I used to think so myself, until I saw this...
http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx
I've also seen other technet articles on this, and will be glad to post those references as well. I have found having this enabled on the X64 platform has a large improvement in overall server performance.
-
I wonder if a combination of both AWE and the /PAE switch is whats needed.
Its hard to determine, the documentation around this issue seems to be highly conflicting. The SQL Server books online say that AWE is not used in the 64 bit environment, but technet and other microsoft blogs seem to differ.
Is there a definitive answer on this?
-
Wow.. good read.. Thanks!
As for the /PAE.. according to this:
http://www.microsoft.com/whdc/system/platform/server/PAE/pae_os.mspx
/PAE is not supported on 64 bit systems..
As for your issue.. perhaps the memory locking is being handled poorly with AWE enabled, maybe if you disabled AWE for a week to see if you still experience this progressive slowdown..
Could other apps on the server be causing a slowdown?
And just for my curiosity, how much of an improvement did you see with AWE enabled? I may try it out in on of our dev environments..
-
Well, let me put it this way. Server and query performance was extremely slow before I came here. When I read that article I posted, I enabled AWE on this box and saw almost a 50% performance improvement overall. Query times were literally cut in half!
As far as other apps are concerned, the server has Reporting Services installed. As far as what I can see from the memory consumption, RS is not using a whole lot of RAM or processor cycles.
I seriously think this may be an issue of memory fragmentation over time. I just need the counters to prove it. If I even knew which counters to watch, I could prove my theory.
-
Jeremy,
Are you sure your performance gain comes from AWE? As far as i know, AWE, PAE and 3GB are not needed for a 64-bit SQL.
I do notice your tempdb obnoxiously large though (15GB). Are you sure your performance hit is not from tempdb? E.g. too much sorting, etc and your tempdb keep expanding and this takes time..
cheers,
Simon
Simon Liew
Microsoft Certified Master: SQL Server 2008 -
from what I read on AWE and 64 bit it depends. you have to test and some workloads will benefit and others will be worse. Slava doesn't say to do it, he just says that it can be good depending on what you do
-
"I seriously think this may be an issue of memory fragmentation over time. I just need the counters to prove it. If I even knew which counters to watch, I could prove my theory."
I think you may be right. I have run into this as well. The memory does fragment and it does have an effect on performance. I am seeking the same information you asked for: how would you go about defragmenting memory in SQL Server?
Try DBCC MEMORYSTATUS and EXEC master..sqbmemory to get a feel for what the space looks like. You might also have a look at sys.dm_os_memory_objects and related DMV's.
I know mine is fragmented - ran into the issue today when RedGate Backup started dying because it couldn't allocate enough 1 MB blocks to do a backup.
-
Just to make things clear in regards to 64-bit and AWE, I've checked multiple times with M$, all answers are nonono, do NOT do it, its unnecessary. If you get performance improvement, its something else.
So happen was browsing the blogs and found an article from slavao, he has also apologised for the confusion between 64-bit and AWE.
FACT: AWE on 64-bit IS UNNECESSARY.
LOCK PAGES IN MEMORY IN 64-bit: YES (even with this, SQL "might" still release memory under certain condition)
http://blogs.msdn.com/slavao/archive/2006/03/13/550594.aspx
Simon
Simon Liew
Microsoft Certified Master: SQL Server 2008 -
Oh yes, by the way, I was talking to M$ in regards to the blog article and below was a copy and paste from part of our correspondence:
2nd reply
I think he was saying AWE mechanism of locking pages, but AWE APIs shouldn’t be used anymore because the VAS for user processes are so much larger now.
1st reply
Yes. It’s saying that you need to enable ‘locked pages in memory’ (it’s an AWE mechanism, but it’s not on by default even on 64-bit systems) if you really wanted to take advantage of application performance by not allowing the OS to do the memory trimming but let the app (SQL Server) in this case handle the trimming. But you don’t need to enable AWE anymore nor does 64-bit OS requires it, it just requires pages not to be paged out from memory by OS by enabling the "locked pages in memory" option.
Hopefully the info is useful in some way to you guys..
Simon
Simon Liew
Microsoft Certified Master: SQL Server 2008 -
Is there a way I can fragment memory on test server to reproduce the errors? I read that running cursors and other queries simultaneously does fragment memory. Any Ideas?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply