CODEWITHSUNDEEP
sqlsql-server
Egor4eg
Egor4eg

Reputation: 2708

Sql Server 2008 performance

I have 2 Sql Servers 2008 on different machines. I execute the same sql query on each machine (select from table with 500000 rows). On the first machine execution time is 1min 9sec. On the second machine this time is 2:45. The hardware on the second machine is better than on the first one. Why execution time is bigger on the second machine? Maybe there are any options on which Sql Server performance depends?

Could issue be in HDD? How can I check is that HDD issue?

See query statistics below:

1st:

Client Execution Time   16:25:21        
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   2       2.0000
  Rows returned by SELECT statements    407354      407354.0000
  Number of transactions    0       0.0000
Network Statistics          
  Number of server roundtrips   3       3.0000
  TDS packets sent from client  3       3.0000
  TDS packets received from server  18077       18077.0000
  Bytes sent from client    738     738.0000
  Bytes received from server    7.403454E+07        74034540.0000
Time Statistics         
  Client processing time    3859        3859.0000
  Total execution time  3859        3859.0000
  Wait time on server replies   0       0.0000

2nd:

Client Execution Time   08:21:24        
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   2       2.0000
  Rows returned by SELECT statements    399952      399952.0000
  Number of transactions    0       0.0000
Network Statistics          
  Number of server roundtrips   3       3.0000
  TDS packets sent from client  3       3.0000
  TDS packets received from server  17833       17833.0000
  Bytes sent from client    738     738.0000
  Bytes received from server    7.29937E+07     72993700.0000
Time Statistics         
  Client processing time    5688        5688.0000
  Total execution time  5734        5734.0000
  Wait time on server replies   46      46.0000

Upvotes: 1

Views: 869

Answers (5)

Gulli Meel
Gulli Meel

Reputation: 891

Best way is to compare the plans.If both are identical and actuall number of rows are same then it could be realted to the server issues.

Even it could be related to network issues if you are returning huge number of rows. When you say hardware is better than first machine that means better IO system,more CPU's and more memory.

Also, there is a possibility that workload is not same on both the machines.

Upvotes: 1

Pavel Nefyodov
Pavel Nefyodov

Reputation: 896

Check Performance monitor on both machines. Check if you got applications running on the background. Check affinity masks for processors.

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40359

There might be physical disk fragmentation issues. On one server, the database files are stored in consecutive blocks/sectors, while on the other the file is scattered all ove the platter. (Likely to happen if you have a db grow by 10% chunks to ginormous sizes over weeks or months, and then back it up and restore it on a mostly empty disk.)

Upvotes: 0

Johann Blais
Johann Blais

Reputation: 9469

Retrieve the execution plan on both servers and look for differences.

I would also guess it is related to statistics, since it can have a big impact on the final execution plan.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96640

Statistics could be out of date. Indexing could be different. Number of records meeting the criteria could be larger.

Upvotes: 1

Related Questions