Performance and Sizing question

Dear experts,
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size ?
2. Memory - minimum and maximum for sql server ?
3. Procedure cahe - size ?
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!
Hi
Check out http://www.sql-server-performance.co...erformance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/tg...568550-7347917
The Guru's Guide to SQL Server Architecture and Internals
http://www.awprofessional.com/catalo...7-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServe...588/45588.html
Others inline:
"AC" wrote:

> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size ?
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file

> 2. Memory - minimum and maximum for sql server ?
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
http://www.windowsitpro.com/SQLServe...156/45156.html
http://www.windowsitpro.com/SQLServe...155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.

> 3. Procedure cahe - size ?
In general this is tuned while under load (firstly load testing and then
live environment)

> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article

> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.

> Thank you in advance!
John
|||http://www.sql-server-performance.co...nitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size ?
> 2. Memory - minimum and maximum for sql server ?
> 3. Procedure cahe - size ?
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!