Config SQL Server properties to use Fixed Memory

Can I use an SQL statement to configure an SQL server to use Fixed
Memory size as opposed to the default Dynamic ? I don't want to use
the GUI.
Thanks
Sid.Hello,
You will have to use SP_CONFIGURE system storedprocedure.
Below example will tell SQL Server to use 4 GB of RAM.
SP_CONFIGURE 'max server memory', 4096
RECONFIGURE
GO
Thanks
Hari
"sid" <sidwelle@.alexian.net> wrote in message
news:1172763475.361495.109810@.p10g2000cwp.googlegroups.com...
> Can I use an SQL statement to configure an SQL server to use Fixed
> Memory size as opposed to the default Dynamic ? I don't want to use
> the GUI.
> Thanks
> Sid.
>|||I suppose you could use
sp_configure with the following arguments:
max server memory (MB)
min server memory (MB)
and then reconfigure. Any particular reason you want to turn off the dynamic
memory management?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Mar 1, 10:13 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I suppose you could use
> sp_configure with the following arguments:
> max server memory (MB)
> min server memory (MB)
> and then reconfigure. Any particular reason you want to turn off the dynamic
> memory management?
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
We are configuring the memory of a server that runs one of our vendors
applications. If we don't set the memory constraints, the application
will eventually use all the memory and seize the server. Right now we
set the memory as fixed and set its size at half the system memory.
Is there any way to specify the fixed memory with a variable ?
( 50% )
Thanks
Sid.|||No, You will have set the memory in MB based on the physical memory
availability.
Thanks
Hari
"sid" <sidwelle@.alexian.net> wrote in message
news:1172766417.255720.121360@.30g2000cwc.googlegroups.com...
> On Mar 1, 10:13 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>> I suppose you could use
>> sp_configure with the following arguments:
>> max server memory (MB)
>> min server memory (MB)
>> and then reconfigure. Any particular reason you want to turn off the
>> dynamic
>> memory management?
>> Cheers,
>> Paul Ibison SQL Server MVP,www.replicationanswers.com
> We are configuring the memory of a server that runs one of our vendors
> applications. If we don't set the memory constraints, the application
> will eventually use all the memory and seize the server. Right now we
> set the memory as fixed and set its size at half the system memory.
> Is there any way to specify the fixed memory with a variable ?
> ( 50% )
> Thanks
> Sid.
>
>
>|||Something like this should do it:
declare @.x int
select @.x = physical_memory_in_bytes / 500.0 as Half_physical_memory_in_MB
from sys.dm_os_sys_info
exec sp_configure 'max server memory (MB)', @.x
reconfigure
exec sp_configure 'min server memory (MB)', @.x
reconfigure
Note that I'd thoroughly test this sort of code first - I haven't thoroughly
tested sys.dm_os_sys_info on different environments yet.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Mar 1, 11:38 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Something like this should do it:
> declare @.x int
> select @.x = physical_memory_in_bytes / 500.0 as Half_physical_memory_in_MB
> from sys.dm_os_sys_info
> exec sp_configure 'max server memory (MB)', @.x
> reconfigure
> exec sp_configure 'min server memory (MB)', @.x
> reconfigure
> Note that I'd thoroughly test this sort of code first - I haven't thoroughly
> tested sys.dm_os_sys_info on different environments yet.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Thanks for the example.
Question: Why are you not setting the 'fixed' as opposed to the Max
and Min ?|||> Question: Why are you not setting the 'fixed' as opposed to the Max
> and Min ?
There is no such setting. There's a max and a min. The GUI fools us into believing that there's a
fixed, but it only sets max and min to the same value.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sid" <sidwelle@.alexian.net> wrote in message
news:1172772610.650684.3930@.v33g2000cwv.googlegroups.com...
> On Mar 1, 11:38 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>> Something like this should do it:
>> declare @.x int
>> select @.x = physical_memory_in_bytes / 500.0 as Half_physical_memory_in_MB
>> from sys.dm_os_sys_info
>> exec sp_configure 'max server memory (MB)', @.x
>> reconfigure
>> exec sp_configure 'min server memory (MB)', @.x
>> reconfigure
>> Note that I'd thoroughly test this sort of code first - I haven't thoroughly
>> tested sys.dm_os_sys_info on different environments yet.
>> Cheers,
>> Paul Ibison SQL Server MVP,www.replicationanswers.com
> Thanks for the example.
> Question: Why are you not setting the 'fixed' as opposed to the Max
> and Min ?
>|||On Mar 1, 12:54 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > Question: Why are you not setting the 'fixed' as opposed to the Max
> > and Min ?
> There is no such setting. There's a max and a min. The GUI fools us into believing that there's a
> fixed, but it only sets max and min to the same value.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "sid" <sidwe...@.alexian.net> wrote in message
> news:1172772610.650684.3930@.v33g2000cwv.googlegroups.com...
>
> > On Mar 1, 11:38 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> >> Something like this should do it:
> >> declare @.x int
> >> select @.x = physical_memory_in_bytes / 500.0 as Half_physical_memory_in_MB
> >> from sys.dm_os_sys_info
> >> exec sp_configure 'max server memory (MB)', @.x
> >> reconfigure
> >> exec sp_configure 'min server memory (MB)', @.x
> >> reconfigure
> >> Note that I'd thoroughly test this sort of code first - I haven't thoroughly
> >> tested sys.dm_os_sys_info on different environments yet.
> >> Cheers,
> >> Paul Ibison SQL Server MVP,www.replicationanswers.com
> > Thanks for the example.
> > Question: Why are you not setting the 'fixed' as opposed to the Max
> > and Min ... Hide quoted text -
> - Show quoted text -
When I try to execute I get "Incorrect syntax near the keywork 'as' "
Thanks
Sid.|||Sorry - it's from an earlier query. Please remove "as
Half_physical_memory_in_MB" and run it.
Paul Ibison|||On Mar 1, 4:59 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Sorry - it's from an earlier query. Please remove "as
> Half_physical_memory_in_MB" and run it.
> Paul Ibison
I had already done that, but I still get " Invalid object name
'sys.dm_os_sys_info' "
I am using SQL 2k
Thanks
Sid.|||For SQL2K, check out below:
sysconfigures
syscurconfigs
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sid" <sidwelle@.alexian.net> wrote in message
news:1172851212.691239.101580@.s48g2000cws.googlegroups.com...
> On Mar 1, 4:59 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>> Sorry - it's from an earlier query. Please remove "as
>> Half_physical_memory_in_MB" and run it.
>> Paul Ibison
> I had already done that, but I still get " Invalid object name
> 'sys.dm_os_sys_info' "
> I am using SQL 2k
> Thanks
> Sid.
>|||On Mar 2, 10:37 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> For SQL2K, check out below:
> sysconfigures
> syscurconfigs
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "sid" <sidwe...@.alexian.net> wrote in message
> news:1172851212.691239.101580@.s48g2000cws.googlegroups.com...
>
> > On Mar 1, 4:59 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> >> Sorry - it's from an earlier query. Please remove "as
> >> Half_physical_memory_in_MB" and run it.
> >> Paul Ibison
> > I had already done that, but I still get " Invalid object name
> > 'sys.dm_os_sys_info' "
> > I am using SQL 2k
> > Thanks
> > Sid.- Hide quoted text -
> - Show quoted text -
Nice to know those tables are there, but I didn't see a field for
system memory.
Sid.|||You mean row? Did you turn on "show advanced options"?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sid" <sidwelle@.alexian.net> wrote in message
news:1172865017.402571.254540@.8g2000cwh.googlegroups.com...
> On Mar 2, 10:37 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> For SQL2K, check out below:
>> sysconfigures
>> syscurconfigs
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "sid" <sidwe...@.alexian.net> wrote in message
>> news:1172851212.691239.101580@.s48g2000cws.googlegroups.com...
>>
>> > On Mar 1, 4:59 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>> >> Sorry - it's from an earlier query. Please remove "as
>> >> Half_physical_memory_in_MB" and run it.
>> >> Paul Ibison
>> > I had already done that, but I still get " Invalid object name
>> > 'sys.dm_os_sys_info' "
>> > I am using SQL 2k
>> > Thanks
>> > Sid.- Hide quoted text -
>> - Show quoted text -
> Nice to know those tables are there, but I didn't see a field for
> system memory.
> Sid.
>|||As far as I can tell from digging through the system tables on SQL Server
2000 this info wasn't available to TSQL directly. You could read it in a COM
object and reference that in a stored proc using the sp_oa.. procedures.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Mar 6, 4:32 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> As far as I can tell from digging through the system tables on SQL Server
> 2000 this info wasn't available to TSQL directly. You could read it in a COM
> object and reference that in a stored proc using the sp_oa.. procedures.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Thanks Paul, thats about what I had figured.
I atleast got what I needed out of it.
Sid.