EC2 -SQL Server Slowness

0

We recently migrated our on prem SQL server 2008 R2(8 CPU 16 GB RAM) to EC2 (r5.4xlarge - 16 CPU 128 GB RAM) 2019. The server is very slow, all the queries are slow. We can see CX_PACKET wait being more and PAGELACTCH_SH.

CE is set to LEGACY and Compatibility set to 2008.

Please advice.

Kaja
asked a month ago384 views
3 Answers
1

@riku - No we don't have any difference. the settings are identical.

Kaja
answered a month ago
0

Hello.

What is the CPU usage rate and memory usage rate of EC2?
I think the database may use a large amount of memory depending on the processing.
Is it possible that that is slowing down the queries?

I think the following blog will be helpful for tuning.
https://www.sqlshack.com/troubleshooting-the-cxpacket-wait-type-in-sql-server/

Also, what type of EBS are you using?
If you are using gp2, try using a volume type with high iops performance such as gp3 or io2 to see if the query speed improves.
https://docs.aws.amazon.com/ebs/latest/userguide/general-purpose.html
https://docs.aws.amazon.com/ebs/latest/userguide/provisioned-iops.html

profile picture
EXPERT
answered a month ago
profile picture
EXPERT
reviewed a month ago
  • Are there any differences in parameters between SQL Server on-premises and EC2?

0

Hi Thanks for your response. CPU is below 20% and Memory i have allocated to SQL Server 100 GB. We run on GP3 4k IOPS and 1k Throughput. Im testing the server with one query at a time. The query completes faster in the onprem version but its slow in EC2.

Kaja
answered a month ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions