June 10, 2003 at 3:05 pm
these are the comparisons for two different servers with the same data and indexes. the same query is taking more than twice on this server.
Application Profile Statistics
Timer resolution (milliseconds)00
Number of INSERT, UPDATE, DELETE statements00
Rows effected by INSERT, UPDATE, DELETE statements00
Number of SELECT statements00
Rows effected by SELECT statements00
Number of user transactions121.9444
Average fetch time00
Cumulative fetch time00
Number of fetches00
Number of open statement handles00
Max number of opened statement handles00
Cumulative number of statement handles00
Network Statistics
Number of server roundtrips11
Number of TDS packets sent11
Number of TDS packets received3416.3333
Number of bytes sent806747.889
Number of bytes received2128414446.8
Time Statistics
Cumulative client processing time53.27778
Cumulative wait time on server replies96522.4226e+007
------------------------------
Application Profile Statistics
Timer resolution (milliseconds)00
Number of INSERT, UPDATE, DELETE statements00
Rows effected by INSERT, UPDATE, DELETE statements00
Number of SELECT statements00
Rows effected by SELECT statements00
Number of user transactions00
Average fetch time00
Cumulative fetch time00
Number of fetches00
Number of open statement handles00
Max number of opened statement handles00
Cumulative number of statement handles00
Network Statistics
Number of server roundtrips00
Number of TDS packets sent00
Number of TDS packets received00
Number of bytes sent00
Number of bytes received00
Time Statistics
Cumulative client processing time00
Cumulative wait time on server replies00
------------------------
can anyone please throw some light?
June 10, 2003 at 3:44 pm
kraks,
Do the two servers in question have identical hardware?
What kind of traffic/load is present on each server?
Have you recently rebuilt indexes or restored the database in question on one of the servers?
thanks,
wavie davie 🙂
June 10, 2003 at 4:58 pm
there are no loads on both the servers. we have isolated these servers. both the servers are not identical. the first statistics is from a 4 proc 4 gig server and the second is from 2 proc 2 gig server. since the powerful machine is slow, i am kinda clueless. all the indexes have been rebuilt, statistics updated for both the servers.
June 10, 2003 at 5:16 pm
Kraks,
Okay, that helps. Are you using the same version of SQL on both boxes? ie., both boxes are running SQL Server 2000 Standard Edition.
Have tried running perfmon on the slow server, checking for disk i/o, processor and memory usage?
wavie davie 🙂
June 11, 2003 at 12:02 am
My reading of the second lot of statistics is that you're sending nothing to the server and therefore getting nothing back. Everything's zero, including network stats. Am I missing something?
Cheers,
- Mark
June 11, 2003 at 3:44 am
the query that is running slow is an sql enterprise 2000 and other is sql standard 2000. I kinda suspect the subsystem to be a bottleneck. what can i check on perfmon to find where exactly the bottlenect is?
June 11, 2003 at 5:17 am
There have been some threads about multi-processor machines behaving strangely. Maybe something to check out ...
June 11, 2003 at 9:47 am
Kraks,
Here are some of the most common sql-related perfmon monitors:
Memory: Pages/Sec
Memory: Available Bytes
Physical Disk: % Disk Time
Physical Disk: Avg. Disk Queue Length
Processor: % Processor Time
System: Processor Queue Length
SQL Server Buffer: Buffer Cache Hit Ratio
SQL Server General: User Connections
If you suspect the disk subsystem is the bottleneck, then you will see % Disk Time (Measures how busy a physical array is) spiking. A good rule of thumb is that this counter should run less than 55% for continuous periods.
Wavie davie 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply