Sql-server – database with same schema and same AWS instance type, but different performance

awsperformancequery-performancesql-server-2008-r2

Hi I am getting a new set of production servers ready. I generated a DB script from SSMS using the schema from Server A to deploy to a new AWS instance of the same type as Server A. After populating the required configuration tables on the new DB I ran and saw a performance degradation on this new server. When I look at the execution plans for a query I see that they are very close to the same other than the value for "Memory Grant". Memory Grant is more on the original server (1656 vs 1112 on the new server). Any ideas on what could be causing this performance issue on the new server?

Best Answer

There are several things which are needed to know here. Some EC2 Instances on AWS are using shared CPU, this could be one reason why your instances are performing different. Also the same EC2 instances in different regions might be different from each other from hardware perspective. Also interesting is to know when you did the performance test, if you setup your server for the first time and run some tests, these will perform not so well compared to a machine which already calculated the same queries before. The reason is the caching of course, caching of raw data, caching of query plans and re-using instead of compiling them and deciding which one to take.

Now we have to counters about memory grant, I think we are talking about memory grants pending.

Memory Grants Pending

This counter shows you the number of processes waiting on a workspace memory grant. Of course the lower the number, the better your performance.

But for you the new server has a lower value, so it is performing at least better for this specific value.

Brent Ozar, the king of performance, has a nice list of counters which are worth looking at:

http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

Monitoring only one performance counter will in most cases not give you enough vision.