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:[vbcol=seagreen]
> 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/boo
kDisplay.html?bID=10220
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182433233.008715.247450@.o61g2000hsh.googlegroups.com...
>
>
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...
>
>
> 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...
>
>
> 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/li...s190
673(SQL.90).aspx
AWE is not needed and cannot be configured on 64-bit operating systems.|||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:
> Yes I do.
>|||On Jun 21, 12:20 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:[vbcol=seagreen]
> 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/boo
kDisplay.html?bID=10220
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1182442259.823849.93310@.q75g2000hsh.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?|||"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