Server Comparison

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Server Comparison

Server Comparison

Rate Topic

Display Mode

Topic Options

Author

Message

Andrew Hartnett

Posted Friday, March 13, 2009 8:09 AM

Forum 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

Henry Treftz

Posted Friday, March 13, 2009 9:04 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:51 PM
Points: 175, Visits: 327

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

Andrew Hartnett

Posted Friday, March 13, 2009 10:06 AM

Forum 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

Grant Fritchey

Posted Friday, March 13, 2009 10:30 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,375, Visits: 25,159

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 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 »

Permissions