Poor Performances causing TIMEOUT

[sobeit]

Hi,

We have been experiencing performance problems in our SQL 6.5 databases (about 9 databases) in one NT Server.
We are reviewing stored procedures and indexes, but at the end I am guessing that we will continue to have problems because some of our users are using MS Access to browse and edit data and our network is probably at its capacity (I don't think all machines have 100 Mb/s). We are thinking about getting another SQL Server machine and moving some databases to it.

Will having 2 SQL Server machine help? We will try to optimize the SP and MS Access querries that are hitting the database. Are there other items (disk location) and configuration that we need to examine?

Thanks in advance.

 

[tomschafer]

You could check the ODBC Timeout setting in MS Access- 300 seconds is the max.

 

[Frano]

2 SQL boxs might/should help, but it might be more cost effective to identify the bottlenecks first. (which you mention at the end of your question)

What is your disk configuration? ie RAID 5, logs on different disks etc.

Steve Francis

 

[sobeit]

Thanks for replying.

I will make sure that the MS Access connection timeout
is set at max if the users want to wait that long.

I think that we are using RAID5 techology and the log file and the data file are in different disks. The following is the configure setttings:
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 51 51
fill factor 0 100 0 0
free buffers 20 524288 12800 12800
hash buckets 4999 265003 24000 24001
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 50000 50000
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 256000 256000
nested triggers 0 1 1 1
network packet size 512 32767 4096 4096
open databases 5 32767 20 20
open objects 100 2147483647 2000 2000
priority boost 0 1 0 0
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 300 300
user options 0 4095 0 0