0

I am running a fairly basic query on my local development machine which runs almost instantly. However on the server its taking around 3-4 seconds to return results. I have ran Client Statistics on both machines and get these results.

Local machine:

Query Profile Statistics            
  Number of INSERT, DELETE and UPDATE statements    0       
  Rows affected by INSERT, DELETE, or UPDATE statements 0       
  Number of SELECT statements   3       
  Rows returned by SELECT statements    17      
  Number of transactions    0       
Network Statistics          
  Number of server roundtrips   2       
  TDS packets sent from client  2       
  TDS packets received from server  9       
  Bytes sent from client    342     
  Bytes received from server    30141       
Time Statistics         
  Client processing time    16      
  Total execution time  508     
  Wait time on server replies   492     

Server machine:

Query Profile Statistics            
  Number of INSERT, DELETE and UPDATE statements    0       
  Rows affected by INSERT, DELETE, or UPDATE statements 0       
  Number of SELECT statements   3   
  Rows returned by SELECT statements    17      
  Number of transactions    0       
Network Statistics          
  Number of server roundtrips   2       
  TDS packets sent from client  2       
  TDS packets received from server  9       
  Bytes sent from client    342     
  Bytes received from server    31560       
Time Statistics         
  Client processing time    16      
  Total execution time  3638        
  Wait time on server replies   3622        

What could be the reason for it taking so much longer on the server? Its causing webpages to load slowly and hence users and search engines are penalising me.

14
  • Can you post the actual slow and fast query plan xml (use pastebin and link it here) ? Also, what is the max memory on the server and the local machine ? Check sys.configuration to see if there is any anomaly between local and remote server. – Kin Shah Jul 15 '16 at 12:31
  • Are index statistics updated on production database? Is something else running on hardware it's using? – Marcin S. Jul 15 '16 at 13:07
  • @MarcinS. I ran sp_updatestats on the server and updated the statistics on all tables - but no difference. It still takes 3 seconds to run on the server. My local computers are Intel Core i7 machines. The server is an AMD Quad-Core CPU. Would the CPU difference really cause such a delay in performance on a simple select query? – volume one Jul 15 '16 at 13:10
  • @volumeone What's the load on server? Are there any other processes running (try using sp_WhoIsActive from Adam Machanic or at least check what is Activity Monitor telling You)? That shouldn't be the case (it's not nearly as important as statistics, performance wise), but maybe Your db is fragmented (like >70% fragmentation on indexes)? If so, check with Ola Hallegren's Index and Statistics Maintenance – Marcin S. Jul 15 '16 at 13:22
  • @volumeone Try using Ola's script instead of sp_updatestats, it has it's issues. – Marcin S. Jul 15 '16 at 13:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.