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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

livcyxingfoe
Starting Member

3 Posts

Posted - 2013-04-11 : 05:57:13
unspammed
Go to Top of Page
   

- Advertisement -