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 Queries generated at runtime

Hi

We are using SQL SERVER 2005, enterprise/standard edition for an application that generates queries at runtime and normally having many joins in it. But these queries are taking lot of time when they are executed. After setting the database parameter PARAMETERIZATION to FORCED, performance of the queries have improved a lot but still we want to improve them further. Is there any other parameter that we can set for improving the performance of the queries. We have created the indexes on the basic columns that will be used while querying but it may happen that some other columns may also be used for searching in which case queries become quiet slow. Is there anything like skip-scan indexes(of oracle) in sql server 2005.

Regards
Salil

Check if there are columns that participate in WHERE clause and have not indexes.

You say "an application that generates queries at runtime", try to use parametrized stored procedures that build dynamically queries using sp_executesql;

in queries don't use "select * from ..." use "select col1,col2,... from ..."

try to use WHERE clause in queries to force using indexes.

you say your queries have many joins on it, try to use indexed views, see article|||1. We have created the indexes on the main columns that we know will be included in the where clause but there can be some columns, we dont know, that can be included in the where clause.
2. Going for parameterized stored procedures is a nice option but we cant go for them at this stage of our product.
3. We are using column names in the select clause rather than "select * "
4. We have many joins but we dont know join will be made to which table.

Thats why we are looking for something more generic like the PARAMETERIZATION parameter. Is there anything else that we can set at the database level for improving the performance of our queries.

|||

Salil wrote:

1. We have created the indexes on the main columns that we know will be included in the where clause but there can be some columns, we dont know, that can be included in the where clause.
2. Going for parameterized stored procedures is a nice option but we cant go for them at this stage of our product.
3. We are using column names in the select clause rather than "select * "
4. We have many joins but we dont know join will be made to which table.

I think you work with a black box and you can optimize outside it; i think you haven't many choices

Check if database options related to STATISTICS are on "ON" option.

So, I think you have to open that black box....

|||we have already set AUTO_UPDATE_STATISTICS_ASYNC ON at the database level.