Recently, an issue was experienced where AWS RDS SQL server was consuming 100% CPU throughout the day. CPU capacity was really high hence it was quite unexpected. Upon investigation, it was found that there was a particular query was taking huge CPU during execution. But surprisingly, when the query was executed from SQL management studio, it was running fine, even it was executing within a second.
Please see below how to investigate high CPU usage issue for AWS RDS. This could be applicable for standalone SQL Server too.
Firstly, we looked at the RDS CPU utilisation report when it has started to happen. Find out the time.
Confirm the same with application team when application has started to see this application error.
Right click on database instance => Reports => Standard Reports => Performance - Top queries by total CPU Time
[You need to have View Server State permission with your user]
Find the application query that is taking the CPU. Press + sign.
It will show when the query plan was created for this query. For my case it was the same day when the issue started.
Now follow the below procedure to remove the cached plan so that RDS can create a better plan again.
SELECT TOP 10 *
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases
ON dm_exec_sql_text.dbid = databases.database_id
WHERE dm_exec_sql_text.text LIKE '%AWordFromYourQuery%';
Replace "AWordFromYourQuery" with a word from your query that is uncommon to other queries.
Take out the Plan_handle for your query.
Execute the FREEPROCCACHE as below.
DBCC FREEPROCCACHE(Plan handle obtained from above step).
DBCC FREEPROCCACHE (0x050011007A2CC30E204991F30200000001000000000000000000000000000000000000000000000000000000);
If you are lucky, this can resolve the issue. It did for my case.