Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Server Comparison Expand / Collapse
Author
Message
Posted Friday, March 13, 2009 8:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 10, 2011 1:38 PM
Points: 2, Visits: 108
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.224 00:29:26.779

Post #675195
Posted Friday, March 13, 2009 9:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:30 AM
Points: 177, Visits: 358
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?

Post #675285
Posted Friday, March 13, 2009 10:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 10, 2011 1:38 PM
Points: 2, Visits: 108
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
Post #675368
Posted Friday, March 13, 2009 10:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 13,872, Visits: 28,270
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #675394
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse