SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Performance Comparison - 2 servers
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shiyam198
Yak Posting Veteran

94 Posts

Posted - 03/28/2013 :  14:15:56  Show Profile  Reply with Quote
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 - 03/28/2013 :  14:43:10  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 03/28/2013 :  14:44:15  Show Profile  Visit russell's Homepage  Reply with Quote
1. you should put an index on [time]
2. Update the statistics
Go to Top of Page

shiyam198
Yak Posting Veteran

94 Posts

Posted - 03/28/2013 :  14:59:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 03/31/2013 :  02:55:46  Show Profile  Visit jackv's Homepage  Reply with Quote
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

China
3 Posts

Posted - 04/11/2013 :  05:57:13  Show Profile  Reply with Quote
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000