|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL SErver 64 bit use of memory
Hello,
Server 2003 EE sp 2 64 bit 8 gb of RAM SQL Server STD edition sp 2 64 bit 6144 max server memory. AWE is set. Lock pages in memory is set. SQL has not been stopped/started since Sept. Page Life Expectancy = 1379759 Target server memory = 5.9 GB Total server memory = 1.788 GB Total size of all databases = 690 MB. AWE and lock pages in memory are set due to "real world" experience about cache flushing on 64 bit STD edition. I would expect total server memory to be much closer to target, any ideas as to why this might not be the case. TIA< First off you do not need AWE if you are running 64 bit so you can turn that
off. But the bottom line is that you have far less data than you have memory so there is no need to use all that you have available. If your dbs total only 690MB in size you probably always have them in cache unless you restart. The rest is probably related to poor reuse of your query plans and making the proc cache larger than it should be. Since the memory is dynamic in 64 bit it will not use more memory than it needs and that is far below the target of what it can use. This is normal and to be expected. -- Show quoteHide quoteAndrew J. Kelly SQL MVP Solid Quality Mentors "Joe" <J**@discussions.microsoft.com> wrote in message news:EA88C84D-5D97-4663-949E-D6CFE8504A62@microsoft.com... > Hello, > Server 2003 EE sp 2 64 bit 8 gb of RAM > SQL Server STD edition sp 2 64 bit 6144 max server memory. > AWE is set. Lock pages in memory is set. SQL has not been > stopped/started > since Sept. > Page Life Expectancy = 1379759 > Target server memory = 5.9 GB > Total server memory = 1.788 GB > Total size of all databases = 690 MB. > > AWE and lock pages in memory are set due to "real world" experience about > cache flushing on 64 bit STD edition. > I would expect total server memory to be much closer to target, any ideas > as to why this might not be the case. > TIA< > Thanks for the response. My initial thought was the total data size as well
but wanted someone elses opinion on that, I had started 2nd guessing myself. Not to start an argument but I have seen a positive impact by enabling AWE and lock pages in memory in SQL SErver 2005 STD edition. Perhaps it is the placebo effect. Show quoteHide quote "Andrew J. Kelly" wrote: > First off you do not need AWE if you are running 64 bit so you can turn that > off. But the bottom line is that you have far less data than you have memory > so there is no need to use all that you have available. If your dbs total > only 690MB in size you probably always have them in cache unless you > restart. The rest is probably related to poor reuse of your query plans and > making the proc cache larger than it should be. Since the memory is dynamic > in 64 bit it will not use more memory than it needs and that is far below > the target of what it can use. This is normal and to be expected. > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > > "Joe" <J**@discussions.microsoft.com> wrote in message > news:EA88C84D-5D97-4663-949E-D6CFE8504A62@microsoft.com... > > Hello, > > Server 2003 EE sp 2 64 bit 8 gb of RAM > > SQL Server STD edition sp 2 64 bit 6144 max server memory. > > AWE is set. Lock pages in memory is set. SQL has not been > > stopped/started > > since Sept. > > Page Life Expectancy = 1379759 > > Target server memory = 5.9 GB > > Total server memory = 1.788 GB > > Total size of all databases = 690 MB. > > > > AWE and lock pages in memory are set due to "real world" experience about > > cache flushing on 64 bit STD edition. > > I would expect total server memory to be much closer to target, any ideas > > as to why this might not be the case. > > TIA< > > > > AWE is useless in 64 bit but Lock Pages can certainly make a difference. But
it is not a SQL Server setting, it is a windows one and since you are using Windows EE it will work. -- Show quoteHide quoteAndrew J. Kelly SQL MVP Solid Quality Mentors "Joe" <J**@discussions.microsoft.com> wrote in message news:B35A9BD0-C55E-4E88-8DB1-12AF6A1B4A16@microsoft.com... > Thanks for the response. My initial thought was the total data size as > well > but > wanted someone elses opinion on that, I had started 2nd guessing myself. > Not to start an argument but I have seen a positive impact by enabling > AWE > and lock pages in memory in SQL SErver 2005 STD edition. Perhaps it is > the > placebo effect. > > "Andrew J. Kelly" wrote: > >> First off you do not need AWE if you are running 64 bit so you can turn >> that >> off. But the bottom line is that you have far less data than you have >> memory >> so there is no need to use all that you have available. If your dbs total >> only 690MB in size you probably always have them in cache unless you >> restart. The rest is probably related to poor reuse of your query plans >> and >> making the proc cache larger than it should be. Since the memory is >> dynamic >> in 64 bit it will not use more memory than it needs and that is far below >> the target of what it can use. This is normal and to be expected. >> >> -- >> Andrew J. Kelly SQL MVP >> Solid Quality Mentors >> >> >> "Joe" <J**@discussions.microsoft.com> wrote in message >> news:EA88C84D-5D97-4663-949E-D6CFE8504A62@microsoft.com... >> > Hello, >> > Server 2003 EE sp 2 64 bit 8 gb of RAM >> > SQL Server STD edition sp 2 64 bit 6144 max server memory. >> > AWE is set. Lock pages in memory is set. SQL has not been >> > stopped/started >> > since Sept. >> > Page Life Expectancy = 1379759 >> > Target server memory = 5.9 GB >> > Total server memory = 1.788 GB >> > Total size of all databases = 690 MB. >> > >> > AWE and lock pages in memory are set due to "real world" experience >> > about >> > cache flushing on 64 bit STD edition. >> > I would expect total server memory to be much closer to target, any >> > ideas >> > as to why this might not be the case. >> > TIA< >> > >> >> We are having a performance problem with 64Gb RAM on 2005 STD on W2K3 Server
64bit EE. This problem does not present itself when the memory available to SQL Server is locked to 16Gb. We have a case open with M$ about it and here is an interesting article on possible causes: http://support.microsoft.com/kb/918483 The reason I am mentioning it is because the above article states that SQL 2005 STD edition does not support the lock pages in memory privilege. You need SQL 2005 EE to support Lock Pages. So 64Bit Windows does not need AWE as it's function is to allow 32bit systems to address memory outside the scope of 32bit numbering. As far as I am concerned, this is a crime. M$ are preventing a non-functioning product from functioning. If they allowed STD to support LPiM then our problems may be solved. In our active/pasive failover configuration, the licensing implications for moving from STD to EE involve a hike in fees of fourfold. Show quoteHide quote "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:%23GpxbPcVJHA.4680@TK2MSFTNGP06.phx.gbl... > AWE is useless in 64 bit but Lock Pages can certainly make a difference. > But it is not a SQL Server setting, it is a windows one and since you are > using Windows EE it will work. > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > > "Joe" <J**@discussions.microsoft.com> wrote in message > news:B35A9BD0-C55E-4E88-8DB1-12AF6A1B4A16@microsoft.com... >> Thanks for the response. My initial thought was the total data size as >> well >> but >> wanted someone elses opinion on that, I had started 2nd guessing myself. >> Not to start an argument but I have seen a positive impact by enabling >> AWE >> and lock pages in memory in SQL SErver 2005 STD edition. Perhaps it is >> the >> placebo effect. >> >> "Andrew J. Kelly" wrote: >> >>> First off you do not need AWE if you are running 64 bit so you can turn >>> that >>> off. But the bottom line is that you have far less data than you have >>> memory >>> so there is no need to use all that you have available. If your dbs >>> total >>> only 690MB in size you probably always have them in cache unless you >>> restart. The rest is probably related to poor reuse of your query plans >>> and >>> making the proc cache larger than it should be. Since the memory is >>> dynamic >>> in 64 bit it will not use more memory than it needs and that is far >>> below >>> the target of what it can use. This is normal and to be expected. >>> >>> -- >>> Andrew J. Kelly SQL MVP >>> Solid Quality Mentors >>> >>> >>> "Joe" <J**@discussions.microsoft.com> wrote in message >>> news:EA88C84D-5D97-4663-949E-D6CFE8504A62@microsoft.com... >>> > Hello, >>> > Server 2003 EE sp 2 64 bit 8 gb of RAM >>> > SQL Server STD edition sp 2 64 bit 6144 max server memory. >>> > AWE is set. Lock pages in memory is set. SQL has not been >>> > stopped/started >>> > since Sept. >>> > Page Life Expectancy = 1379759 >>> > Target server memory = 5.9 GB >>> > Total server memory = 1.788 GB >>> > Total size of all databases = 690 MB. >>> > >>> > AWE and lock pages in memory are set due to "real world" experience >>> > about >>> > cache flushing on 64 bit STD edition. >>> > I would expect total server memory to be much closer to target, any >>> > ideas >>> > as to why this might not be the case. >>> > TIA< >>> > >>> >>> >  
Other interesting topics
Error logs enormous. Can I delete or save elsewhere?
MS Access, ODBC, SQL 2005, delays before data appears Script to delete records from a table older than N number of days. in full recovery mode but log keeps self-truncating Server Disk Space Max server memory being ignored Security Question how to store decimals in tables The time stamp counter of CPU on scheduler id X is not synchronized with other CPUs Cumulative package 2 for SQL 2008 |
|||||||||||||||||||||||
