November 14, 2008 at 12:34 pm
Hi All
We have an HP ML570 G3 windows server 2003 Enterprise edition with SQL 2000 Enterprise Edition SP4. It has 4 Xeon 3.33 Ghz processors running Hyper-Threading so we have total 8 processors and 3.5 GB of RAM. All drivers and firmwares are up-to-date. O/S and SQL logs are on RAID 1 and SQL data files are on RAID 5; plenty of unused space. The only problem we have is the server is very slow. This is a back end box for a web-based application running on another box. The previous version of the application were running fast, everyone was happy with it. When we installed the new version we also migrated it to a newer server. However the app was starting to run slower. I monitored the server performance and all the numbers are perfect but the app is still slow. On the SQL the average CPU usage is nerver go higher than 10% at anytime so I just wonder if SQL is misconfigured. Can you guys help look for anything wrong here? Thanks.
Below is result from sp_configure. Sorry the numbers are not aligned.
affinity mask -2147483648 2147483647 62 0
allow updates 0 1 0 0
awe enabled 0 1 1 1
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 2 2
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2560 2560
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 1 1
remote query timeout (s) 0 2147483647 600 600
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 1404 1404
November 14, 2008 at 12:40 pm
Why did you concentrate on the SQL server parameters only?
What about your network traffic? Do you have a single network card on this box? What is the network speed?
November 14, 2008 at 12:47 pm
Glen (11/14/2008)
Why did you concentrate on the SQL server parameters only?What about your network traffic? Do you have a single network card on this box? What is the network speed?
I got to look on the SQL part first because I am DBA and it seems everyone is going to blame others first so I must make sure I am not doing anything wrong on my part 🙂
We are on a 100 MB Cisco switch network. The NIC's was at 100 MB full. I changed to Auto, nothing improved.
Thanks.
November 14, 2008 at 1:28 pm
What is actually slow?
If the application is responding slowly then have you tried running the queries ran by application manually under query analyzer? What is the Index structure and index fragmentation(are they getting
rebuilt or defragmented regularly)and check the execution plan of queries if they run too slow when ran manually.
Also, post these four counters output from performance monitor:
SQLServer:Buffer Manager\Buffer cache hit ratio
SQLServer:Buffer Manager\Checkpoint pages/sec
SQLServer:Buffer Manager\Lazy writes/sec
SQLServer:Buffer Manager\Page life expectancy
MJ
November 14, 2008 at 1:59 pm
MANU (11/14/2008)
What is actually slow?If the application is responding slowly then have you tried running the queries ran by application manually under query analyzer? What is the Index structure and index fragmentation(are they getting
rebuilt or defragmented regularly)and check the execution plan of queries if they run too slow when ran manually.
Also, post these four counters output from performance monitor:
SQLServer:Buffer Manager\Buffer cache hit ratio
SQLServer:Buffer Manager\Checkpoint pages/sec
SQLServer:Buffer Manager\Lazy writes/sec
SQLServer:Buffer Manager\Page life expectancy
MJ
That is the hard part: the web app and SQL database are from a vendor. I don't know what it does in each stored procedure because they are encrypted. Monitoring a web app is impossible to tell what stored procedures are called when a web user click on a button on the page because in profiler I see hundreds of stored procedures running by only one login for the IIS front end.
Nightly the database is checked, optimized, and reindexed so I am sure fragment is not a problem.
SQLServer:Buffer Manager\Buffer cache hit ratio: 99.8
SQLServer:Buffer Manager\Checkpoint pages/sec: 0 most of the time. It peaks for less than 5 seconds then drop to 0 again
SQLServer:Buffer Manager\Lazy writes/sec: same as Checkpoint pages/sec
SQLServer:Buffer Manager\Page life expectancy: always greater than 300 (expected number) unless a checkpoint occurs then it drops below 300 but only for couple seconds
Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply