We optimised our databases by indexing and using more efficient LINQ statements. Now, the web app is overloading the CPU. It is written very inefficiently with nested iterations getting data mostly from a cache store. We think that the slower database retrieval times kept a throttle on the system.
Is there a way to make retrieval times slower so that we can save the CPU? There were many hundreds of improvements made that would be too complex to role back. Can we strangle the bandwidth or changes SQL Server settings ?
Update We use Amazon RDS which doesn't allow changes to memory or parallelism.
I can't reduce memory or CPUs, so.. would I get a tempering effect if I ran some traces on the SQL Server? At 40K statements a minute, it could be costly enough to slow results to the web app?
Another option could be to run a pointless statement in a loop from the query window. Can anyone suggest something safe that would slow things down across the board?
Things get heated daily at about 6pm for 3 hours.
RDS Restrictions:
• AlwaysOn AG* • Stretch Database • Buffer Pool Extension • BULK INSERT and OPENROWSET(BULK…) features • Data Quality Services • Database Log Shipping • Database Mail • Distributed Queries(Linked Servers)* • Distributed Transaction Coordinator(MSDTC) • File Tables • FILESTREAM Support • Maintenance Plans • Performance Data Collector • Policy-Based Management • Polybase • R • Replication • Resource Governor • SQL Server Audit • Server-level triggers • Service Broker endpoints • T-SQL endpoints (all operations using CREATE ENDPOINTs are unavailable) • WCF Data Services • SSAS, SSRS, SSIS • Master Data Services
You could migrate the RDS database to a smaller instance. So, for example if you are on a db.t3.medium, you could migrate to a db.t3.small, which has less CPU and RAM.
https://aws.amazon.com/rds/instance-types/
Of course, you'd need to work out a migration strategy in order to be able to do that. Shouldn't be too difficult except for maybe a gap during cutover.