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
Dylan

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" wrote in message
news:1182433233.008715.247450-at-o61g2000hsh.googlegr oups.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"
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" wrote in message
>
> news:1182433233.008715.247450-at-o61g2000hsh.googlegr oups.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" wrote in message
news:1182434972.723295.308160-at-o61g2000hsh.googlegr oups.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"
wrote:
> "dylan" wrote in message
>
> news:1182434972.723295.308160-at-o61g2000hsh.googlegr oups.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" wrote in message
news:1182436089.647770.79590-at-p77g2000hsh.googlegro ups.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"
wrote:
> "dylan" wrote in message
>
> news:1182436089.647770.79590-at-p77g2000hsh.googlegro ups.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/lib...3(SQL.90).aspx
AWE is not needed and cannot be configured on 64-bit operating systems.

"dylan" wrote in message
news:1182437032.054788.207030-at-n60g2000hse.googlegr oups.com...
>
> Hmm, well according to this article
> http://msdn2.microsoft.com/en-us/lib...3(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"
wrote:
> "dylan" wrote in message
>
> news:1182437032.054788.207030-at-n60g2000hse.googlegr oups.com...
>
>
>
> > Hmm, well according to this article
> >http://msdn2.microsoft.com/en-us/lib...3(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/lib...0(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" wrote in message
news:1182440393.064379.241090-at-p77g2000hsh.googlegr oups.com...
> On Jun 21, 11:09 am, "Adam Machanic"
> wrote:
>> "dylan" wrote in message
>>
>> news:1182437032.054788.207030-at-n60g2000hse.googlegr oups.com...
>>
>>
>>
>> > Hmm, well according to this article
>> >http://msdn2.microsoft.com/en-us/lib...3(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/lib...0(SQL.90).aspx I tried
> to do that, but it is still not working. Any insight?
>
> Thanks very much for your help.
>