Make SQL use more memory

I've got a Windows 2003 Enterprise server with 8GB of memory running
SQL 2005 Standard (32 bit). According to the documentation SQL can
take up to the OS maximum amount of memory (64 GB with PAE). Well,
SQL isn't taking more than about 1.5GB of memory, and the Buffer Cache
Hit Ratio is up close to 100. How can I make SQL use more physical
memory?
Thanks
DylanIf the Buffer Cache Hit Ratio is 100, that means that SQL Server doesn't
need to take any more memory. Your queries are all using data that's
already cached. SQL Server will not take memory it doesn't need.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1182433233.008715.247450@.o61g2000hsh.googlegroups.com...
> I've got a Windows 2003 Enterprise server with 8GB of memory running
> SQL 2005 Standard (32 bit). According to the documentation SQL can
> take up to the OS maximum amount of memory (64 GB with PAE). Well,
> SQL isn't taking more than about 1.5GB of memory, and the Buffer Cache
> Hit Ratio is up close to 100. How can I make SQL use more physical
> memory?
> Thanks
> Dylan
>|||On Jun 21, 9:47 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> If the Buffer Cache Hit Ratio is 100, that means that SQL Server doesn't
> need to take any more memory. Your queries are all using data that's
> already cached. SQL Server will not take memory it doesn't need.
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182433233.008715.247450@.o61g2000hsh.googlegroups.com...
> > I've got a Windows 2003 Enterprise server with 8GB of memory running
> > SQL 2005 Standard (32 bit). According to the documentation SQL can
> > take up to the OS maximum amount of memory (64 GB with PAE). Well,
> > SQL isn't taking more than about 1.5GB of memory, and the Buffer Cache
> > Hit Ratio is up close to 100. How can I make SQL use more physical
> > memory?
> > Thanks
> > Dylan
Ah, so you're right - I was reading my thresholds sheet wrong. I guess
I should have thought about what a ratio is before posting.
In any event, this SQL server's not very highly utilized right now.
If it becomes more highly utilized and does in fact need to take more
memory, will it, or do I need to change anything? I was reading about
enabling AWE - is that something that I should have enabled?
Thanks!
Dylan|||"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1182434972.723295.308160@.o61g2000hsh.googlegroups.com...
> In any event, this SQL server's not very highly utilized right now.
> If it becomes more highly utilized and does in fact need to take more
> memory, will it, or do I need to change anything? I was reading about
> enabling AWE - is that something that I should have enabled?
Yes, you'll have to enable AWE if you want SQL Server to use any RAM
over the 2GB user-mode VAS limit.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220|||On Jun 21, 10:16 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182434972.723295.308160@.o61g2000hsh.googlegroups.com...
>
> > In any event, this SQL server's not very highly utilized right now.
> > If it becomes more highly utilized and does in fact need to take more
> > memory, will it, or do I need to change anything? I was reading about
> > enabling AWE - is that something that I should have enabled?
> Yes, you'll have to enable AWE if you want SQL Server to use any RAM
> over the 2GB user-mode VAS limit.
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220
Great, thanks - one last question. I see that you need to enable Lock
Pages in Memory access through group Policy. It says to:
In the Select Users or Groups dialog box, add an account with
privileges to run sqlservr.exe.
Is that referring to the user that the MSSQLSERVER service logs in as
when it starts, or is that just any user with login access to the
database? It seems like no matter what account (or accounts) I give
that access to, SQL still spits out an error saying that they don't
have access.|||"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1182436089.647770.79590@.p77g2000hsh.googlegroups.com...
> Great, thanks - one last question. I see that you need to enable Lock
> Pages in Memory access through group Policy. It says to:
As far as I know, that's only applicable to 64-bit, Enterprise Edition
SQL Server.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220|||On Jun 21, 10:34 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182436089.647770.79590@.p77g2000hsh.googlegroups.com...
>
> > Great, thanks - one last question. I see that you need to enable Lock
> > Pages in Memory access through group Policy. It says to:
> As far as I know, that's only applicable to 64-bit, Enterprise Edition
> SQL Server.
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220
Hmm, well according to this article http://msdn2.microsoft.com/en-us/library/ms190673(SQL.90).aspx
AWE is not needed and cannot be configured on 64-bit operating systems.|||"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1182437032.054788.207030@.n60g2000hse.googlegroups.com...
> Hmm, well according to this article
> http://msdn2.microsoft.com/en-us/library/ms190673(SQL.90).aspx
> AWE is not needed and cannot be configured on 64-bit operating systems.
AWE is needed for 32-bit SQL Server, whether running on a 32-bit or
64-bit operating system. Lock Pages in Memory is only needed for 64-bit SQL
Server. You're running 32-bit SQL Server, according to your first post?
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220|||On Jun 21, 11:09 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182437032.054788.207030@.n60g2000hse.googlegroups.com...
>
> > Hmm, well according to this article
> >http://msdn2.microsoft.com/en-us/library/ms190673(SQL.90).aspx
> > AWE is not needed and cannot be configured on 64-bit operating systems.
> AWE is needed for 32-bit SQL Server, whether running on a 32-bit or
> 64-bit operating system. Lock Pages in Memory is only needed for 64-bit SQL
> Server. You're running 32-bit SQL Server, according to your first post?
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220
I really need to read these things better, I didn't see in your
previous post that you were talking about Enterprise Edition SQL
Server, I thought you meant 64 Bit Windows 2003 Enterprise Edition.
Yes, I am running 32 bit SQL Server Standard on 32 Bit Windows 2003
Enterprise. Given that, I can't get AWE to enable. When I try to
enable it, I get the following error:
Alter Failed (Microsoft.SQLServer.SMO)
Additional information
+ An exception occured while executing a Transact-SQL statement or
batch.
(Microsoft.SQLServer.ConnectionInfo)
+ Address Windowing Extensions (AWE) requires the "lock pages in
memory" privilege which is not currently present in the access token
of the process.
When I looked that up, I found that I needed to enable the Enable the
Lock Pages in Memory Option. Using this article
http://msdn2.microsoft.com/en-us/library/ms190730(SQL.90).aspx I tried
to do that, but it is still not working. Any insight?
Thanks very much for your help.|||Do you have the /PAE switch enabled in boot.ini ?
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1182440393.064379.241090@.p77g2000hsh.googlegroups.com...
> On Jun 21, 11:09 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
> wrote:
>> "dylan" <dylan.roeh...@.gmail.com> wrote in message
>> news:1182437032.054788.207030@.n60g2000hse.googlegroups.com...
>>
>> > Hmm, well according to this article
>> >http://msdn2.microsoft.com/en-us/library/ms190673(SQL.90).aspx
>> > AWE is not needed and cannot be configured on 64-bit operating systems.
>> AWE is needed for 32-bit SQL Server, whether running on a 32-bit or
>> 64-bit operating system. Lock Pages in Memory is only needed for 64-bit
>> SQL
>> Server. You're running 32-bit SQL Server, according to your first post?
>> --
>> Adam Machanic
>> SQL Server MVP
>> Author, "Expert SQL Server 2005
>> Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> I really need to read these things better, I didn't see in your
> previous post that you were talking about Enterprise Edition SQL
> Server, I thought you meant 64 Bit Windows 2003 Enterprise Edition.
> Yes, I am running 32 bit SQL Server Standard on 32 Bit Windows 2003
> Enterprise. Given that, I can't get AWE to enable. When I try to
> enable it, I get the following error:
> Alter Failed (Microsoft.SQLServer.SMO)
> Additional information
> + An exception occured while executing a Transact-SQL statement or
> batch.
> (Microsoft.SQLServer.ConnectionInfo)
> + Address Windowing Extensions (AWE) requires the "lock pages in
> memory" privilege which is not currently present in the access token
> of the process.
> When I looked that up, I found that I needed to enable the Enable the
> Lock Pages in Memory Option. Using this article
> http://msdn2.microsoft.com/en-us/library/ms190730(SQL.90).aspx I tried
> to do that, but it is still not working. Any insight?
> Thanks very much for your help.
>|||On Jun 21, 11:53 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> Do you have the /PAE switch enabled in boot.ini ?
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182440393.064379.241090@.p77g2000hsh.googlegroups.com...
> > On Jun 21, 11:09 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
> > wrote:
> >> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> >>news:1182437032.054788.207030@.n60g2000hse.googlegroups.com...
> >> > Hmm, well according to this article
> >> >http://msdn2.microsoft.com/en-us/library/ms190673(SQL.90).aspx
> >> > AWE is not needed and cannot be configured on 64-bit operating systems.
> >> AWE is needed for 32-bit SQL Server, whether running on a 32-bit or
> >> 64-bit operating system. Lock Pages in Memory is only needed for 64-bit
> >> SQL
> >> Server. You're running 32-bit SQL Server, according to your first post?
> >> --
> >> Adam Machanic
> >> SQL Server MVP
> >> Author, "Expert SQL Server 2005
> >> Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> > I really need to read these things better, I didn't see in your
> > previous post that you were talking about Enterprise Edition SQL
> > Server, I thought you meant 64 Bit Windows 2003 Enterprise Edition.
> > Yes, I am running 32 bit SQL Server Standard on 32 Bit Windows 2003
> > Enterprise. Given that, I can't get AWE to enable. When I try to
> > enable it, I get the following error:
> > Alter Failed (Microsoft.SQLServer.SMO)
> > Additional information
> > + An exception occured while executing a Transact-SQL statement or
> > batch.
> > (Microsoft.SQLServer.ConnectionInfo)
> > + Address Windowing Extensions (AWE) requires the "lock pages in
> > memory" privilege which is not currently present in the access token
> > of the process.
> > When I looked that up, I found that I needed to enable the Enable the
> > Lock Pages in Memory Option. Using this article
> >http://msdn2.microsoft.com/en-us/library/ms190730(SQL.90).aspxI tried
> > to do that, but it is still not working. Any insight?
> > Thanks very much for your help.
Yes I do.|||Can you go into gpedit and make sure the perms are set properly, then go
back out to a command prompt, run gpupdate, and try again?
--
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1182442259.823849.93310@.q75g2000hsh.googlegroups.com...
> On Jun 21, 11:53 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
> wrote:
>> Do you have the /PAE switch enabled in boot.ini ?
>> --
>> Adam Machanic
>> SQL Server MVP
>> Author, "Expert SQL Server 2005
>> Development"http://www.apress.com/book/bookDisplay.html?bID=10220
>> "dylan" <dylan.roeh...@.gmail.com> wrote in message
>> news:1182440393.064379.241090@.p77g2000hsh.googlegroups.com...
>> > On Jun 21, 11:09 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
>> > wrote:
>> >> "dylan" <dylan.roeh...@.gmail.com> wrote in message
>> >>news:1182437032.054788.207030@.n60g2000hse.googlegroups.com...
>> >> > Hmm, well according to this article
>> >> >http://msdn2.microsoft.com/en-us/library/ms190673(SQL.90).aspx
>> >> > AWE is not needed and cannot be configured on 64-bit operating
>> >> > systems.
>> >> AWE is needed for 32-bit SQL Server, whether running on a 32-bit
>> >> or
>> >> 64-bit operating system. Lock Pages in Memory is only needed for
>> >> 64-bit
>> >> SQL
>> >> Server. You're running 32-bit SQL Server, according to your first
>> >> post?
>> >> --
>> >> Adam Machanic
>> >> SQL Server MVP
>> >> Author, "Expert SQL Server 2005
>> >> Development"http://www.apress.com/book/bookDisplay.html?bID=10220
>> > I really need to read these things better, I didn't see in your
>> > previous post that you were talking about Enterprise Edition SQL
>> > Server, I thought you meant 64 Bit Windows 2003 Enterprise Edition.
>> > Yes, I am running 32 bit SQL Server Standard on 32 Bit Windows 2003
>> > Enterprise. Given that, I can't get AWE to enable. When I try to
>> > enable it, I get the following error:
>> > Alter Failed (Microsoft.SQLServer.SMO)
>> > Additional information
>> > + An exception occured while executing a Transact-SQL statement or
>> > batch.
>> > (Microsoft.SQLServer.ConnectionInfo)
>> > + Address Windowing Extensions (AWE) requires the "lock pages in
>> > memory" privilege which is not currently present in the access token
>> > of the process.
>> > When I looked that up, I found that I needed to enable the Enable the
>> > Lock Pages in Memory Option. Using this article
>> >http://msdn2.microsoft.com/en-us/library/ms190730(SQL.90).aspxI tried
>> > to do that, but it is still not working. Any insight?
>> > Thanks very much for your help.
> Yes I do.
>|||On Jun 21, 12:20 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> Can you go into gpedit and make sure the perms are set properly, then go
> back out to a command prompt, run gpupdate, and try again?
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182442259.823849.93310@.q75g2000hsh.googlegroups.com...
> > On Jun 21, 11:53 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
> > wrote:
> >> Do you have the /PAE switch enabled in boot.ini ?
> >> --
> >> Adam Machanic
> >> SQL Server MVP
> >> Author, "Expert SQL Server 2005
> >> Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> >> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> >>news:1182440393.064379.241090@.p77g2000hsh.googlegroups.com...
> >> > On Jun 21, 11:09 am, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
> >> > wrote:
> >> >> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> >> >>news:1182437032.054788.207030@.n60g2000hse.googlegroups.com...
> >> >> > Hmm, well according to this article
> >> >> >http://msdn2.microsoft.com/en-us/library/ms190673(SQL.90).aspx
> >> >> > AWE is not needed and cannot be configured on 64-bit operating
> >> >> > systems.
> >> >> AWE is needed for 32-bit SQL Server, whether running on a 32-bit
> >> >> or
> >> >> 64-bit operating system. Lock Pages in Memory is only needed for
> >> >> 64-bit
> >> >> SQL
> >> >> Server. You're running 32-bit SQL Server, according to your first
> >> >> post?
> >> >> --
> >> >> Adam Machanic
> >> >> SQL Server MVP
> >> >> Author, "Expert SQL Server 2005
> >> >> Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> >> > I really need to read these things better, I didn't see in your
> >> > previous post that you were talking about Enterprise Edition SQL
> >> > Server, I thought you meant 64 Bit Windows 2003 Enterprise Edition.
> >> > Yes, I am running 32 bit SQL Server Standard on 32 Bit Windows 2003
> >> > Enterprise. Given that, I can't get AWE to enable. When I try to
> >> > enable it, I get the following error:
> >> > Alter Failed (Microsoft.SQLServer.SMO)
> >> > Additional information
> >> > + An exception occured while executing a Transact-SQL statement or
> >> > batch.
> >> > (Microsoft.SQLServer.ConnectionInfo)
> >> > + Address Windowing Extensions (AWE) requires the "lock pages in
> >> > memory" privilege which is not currently present in the access token
> >> > of the process.
> >> > When I looked that up, I found that I needed to enable the Enable the
> >> > Lock Pages in Memory Option. Using this article
> >> >http://msdn2.microsoft.com/en-us/library/ms190730(SQL.90).aspxItried
> >> > to do that, but it is still not working. Any insight?
> >> > Thanks very much for your help.
> > Yes I do.
Well, that's part of the issue, I guess. It says "add an account with
privileges to run sqlservr.exe" - by that does it mean to add the
account that the MSSQLSERVER service starts as, or an account that has
sysadmin rights to the SQL Server?|||"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1182446047.108936.106190@.w5g2000hsg.googlegroups.com...
> Well, that's part of the issue, I guess. It says "add an account with
> privileges to run sqlservr.exe" - by that does it mean to add the
> account that the MSSQLSERVER service starts as, or an account that has
> sysadmin rights to the SQL Server?
The account that the SQL Server service runs as.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220|||On Jun 21, 1:17 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182446047.108936.106190@.w5g2000hsg.googlegroups.com...
>
> > Well, that's part of the issue, I guess. It says "add an account with
> > privileges to run sqlservr.exe" - by that does it mean to add the
> > account that the MSSQLSERVER service starts as, or an account that has
> > sysadmin rights to the SQL Server?
> The account that the SQL Server service runs as.
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220
I still get the same error. Does it matter that I'm not logging into
the SQL Server as the account that starts the service?|||"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1182447460.877712.292450@.n60g2000hse.googlegroups.com...
> I still get the same error. Does it matter that I'm not logging into
> the SQL Server as the account that starts the service?
No, that shouldn't be an issue. Did you try rebooting? I'm wondering
if the policy isn't being applied for some reason.
Also, if you look in Task Manager, is SQL Server running using the
account that you think it should be using?
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220|||On Jun 21, 2:12 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182447460.877712.292450@.n60g2000hse.googlegroups.com...
>
> > I still get the same error. Does it matter that I'm not logging into
> > the SQL Server as the account that starts the service?
> No, that shouldn't be an issue. Did you try rebooting? I'm wondering
> if the policy isn't being applied for some reason.
> Also, if you look in Task Manager, is SQL Server running using the
> account that you think it should be using?
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220|||On Jun 21, 2:12 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182447460.877712.292450@.n60g2000hse.googlegroups.com...
>
> > I still get the same error. Does it matter that I'm not logging into
> > the SQL Server as the account that starts the service?
> No, that shouldn't be an issue. Did you try rebooting? I'm wondering
> if the policy isn't being applied for some reason.
> Also, if you look in Task Manager, is SQL Server running using the
> account that you think it should be using?
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220
I haven't tried rebooting, I can't do that until tonight, but I will
try that. I know sometimes Computer Configuration policies do not
take effect until after rebooting. I checked, and the SQL Server is
running as the account I expect it to be.|||Do you see "Address Windowing Extensions enabled" in the message log?
> I haven't tried rebooting, I can't do that until tonight, but I will
> try that. I know sometimes Computer Configuration policies do not
> take effect until after rebooting. I checked, and the SQL Server is
> running as the account I expect it to be.
>