Connection to SQL Server 2008 R2 Database Server is SLOW

Question

The database server is a VM running SQL Server 2008 R2 on top of Windows Server 2012, 24GB RAM allocated and 2TB of disk space.

Overall, the database connections are very slow and one thing that stands out is that the connection to the database server via SSMS takes 5-10 seconds.

On other much less powerful servers, it takes 1-2 seconds.

The VM is technically way more powerful than other machines, but the connection to the server is too slow.

So, my guess is the issue is network related, but any clues on where I should be looking?

Thanks!

Solution

You mention that the connection takes a long time - once the connection is established do operations / queries proceed at a normal speed? If it's just establishing connections that are slow but you can receive result sets at a normal rate I would make sure there are no authentication issues like the site is not properly defined for the database server and it's querying a DC in a remote location, or that it's not using an appropriate DNS server.

If operations are slow after you've established the connection you need to start troubleshooting why the new server is under performing.

Before you can say server A is better than server B, or equivalent, or worse - you really need to have a baseline.

For example - for all my database servers I run a set of SQL IO ( http://www.microsoft.com/en-us/download/details.aspx?id=20163) benchmarks for various read/write permutations. Then before I even install SQL server or anything else I see how the new platform compares to what I was working with before. Keep a history.

The same with network performance - you can use something like iperf to measure the raw bandwidth between to machines on the same subnet, different subnets, different OSs

I would start with basic tests like this to see if anything jumps out. Just because the new server looks more powerful on paper doesn't mean it actually is. If these look good then start benchmarking actual operations within SQL server.

VMs can easily be constrained by other load on their hosts. Perhaps the database server has its storage in a storage aggregate which is overutlized, or overutlized CPUs.

See if the raw stats look good, then look to see if it's something specific to the product configuration (sql server in this) case.

Comments

[AbeP]

Thanks! This was a great starting point for me. The issue did turn out to be the network drivers on the host Hyper-V server was outdated. I also think configuring the new VM to run on RAID-10 might yield better results.