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!
Showing posts with label bound. Show all posts
Showing posts with label bound. Show all posts
Tuesday, March 20, 2012
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.c...performance.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/t...6568550-7347917
The Guru's Guide to SQL Server Architecture and Internals
http://www.awprofessional.com/catal...37-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServ...5588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3
G.
> 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/d...br />
8dbn.asp
http://www.windowsitpro.com/SQLServ...5156/45156.html
http://www.windowsitpro.com/SQLServ...5155/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.
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39158&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=Article[
/url]
> 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.c...onitor_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!
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.c...performance.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/t...6568550-7347917
The Guru's Guide to SQL Server Architecture and Internals
http://www.awprofessional.com/catal...37-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServ...5588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3
G.
> 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/d...br />
8dbn.asp
http://www.windowsitpro.com/SQLServ...5156/45156.html
http://www.windowsitpro.com/SQLServ...5155/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.
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39158&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=Article[
/url]
> 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.c...onitor_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!
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.com/articles_performance.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/detail/-/1555582419/104-6568550-7347917
The Guru's Guide to SQL Server Architecture and Internal
http://www.awprofessional.com/catalog/product.asp?product_id={FA365B66-8A6F-400E-A637-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45588/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/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45156/45156.html
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45155/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/Index.cfm?ArticleID=39158&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=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.com/performance_monitor_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!
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.com/articles_performance.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/detail/-/1555582419/104-6568550-7347917
The Guru's Guide to SQL Server Architecture and Internal
http://www.awprofessional.com/catalog/product.asp?product_id={FA365B66-8A6F-400E-A637-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45588/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/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45156/45156.html
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45155/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/Index.cfm?ArticleID=39158&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=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.com/performance_monitor_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!
Subscribe to:
Posts (Atom)