March 13, 2009 at 8:09 am
This is my first post on here so bare with me. I have a sql query that I have run on two different servers. I am trying to get some performance metrics so I can see which server has a better architecture. The only thing that I do not understand is that the TEST server has a smaller elapsed time but has a longer CPU seconds/Iteration Avg. It would seem to me that if the cpu time is longer that the elapsed time would follow the same trend. Any ideas as to why this is would be helpful. Thanks
Server TEST BISQL
Client Seconds/Iteration(Avg):14.4086 17.6521
CPU Seconds/Iteration (Avg):62.8717 37.0128
Actual Seconds/Iteration: 14.4101 17.6065
Logical Reads/Iterations (Avg)4507028 4507138
Elapsed Time 00:24:01.22400:29:26.779
March 13, 2009 at 9:04 am
CPU time is not the only metric, don't forget I/O as well. Have you tried looking at the actual query plan (not the estimated one) for the query on each server? If so is there a difference?
March 13, 2009 at 10:06 am
The query plans are indeed different. The actual plan on TEST has a nested loop, an index scan and a key lookup. The actual plan on BISQL parallelism and a table scan. So the reason for the longer elapsed time with the shorter cpu time might be I/O? They both produce the same amount of records
March 13, 2009 at 10:30 am
If it's two different plans, that's probably the issue.
Make sure the same parameters are in use on both and make sure that both have updated statistics and defragmented indexes.
The very best way to compare apples to apples is to restore a backup from the first server on the second. That'll let you be sure that you have a precise copy of the data, the structures, statistics, indexes, etc. Then, any differences are likely to be caused by hardware; memory, cpu or disk.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply