April 19, 2002 at 5:50 am
Both the DDL of the tables and the Execution are dead on and the trace numbers look fine except duration. This now has to be either a server settings issue or a bottleneck due to hardware. Try another query on a different table if possible and see if they run worse of the production box as opposed to dev. Also check the settings on your Production server and make sure it is set to dynamically allocate memory and use all available processors. Also what kind of load is on the server during these times. What else is running on the Production server?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 19, 2002 at 6:04 am
There's a fair amount of load on the production server, but the cpu averages at 6%
Both boxes are just default sql installs, nothing special has been done, they both use all avaliable cpu's, but the production one is the only one with dual cpu's
Doing a different query on a different table and the production server still runs slower
Is it possible that the RAID 5 array on the production server is slowing it down?
April 19, 2002 at 6:07 am
The only other thing on the box is cold fusion and IIS 5, which is hosting a smallish site (the larger site is on it's own box and uses this as it's sql server)
but neither service creates much load
April 22, 2002 at 2:42 am
Raid 5 will be slower than Raid 1,
the only way to gather the time-differences/i-o
differences would be to run something like:
Performance Montior with disk performance stats switched on,
looking at the different speeds to do the same op whilst nothing
else is running.
April 22, 2002 at 5:22 am
6% cpu is decent, doubt it make the difference. Have you tried running it multiple times? First time will create the plan and also cache the data.
Andy
April 22, 2002 at 5:34 am
Hey Dan I don't believe that RAID1 is any different than RAID5 on reads, it is writes where 5 is slower due to the parity it writes.
Also noodles is the data is not a security issue and the db is small could you email it to me to look at here and test reactions on my servers (has to be under 4mb to get here same day, email server marks larges files low priotiry otherwise)?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 22, 2002 at 3:22 pm
i'll put that table into it's own database and zip it up for everyone to take a look at, the data in that table doesn't have to be secure
Will do it later today
April 25, 2002 at 1:45 am
Noodles, Can you run sp_configure and post the results. Make sure that "Show Advanced Options" is set to 1.
The reason I am asking, you might have "Max Degree of Parallelism" enabled. This parameter should be analyzed before being used. In a multiple cpu environment it can cause a major performance hit. In a single cpu environment it has no benefit. It is used mainly for long running queries like in an OLAP environment, in an OLTP environment it can severly degrade performance.
Your Production environment is an OLTP environment I am assuming and this could be the reason why you have poor response times from the query. It is enabled by default in SQL, setting '0'. Set the option to 1 and you might see a change in performance. Setting the option to '1' tells SQL to not perform this function, a setting of '0'(default upon install) means use all available processors for parallel plan execution which has an overhead penalty for OLTP databases.
In my shop we enable "Max Degree of Parallelism" at night for improved performance on long running batch jobs, DBCC CHECKDB,CHECKTABLE,CHECKFILEGROUP to take advantage of performance across all processors.
Sorry for writing a book, just trying to help.
Regards,
Edited by - jock williams on 04/25/2002 01:46:35 AM
Edited by - jock williams on 04/25/2002 01:47:24 AM
April 25, 2002 at 4:03 pm
affinity mask-2147483648214748364700
allow updates0100
awe enabled0100
c2 audit mode0100
cost threshold for parallelism03276755
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)4214748364721474836472147483647
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)5126553640964096
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 trans0100
remote query timeout (s)02147483647600600
scan for startup procs0100
set working set size0100
show advanced options0111
two digit year cutoff1753999920492049
user connections03276700
user options03276700
Is this what you needed?
I couldn't find an option for Show Advanced Options
April 28, 2002 at 9:31 pm
Hi Nick; if you have not been successful you might like to check your collation orders on the server - it could be one factor affecting the performance; the default orders have changed between sql 7 and 2000. Note the 'server' collation order may be different to the 'database' collation order. While MS 'fully support' multiple collation orders we have experienced these problems in the past.
Regards,
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply