We have 2008R2 servers, We are testing performance on new hyper-v servers before we migrate from old 2005 phsyical servers and as no one is on the new server, and with newer cpus, more memory, better disks etc, we would expect better or at least equivelant speed, but our erp application feels much slower in testing, but I am having trouble to understand exactly where the problem is and how to solve it, I've tried checking lots of counters/waits etc.

I've now dropped down to a simple small query.SELECT * from dbo.dimCustomer from adventureworksDWIt seems async_network_io is much higher but this is with running the simple query directly in ssms on the server (via rdp). (used http://sqlcat.codeplex.com/wikipage?title=ExtendedEventsWaitStats)

If I run this query many times over (the table is only 9MB), with no other users on the system, I know the data is cached.On 'servers' I am rdp'd onto, I get values around this, but they do change a lot but no where near my desktop speed.CPU time 219 ms, elapsed time 1387

On my desktop, I getCPU 31ms elapsed time 507ms

Any ideas why a server would be much slower when no network involvement, no disk involvement afaik.

My first guess would be the virtual machines aren't configured correctly for optimal SQL Server performance. If not done exactly right, it's pretty usual to get some performance degredation when you go virtual.

Another possibility is that the desktop machine and the old servers may have had the databases on local drives, while the new servers have them on a SAN or some such. Local bus is usually faster than a SAN, even if just due to latency.

also, because of differences in the SQL engine's optimizer between versions, if you upgrade from 2005 to 2008R2 like you said, at a minimum you need to updating statistics, and i'd consider rebuilding the indexes , as that is a critical piece that must be part of your upgrade process.

you can search and there's lots of posts stating "new server is slower than old one", and that is often a root cause.

Thanks for all the responses, I will try to answer/respond to them all.

in my small test to see a reproducable negative difference, I am doing a select * from a 9MB table, which will do a full clustered index scan so a table scan, so not sure how rebuilding statistics will help in this specific case. We have done update statistics on the erp database to no effect, but I will give that a shot, and if all the data is in cache, why would the i/o have that much of an impact if all data is in local RAM?

However I will find a few more machines to do an identical test with local/remote and virtual/physical.

In this specific example it is the adventureworksDW database so it is not a 2005 to 2008R2 issue, as the source is 2008R2.

We've run SQLIO some time ago, and the virtual servers drives are much faster than my local drive but I will run the tests again, in case something has changed.

The max sql memory is set, but the data is only 9MB, and I re-run the select * from dbo.dimcustomer many times to make sure it is fully in memory.

I will check with the network/server guys about the energy saving features.

All references I can find to network_io wait type issue do not seem to be relevant here, as I am running the query on the server in ssms and with cached data.

If it is only going to be a SELECT from a 9MB table then you are most of the way there. If it is to predict how your production system will work on the new servers then you are going down the wrong tack.

If you want to work out how your production workload will perform then you need to run part of your production workload. SQL Server has Replay capabilities, so you can capture a SQL Profiler trace of your production system and replay the workload on your new system. While both the capture and replay are running, also run a PAL trace to capture IO statistics. You can then compare raw run times between the capture and the replay to get an idea of comparative run times, and compare the PAL output to see where bottlenecks exist in your old and new systems. If you find a bottleneck in your new system you think you can fix, then do so and rerun the workload.

At some point you will get to a state where you cannot fix any further bottlenecks found by PAL. At this stage you can look at overall run times, response times, etc, and have meaningful data you can report to management about the new system performance. It is then up to the managers to decide if the new system is fit for use.

Original author: SQL Server FineBuild1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005.23 July 2015: now over 34,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.