Performance Comparison - 2 servers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Performance Comparison - 2 servers

Author

Topic

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2013-03-28 : 14:15:56

Hi,

I have 2 servers - Lets call them A and B.
They have the same same database. A database with one table that has 5 12 columns and one of them is XML type.

the database is 125~ GB

I run the following Script on both. It results 644 rows.


set statistics io on
set statistics time on

declare @startDate datetime
declare @endDate datetime
set @startDate = '2013-03-20 23:00:00.000'
set @endDate = '2013-03-20 23:59:59.99'

SELECT *
FROM [Event]
WHERE Time - '4:00:00' > @startDate
AND Time - '4:00:00' < @endDate

Server A takes only 1 second and Server B took 30 minutes. As you can see above I had the IO and TIME on.

Results for both are here:

SERVER A:


SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(644 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Event'. Scan count 25, logical reads 105607, physical reads 0, read-ahead reads 38, lob logical reads 15, lob physical reads 0, lob read-ahead reads 16.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
 CPU time = 3964 ms,  elapsed time = 592 ms.

SERVER B:



 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(644 row(s) affected)
Table 'Event'. Scan count 1, logical reads 13501883, physical reads 51263, read-ahead reads 13458858, lob logical reads 15, lob physical reads 4, lob read-ahead reads 16.

 SQL Server Execution Times:
   CPU time = 1885615 ms,  elapsed time = 2097132 ms.

This table doesn't have any indexes. And it's quite obvious the problem is on the servers.

Can you please let me know what the results set mean? what should I look at next?

- Shiyam

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2013-03-28 : 14:43:10

Additional info:

I allowed "Client Statistics and ran the scripts again on both the servers:

SERVER B:


Client Execution Time	14:30:42		
Query Profile Statistics			
  Number of INSERT, DELETE and UPDATE statements	0		0.0000
  Rows affected by INSERT, DELETE, or UPDATE statements	0		0.0000
  Number of SELECT statements 				4		4.0000
  Rows returned by SELECT statements			646		646.0000
  Number of transactions 				0		0.0000
Network Statistics			
  Number of server roundtrips				1		1.0000
  TDS packets sent from client				1		1.0000
  TDS packets received from server			446		446.0000
  Bytes sent from client				632		632.0000
  Bytes received from server				1823706		1823706.0000
Time Statistics			
  Client processing time				13		13.0000
  Total execution time					2212678		2212678.0000
  Wait time on server replies				2212665		2212665.0000

SERVER A


Client Execution Time	12:43:39		
Query Profile Statistics			
  Number of INSERT, DELETE and UPDATE statements	0		0.0000
  Rows affected by INSERT, DELETE, or UPDATE statements	0		0.0000
  Number of SELECT statements 				4		4.0000
  Rows returned by SELECT statements			646		646.0000
  Number of transactions 				0		0.0000
Network Statistics			
  Number of server roundtrips				1		1.0000
  TDS packets sent from client				1		1.0000
  TDS packets received from server			446		446.0000
  Bytes sent from client				628		628.0000
  Bytes received from server				1823765		1823765.0000
Time Statistics			
  Client processing time				31		31.0000
  Total execution time					327		327.0000
  Wait time on server replies				296		296.0000

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-03-28 : 14:44:15

1. you should put an index on [time]
2. Update the statistics

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2013-03-28 : 14:59:30

Hi Russ,

Thanks for your reply. I will try that. But both the servers don't have an index. How can it work on one faster and then the other slower?

- Shiyam

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-03-31 : 02:55:46

Are the two servers of the same specification: do they have same amount of memory allocations, CPU count, CPU speed , disk response rates?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

livcyxingfoe
Starting Member

3 Posts

Posted - 2013-04-11 : 05:57:13

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources