1

We have a setup with two web servers and one DB server in both the UK and Australia. These servers are with Rackspace and the webservers connect to the db servers via private IP in both circumstances. The same website on respective web servers in each region connected to their respective regional DB server seems to generate very different performance

Any interaction between the respective web server and db server seems to be significantly slower on the Australian server than the UK. That's obviously by firing the action from a browser in their respective locations.

The Australian DB server is slightly different, it has a faster processor and more memory. I work as a .NET developer so I'm not really sure how to determine what the problem is. How can I determine what is going on here and try to work out why DB access appears to be so much slower on the Australian side than the UK side?

There is a further difference, the Australian DB server is running IIS for a classic asp page that loads/uploads resources from an old classic asp site. This doesn't appear to be using much memory or CPU power and is rarely used so I don't see how it could be the problem. What I would really like is a way to see where the slow down/timings are occurring in the process from the webserver. What is the best way of doing this?

edit - What I have noticed is that in SQL profiler the same events on the Australian DB seem to trigger much higher duration for the Audit logout. As far as I can gather this just means that the events occurring during the login to the SQL server are taking longer. I'm not sure if anything else can be determined from this.

Rob
  • 31
  • 7

1 Answers1

1

Here would be the first things that I would do in your situation:

  1. Download and run the Blitz diagnostic script from Brent Ozar. Pay particular attention to the high priority items, but be sure to look through the whole report.
  2. I'd go double-check on how much RAM is allocated to SQL on each server. By default, SQL has an unlimited allocation of RAM. The memory limit should be capped at maximum at 4gb less than total RAM in the system. And the more other stuff that is running on those servers, the larger the spread should be between SQL's memory max and the system installed amount. The goal here being to make sure that the OS isn't starved of RAM to support the tasks being asked of the computer. The OS can't efficiently interface between the hardware and SQL if it is RAM starved.
  3. Download the Microsoft SysInternals Suite and fire up Process Explorer. Look for performance bottlenecks using its resource monitors, particularly disk I/O.
  4. Also from Sysinternals Suite, use TCPView to check how many connections your DB server has active to it. If the number is in the hundreds, you will probably need to pay attention to why this is and ensure that nothing is getting stuck.
  5. Finally, after the easy stuff is double checked, break out the SQL profiler and start looking for slow queries and other problems. https://www.red-gate.com/simple-talk/sql/performance/how-to-identify-slow-running-queries-with-sql-profiler/
Rob Pearson
  • 429
  • 2
  • 12
  • Thanks for your advice, the blitz diagnostic seems very useful. I'll go through and see if any of this can establish the issue. – Rob May 25 '18 at 15:44
  • You're very welcome. Please let me know if you find anything interesting (or weird). I'm happy to help. – Rob Pearson May 25 '18 at 16:05