April 19, 2002 at 9:50 am
The server is an IBM Netfinity 4500R, dual 733 PIII, with 2 GIG RAM. Data is stored on an IBM 7133-D40 SSA with 9.1 GIG drives in RAID 5 on a partition with 15 GIG
free. The OS is NT server 4.0 sp3 with SQL 6.5 sp5.
Below os the sql's config file Server is running fine but would like to see if we could speed it up
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask 0 2147483647 0 0
allow updates 0 1 0 0
backup buffer size 1 32 1 1
backup threads 0 32 5 5
cursor threshold -1 2147483647 -1 -1
database size 2 10000 2 2
default language 0 9999 0 0
default sortorder id 0 255 53 53
fill factor 0 100 0 0
free buffers 20 524288 37500 37500
hash buckets 4999 265003 25000 24989
language in cache 3 100 3 3
LE threshold maximum 2 500000 200 200
LE threshold minimum 2 500000 20 20
LE threshold percent 1 100 0 0
locks 5000 2147483647 75000 75000
LogLRU buffers 0 2147483647 0 0
logwrite sleep (ms) -1 500 0 0
max async IO 1 1024 8 8
max lazywrite IO 1 1024 8 8
max text repl size 0 2147483647 65536 65536
max worker threads 10 1024 255 255
media retention 0 365 0 0
memory 2800 1048576 750000 750000
nested triggers 0 1 1 1
network packet size 512 32767 4096 4096
open databases 5 32767 20 20
open objects 100 2147483647 75000 75000
priority boost 0 1 1 1
procedure cache 1 99 30 30
Protection cache size 1 8192 15 15
RA cache hit limit 1 255 4 4
RA cache miss limit 1 255 3 3
RA delay 0 500 15 15
RA pre-fetches 1 1000 3 3
RA slots per thread 1 255 5 5
RA worker threads 0 255 3 3
recovery flags 0 1 0 0
recovery interval 1 32767 5 5
remote access 0 1 1 1
remote conn timeout -1 32767 10 10
remote login timeout 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout 0 2147483647 0 0
remote sites 0 256 10 10
resource timeout 5 2147483647 10 10
set working set size 0 1 0 0
show advanced options 0 1 1 1
SMP concurrency -1 64 0 0
sort pages 64 511 64 64
spin counter 1 2147483647 10000 10000
tempdb in ram (MB) 0 2044 0 0
time slice 50 1000 100 100
user connections 5 32767 100 100
user options 0 4095 0 0
April 19, 2002 at 10:39 am
A few thoughts.
1. Upgrade to SQL 2000 and W2K. I know it costs money, but it also will give nice performance improvements in how the server runs. You can run SQL in v6.5 comparability mode so your apps won't break.
2. Instead of a single RAID 5 array, you should have at least 3 partitions. I know others will chime in, but at a min, setup a RAID 1 for the OS/system files. You can use this for backups as well. RAID 1 for the log files, RAID 1 or 5 for the data, depending on what you can afford. Or 0+1 for the data if you can. This will help.
Run the SQLTrace and find poorly performing queries. Tune these with indexes or rewrites.
The default for the procedure cache is 30% for v6.5. Not likely you need this much with 2GB of RAM. Lower this to something more reasonable. There is a white paper at MS on tuning that will tell you how to check the size of the cache.
Steve Jones
April 19, 2002 at 3:35 pm
1) Agreed, especially with maintainence issues of SQL 7 and 2K are far superior and they are full supported currently by MS. W2K has it's advantages over NT4 but no where near as drastic as SQL 7 or 2K over 6.5.
2) I agree with the statement for the most part but I would say at least 2 drive arrays on seperate array controllers so you can seperate log files from data files and they do not fight for controller access time. Also in regards to RAID5 over RAID10 (0+1) this boils down to number of writes compared to reads and some decision. My opinion is log files love RAID10 due to major writes, mostly read databases with few writes far and in between actually are better off (mostly due to cost and upkeep) on RAID5 but high write DBs should be if at all possible on RAID10 (the reason is in the parity, when data writes, the controller then reads to build the parity, writes the parity changes and adjusts the parity across the array so lots of reading and writing. RAID 10 is a striped set that is mirrored to another stripped set, the strips have no parity so write/write can occur 2 writes and no data adjusting, plus you get the speed of a strip set. Down side and reason stick with RAID5 on low write systems is cost, you only lose 1 drive as opposed to an exact same number on RAID10).
Big binnefits are indexes, use the fewest you possibly can, that meet your needs. This is especially ture in high write environments where every update to the table means an update to every index. Utilize Stored Procedures and try to avoid straight querying (caching plans and speed overall is generally better as long as you follow the rules, plus you control access better).
Do not run other services or apps on this box. Set page file to more than one drive but not the drives involved with SQL files. Do not install SQL to the drives where the data or logs will live but do have master and all DBs on the arrays. (sorry notin front of me) but you should set performance for background services on NT as oppossed to applications.
Baseline your server with PerfMon and other tools before and after going live. This will give you an idea of bottlenecks produced by the load. Run baselines any time you make changes to anything and periodically based on the amount of new data added, usually Monthly will be helpfull.
Learn all the methods for maintaining the DBs themselves and make sure you do as much as possible to keep things smooth.
Off-peak hours will be your best friend.
There are lots of other things you should consider but most can be found all around the net. And anything you learn test it before you put in prodution use.
Good resource site for Performance tuning to check out http://www.sql-server-performance.com
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 30, 2002 at 8:06 am
Is there anything we can do wiht what he got?
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
April 30, 2002 at 9:40 am
Thanks for all your suggestions. I have rocommeded to the money people upgrading to win 2k and sql 2k now it is a waiting game till they get it approved. Thanks again
May 12, 2002 at 9:21 pm
Hi there,
Just few more suggestions on your config.
1. If you have the dual processor, then setting the priority boost to true is so meaningful and helps to improve the performance considerably. If it is under replication or some other additional tasks running then this setting will affect your entire performance. It is better to turn it off under the circumstances where you are not really sure about the situation.
2. If the server is purely dedicated to sQL alone and there is no other front end application is running on the server, this could be the time where you can increase the memory size considerably. Increase your memory size and see how it is helped you on performance basis.
3. Object locks is one of the other area where you can put little more effort to tune the performance. But You must be very careful as it may affect the performance severely. It depends on the no. of active users connected with your server.
Try these options on week ends and see how it goes. Be ready to switch back to your previous config at any time as these suggestions may need your server activities some times.
If you are concerned about the performance of the entire system, then put some efforts on Indexes and DBCC checks on your Database system.
Hope it will help you a little bit.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply