July 7, 2022 at 1:40 pm
Server team just rebuilt a new SQL server for the application team for their work or upgrade. We use AWS. I checked all the specs to match with the old server and it looks good, even better since the data, log file and tempdb sit on their own drive now. During the pre deployment, the application team experienced slowness. The process which used to take 3 - 4 hours started taking 10+ hours. The IOPS and Throughput were increased/changed as well to match with the old server from AWS console and we thought this would definitely increase the pre-deployment process but it didn't. I pinged both the old and new server from the app server and the response time is good too. SQL Server itself looks good, resources are good. Now the question. Can someone guide me what else should be looking at? Any help is highly appreciated.
July 7, 2022 at 2:00 pm
Did you upgrade to a new version? If so, update statistics. What version did you move from? to? (presumably one of them is 2017 since you're here)... If you moved from pre-2014, you could be encountering problems due to cardinality estimator changes
What instance type/size? What types of disks? How big is each disk? (w/o provisioned I/O, it's proportional to disk size) Provisioned I/O? Are you using instance SSD drive for tempdb?
What does AWS monitoring show for Network bytes/packets? Disk read & write bytes, operations?
Do you have baseline query plans to compare to new plans to see what's actually changed? Cached plans would be gone after migrating to a new server.
July 7, 2022 at 2:55 pm
It's all about the fundamentals. If every single thing is equal, so should performance be. So things are not equal. It's a question of walking through and figuring it out.
First up, @ratbak's question is a good one. Is this a different version of SQL Server. Most importantly, have you moved from pre-2014 to 2014 or above?. The change in the cardinality estimation engine in 2014 can lead to issues. However, any other possible upgrade could also be an issue. Plus, if you did upgrade, did you change compatibility level on the databases?
After that. Server settings, are they the same. We're talking Max Degree of Parallelism, Cost Threshold for Parallelism, all of those things. Which ones are the same and which are different.
Then, database settings. Check each one. Validate they're the same.
Then, statistics updates, cache refresh, all the internals stuff.
You just have to work the problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 19, 2022 at 1:53 pm
After changing just the compatibility level, queries are running efficiently. Thanks all...
July 19, 2022 at 2:51 pm
After changing just the compatibility level, queries are running efficiently. Thanks all...
That also means that you may be missing out on some newer technology in the database engine. It might actually be worth checking the code and make it run better under the higher compatibility level.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 27, 2022 at 8:47 am
Latest CU applied ?
I had that issue with one application on SQL 2019, that there where a bug in SQL that slowed down application performance like you described.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply