Friday, March 30, 2012

Max Memory Effect

What is the effect of configuring Max memory on SQL Server 2005?
EXEC sys.sp_configure N'max server memory (MB)', N'100'
GO
RECONFIGURE WITH OVERRIDE
Hello,
MAX SERVER MEMORY option will limit the amount of memory in the buffer pool
used by an instance of SQL Server engine. This will prevent SQL Server from
using
more than the specified amount of memory, thus leaving remaining memory
available to start other applications quickly. The value 100 will assign
just
100 MB for SQL Server.
The below value 0 will make the memory back to dynamic mode.
USE master
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:B3D422B8-623F-4328-9952-0B6E0D39FB99@.microsoft.com...
> What is the effect of configuring Max memory on SQL Server 2005?
> EXEC sys.sp_configure N'max server memory (MB)', N'100'
> GO
> RECONFIGURE WITH OVERRIDE
|||100 MB for SQL Server? Is that going to be enough to run SQL Server? what is
the typical setting let's say on an 8Gig memory.
"Hari Prasad" wrote:

> Hello,
> MAX SERVER MEMORY option will limit the amount of memory in the buffer pool
> used by an instance of SQL Server engine. This will prevent SQL Server from
> using
> more than the specified amount of memory, thus leaving remaining memory
> available to start other applications quickly. The value 100 will assign
> just
> 100 MB for SQL Server.
>
> The below value 0 will make the memory back to dynamic mode.
> USE master
> EXEC sp_configure 'show advanced options', 0
> RECONFIGURE WITH OVERRIDE
> Thanks
> Hari
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:B3D422B8-623F-4328-9952-0B6E0D39FB99@.microsoft.com...
>
>
|||Yes 100MB is very small for SQL Server.
In regards to how much memory it depends on a couple factors like 64/32 bit,
other application that are running.
Regards,
John
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:4ED9D588-73C2-4BE7-8925-584878EAB6CD@.microsoft.com...[vbcol=seagreen]
> 100 MB for SQL Server? Is that going to be enough to run SQL Server? what
> is
> the typical setting let's say on an 8Gig memory.
> "Hari Prasad" wrote:
|||if you have 8 GB on a box thats being used solely as a SQL box then you
would want to set it closer to 6 or 7 GB
John J. Hughes II wrote:[vbcol=seagreen]
> Yes 100MB is very small for SQL Server.
> In regards to how much memory it depends on a couple factors like 64/32 bit,
> other application that are running.
> Regards,
> John
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:4ED9D588-73C2-4BE7-8925-584878EAB6CD@.microsoft.com...

No comments:

Post a Comment