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-21474836482147483647620
allow updates0100
awe enabled0111
c2 audit mode0100
cost threshold for parallelism03276722
Cross DB Ownership Chaining0100
cursor threshold-12147483647-1-1
default full-text language0214748364710331033
default language0999900
fill factor (%)010000
index create memory (KB)704214748364700
lightweight pooling0100
locks5000214748364700
max degree of parallelism03200
max server memory (MB)4214748364725602560
max text repl size (B)021474836476553665536
max worker threads3232767255255
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364700
nested triggers0111
network packet size (B)5123276740964096
open objects0214748364700
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote login timeout (s)021474836472020
remote proc trans0111
remote query timeout (s)02147483647600600
scan for startup procs0100
set working set size0100
show advanced options0111
two digit year cutoff1753999920492049
user connections03276700
user options03276714041404
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