November 20, 2007 at 1:01 am
I have a SQL 2005 ent x64 bit in windows server 2003 ent x64 bit R2.
When sql user login then it uses more memory above 7 GB out of 8 GB.
But after office our the server shows that the memory uses below 1GB.
How can I find/monitor out which sql application/service uses more memory
in office our.
PLZ help me with any idea
November 21, 2007 at 1:47 am
The behavior you're seeing is pretty normal for a SQL 2005 instance not configured to use a minimum/maximum amount of memory. SQL 2005 "self-tunes" and will raise and lower the amount of memory in use based on load and memory availability - that memory use is partly memory used for maintaining connections but the vast majority is probably being used to cache data/results. As load on the server goes does down SQL will expire data from its' cache and release that memory back to the operating system.
I've seen SQL 2005 instances using as little as 65K when idle that readily jump to nearly all the available memory on the machine when under heavy use.
If the machine in question is dedicated to SQL Server I'd probably leave well enough alone unless you see a specific reason to set an upper limit on the amount of memory that SQL Server should be allowed to use?
November 21, 2007 at 2:28 am
As with sql 2k5 memory architecture, specially on x64 (keep in mind that AWE is enabled by default here), it is highly recommended to set up the min/max memory for the instance if your app is memory bound. This is because the internal code can generate larger memory requests which can exceed a single page size as with 32 bit installations, but there are actualy many other reasons why MS recommends this. The more RAM you have on the machine, the more important is to set the min/max memory.You have to leave enough memory for the OS; for example, on a 8gb machine, I would set up the max memory to 6-7GB, and the os will have 1-2 gb. Also it is for the better to enable lock pages in memory permission for sql server service account - read in BOL what's this.
To check who is consuming memory, open up activity monitor in management studio and order it by memory desc; for more details you can check using the relevant dmvs.
Hope this helps.
November 21, 2007 at 2:46 am
Just too clarify one point, lock pages in memory is not supported by sql2005 Standard edition. You can set it but it won't do anything.
thanks
Andy.
November 21, 2007 at 2:54 am
With 64-bit SQL Server you should always set the maximum memory SQL can use. By default, SQL will try to use the total of physical memory and pagefile space, leading to lots of paging and poor performance.
If SQL is the only application on the box, set SQL memory to 1 GB to 2 GB less than total server memory. If you have other applications (Full Text Search, SSIS, AS, CLR applications, etc) you must allow enough memory for these to run and therefore lower the memory dedicated to SQL Server.
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
November 21, 2007 at 3:09 am
mcclurea (11/21/2007)
Just too clarify one point, lock pages in memory is not supported by sql2005 Standard edition. You can set it but it won't do anything.thanks
Andy.
Andy, I'm curious what does that mean and where did you get that info from. Are you saying that if you assign the win "lock pages in memory" permission to the sql server service account when x64 standard edition, this is not supported by whom? Thanks.
November 21, 2007 at 3:23 am
Hi,
Just I found a good link http://blogs.technet.com/askperf/archive/2007/05/18/sql-and-the-working-set.aspx
Regards,
Ahmed
November 21, 2007 at 3:25 am
November 21, 2007 at 3:37 am
Andy, the forum chat you've sent me to may be confusing. I do not use Standard edition, however BOL clearly states this recommendation for x64 Entreprise or Standard. This is the x64 memory architecture and does nothing in common with the limitations from Enterprise to Standard editions. Thanks for your response.
November 21, 2007 at 6:25 am
it is critical on x64 platform to set the maximum sql server memory. If you don't you will get problems.
BOL explains how sql server uses memory and it's been this way since 7.0 so have a good read to understand server config, or better still get yourself onto a training course.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 21, 2007 at 7:06 am
It is true that Lock Pages in Memory only works for Enterprise Edition of SQL Server. However, you should be aware that not allowing memory to be trimmed can lead to extremely poor performance and even server hangs, especially if your max memory setting in SQL Server is too high.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 21, 2007 at 9:32 am
Can someone provide a cite to the recommendation to set a maximum memory limit please, that's a new one on me.
November 21, 2007 at 10:19 am
Max Mem setting varies depending on numberous factors including all stuff running on the server, server RAM, network needs, etc. It should also be altered by watching pages/sec setting in profiler to see if stuff is paging to disk. My basic guideline is (all with NOTHING but sql server relational engine running on server):
2GB box: 1.3GB
4GB box: 3.2GB
8GB box: 6GB
16GB box: 13GB
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 21, 2007 at 11:13 am
Found something worth a read on Salva Ok's blog - as always, there are caveats to altering default SQL Server settings...
He's got some very good articles about memory pressure, etc. for those interested.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply