Optimizing SQL 2000 SP4 for large amount of memory

Hi
We will within the next week move our SQL 2000 server SP4 to a new high
performance server (2 x ghz XEON, 4 GB RAM)
The database which is to be running on the server has a physical size of 50
mb on disk.
Is there a way to optimize the SQL 2000 to keep as much as possible
in-memory so searching will as quick as possible? The read / write rate is
approx 1000/1 (we read 1000 x as often as writing)
Is the some memory settings we can tweak or are we better of letting sql
server 2000 handle it?
Thanks in regards
Anders JacobsenJust checking 50Mb not 50 Gb. If so then there's not much you can do, with
4Gb of RAM it will all reside in memory.
--
Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"Anders" <anderskj1@.yahoo.dk> wrote in message
news:%23j7EngYXGHA.4432@.TK2MSFTNGP04.phx.gbl...
> Hi
> We will within the next week move our SQL 2000 server SP4 to a new high
> performance server (2 x ghz XEON, 4 GB RAM)
> The database which is to be running on the server has a physical size of
> 50 mb on disk.
> Is there a way to optimize the SQL 2000 to keep as much as possible
> in-memory so searching will as quick as possible? The read / write rate is
> approx 1000/1 (we read 1000 x as often as writing)
> Is the some memory settings we can tweak or are we better of letting sql
> server 2000 handle it?
> Thanks in regards
> Anders Jacobsen
>|||"Anders" <anderskj1@.yahoo.dk> wrote in message
news:%23j7EngYXGHA.4432@.TK2MSFTNGP04.phx.gbl...
> Hi
> We will within the next week move our SQL 2000 server SP4 to a new high
> performance server (2 x ghz XEON, 4 GB RAM)
> The database which is to be running on the server has a physical size of
50
> mb on disk.
> Is there a way to optimize the SQL 2000 to keep as much as possible
> in-memory so searching will as quick as possible? The read / write rate is
> approx 1000/1 (we read 1000 x as often as writing)
> Is the some memory settings we can tweak or are we better of letting sql
> server 2000 handle it?
Generally you're better off letting SQL Server handle it. It will grab as
much RAM as it can (2GB with Standard, more with Enterprise with the proper
switches) and use it to cache.
Check perfmon and look at the cache hit ratio for one thing to see how
you're doing.
If it's truly 50MB, that'll fit into RAM absolutely w/o problems.
> Thanks in regards
> Anders Jacobsen
>|||> Generally you're better off letting SQL Server handle it. It will grab as
> much RAM as it can (2GB with Standard, more with Enterprise with the
> proper
> switches) and use it to cache.
> Check perfmon and look at the cache hit ratio for one thing to see how
> you're doing.
> If it's truly 50MB, that'll fit into RAM absolutely w/o problems.
Sounds great.
Thanks

Optimizing SQL 2000 SP4 for large amount of memory

Hi
We will within the next week move our SQL 2000 server SP4 to a new high
performance server (2 x ghz XEON, 4 GB RAM)
The database which is to be running on the server has a physical size of 50
mb on disk.
Is there a way to optimize the SQL 2000 to keep as much as possible
in-memory so searching will as quick as possible? The read / write rate is
approx 1000/1 (we read 1000 x as often as writing)
Is the some memory settings we can tweak or are we better of letting sql
server 2000 handle it?
Thanks in regards
Anders JacobsenJust checking 50Mb not 50 Gb. If so then there's not much you can do, with
4Gb of RAM it will all reside in memory.
Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"Anders" <anderskj1@.yahoo.dk> wrote in message
news:%23j7EngYXGHA.4432@.TK2MSFTNGP04.phx.gbl...
> Hi
> We will within the next week move our SQL 2000 server SP4 to a new high
> performance server (2 x ghz XEON, 4 GB RAM)
> The database which is to be running on the server has a physical size of
> 50 mb on disk.
> Is there a way to optimize the SQL 2000 to keep as much as possible
> in-memory so searching will as quick as possible? The read / write rate is
> approx 1000/1 (we read 1000 x as often as writing)
> Is the some memory settings we can tweak or are we better of letting sql
> server 2000 handle it?
> Thanks in regards
> Anders Jacobsen
>|||"Anders" <anderskj1@.yahoo.dk> wrote in message
news:%23j7EngYXGHA.4432@.TK2MSFTNGP04.phx.gbl...
> Hi
> We will within the next week move our SQL 2000 server SP4 to a new high
> performance server (2 x ghz XEON, 4 GB RAM)
> The database which is to be running on the server has a physical size of
50
> mb on disk.
> Is there a way to optimize the SQL 2000 to keep as much as possible
> in-memory so searching will as quick as possible? The read / write rate is
> approx 1000/1 (we read 1000 x as often as writing)
> Is the some memory settings we can tweak or are we better of letting sql
> server 2000 handle it?
Generally you're better off letting SQL Server handle it. It will grab as
much RAM as it can (2GB with Standard, more with Enterprise with the proper
switches) and use it to cache.
Check perfmon and look at the cache hit ratio for one thing to see how
you're doing.
If it's truly 50MB, that'll fit into RAM absolutely w/o problems.

