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) 0 0
Number of INSERT, UPDATE, DELETE statements 0 0
Rows effected by INSERT, UPDATE, DELETE statements 0 0
Number of SELECT statements 0 0
Rows effected by SELECT statements 0 0
Number of user transactions 1 21.9444
Average fetch time 0 0
Cumulative fetch time 0 0
Number of fetches 0 0
Number of open statement handles 0 0
Max number of opened statement handles 0 0
Cumulative number of statement handles 0 0
Network Statistics
Number of server roundtrips 1 1
Number of TDS packets sent 1 1
Number of TDS packets received 34 16.3333
Number of bytes sent 806 747.889
Number of bytes received 21284 14446.8
Time Statistics
Cumulative client processing time 5 3.27778
Cumulative wait time on server replies 9652 2.4226e+007
------------------------------
Application Profile Statistics
Timer resolution (milliseconds) 0 0
Number of INSERT, UPDATE, DELETE statements 0 0
Rows effected by INSERT, UPDATE, DELETE statements 0 0
Number of SELECT statements 0 0
Rows effected by SELECT statements 0 0
Number of user transactions 0 0
Average fetch time 0 0
Cumulative fetch time 0 0
Number of fetches 0 0
Number of open statement handles 0 0
Max number of opened statement handles 0 0
Cumulative number of statement handles 0 0
Network Statistics
Number of server roundtrips 0 0
Number of TDS packets sent 0 0
Number of TDS packets received 0 0
Number of bytes sent 0 0
Number of bytes received 0 0
Time Statistics
Cumulative client processing time 0 0
Cumulative wait time on server replies 0 0
------------------------
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