For a general-purpose or OLTP design, the initial index design should be more conservative:
Clustered index on Primary Key.
Unique non-clustered index any other unique keys.
Index supporting each Foreign Key (where not already covered above).
Then, for very large tables, consider changing to a Nonclustered Primary Key and a Clustered Columnstore.
innodb_buffer_pool_size is 47G -- this is good, and the main user of RAM.
tmp_table_size seems to be set to 6G -- This is dangerously high. If you happen to run a few complex queries at the same time, you could run out of RAM, thereby leading to swapping, which is very bad for MySQL performance. Set it to no more than 600M.
If that does not suffice, let'...
Not having the full table definition, I'm going to take a stab at what it might be. Based on the screenshot in your question, the table could look something like this:
CREATE TABLE dbo.TaskSchedulerItem (
TaskSchedulerItemID int IDENTITY(1,1),
One thing stands out clearly for me - this warning in the XML query plan:
<PlanAffectingConvert ConvertIssue="Seek Plan" Expression="N'Public'=CONVERT_IMPLICIT(nvarchar(20),[r].[SecurityLevel],0)"/>
A little known speed killer in SQL Server is passing an nvarchar() parameter to use in an indexed seek against a ...
As per my understanding in above scenario we do not need order by as index have those columns already sorted
That understanding is incorrect, and that plan shows one reason why. A parallel index scan doesn't output rows in index order, as each thread reads at a different location in the sort order. You can't expect rows in any particular order without an ...
What you described is normal behaviour.
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
delete from t1 where object_id is null;
delete from t2 where object_name = 'T1';
alter table t1 add primary key (object_id);
alter table t2 add constraint t1_fk foreign key (object_id) references t1(object_id);
create index ...
What tuning should I do to lower the CPU usage of Azure MySQL?
Left to itself, MySQL will not consume large amounts of CPU.
It will do so, however, when it is asked to perform [lots of] CPU-intense queries.
You need to investigate the workload that your database is under and tune any poorly-performing queries.
I strongly suspect this is a case where ...
The table in the question isn't partitioned. I assume the intended definitions are:
CREATE PARTITION FUNCTION DemoPartitionFunction (datetime)
AS RANGE RIGHT
FOR VALUES (DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -7),
DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -6),
DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -5),