> Thanks in regards
> Anders Jacobsen
>|||> Generally you're better off letting SQL Server handle it. It will grab as
> much RAM as it can (2GB with Standard, more with Enterprise with the
> proper
> switches) and use it to cache.
> Check perfmon and look at the cache hit ratio for one thing to see how
> you're doing.
> If it's truly 50MB, that'll fit into RAM absolutely w/o problems.
Sounds great.
Thanks

optimization question

I have a table with a large amount of rows > 100 000 with
ID|NAME|HITScolumns
ID is the primary key
NAME is nvarchar
HITS is int
I run this query, it takes less than a second
select top 40 * from table 1 order by id desc
but when i run this it take more than 10 seconds
select top 40 * from table 1 order by hits desc
hits column keeps track of time the page has been loaded
what can i do to make the second query run just as fast as the first one?Do you have an index on HIts? If not then you might want to add one.
Andrew J. Kelly SQL MVP
"Howard" <howdy0909@.yahoo.com> wrote in message
news:%23hSBkKSUGHA.5552@.TK2MSFTNGP14.phx.gbl...
>I have a table with a large amount of rows > 100 000 with
> ID|NAME|HITScolumns
> ID is the primary key
> NAME is nvarchar
> HITS is int
> I run this query, it takes less than a second
> select top 40 * from table 1 order by id desc
> but when i run this it take more than 10 seconds
> select top 40 * from table 1 order by hits desc
>
> hits column keeps track of time the page has been loaded
> what can i do to make the second query run just as fast as the first one?
>
>|||what type of index would you recommend? I ran the Database Engine Tuning
Advisor it didn't give me anything
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u0g2bRSUGHA.5552@.TK2MSFTNGP14.phx.gbl...
> Do you have an index on HIts? If not then you might want to add one.
> --
> Andrew J. Kelly SQL MVP
>
> "Howard" <howdy0909@.yahoo.com> wrote in message
> news:%23hSBkKSUGHA.5552@.TK2MSFTNGP14.phx.gbl...
>|||You really only have two choices here. A clustered or a Nonclustered index.
I assume you already have a clustered index on ID. So try adding a
non-clustered index on Hits and see if it helps.
Andrew J. Kelly SQL MVP
"Howard" <howdy0909@.yahoo.com> wrote in message
news:e$EfRUSUGHA.5500@.TK2MSFTNGP12.phx.gbl...
> what type of index would you recommend? I ran the Database Engine Tuning
> Advisor it didn't give me anything
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u0g2bRSUGHA.5552@.TK2MSFTNGP14.phx.gbl...
>

Operating System Memory. How much?

I currently am looking for a document from microsoft (or close to it)
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"
this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"

Operating System Memory. How much?

I currently am looking for a document from microsoft (or close to it)
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"

Operating System Memory. How much?

I currently am looking for a document from microsoft (or close to it)
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"