I have a fairly simple SQL Server 2014 Standard database with one main table. Here's the workload:
I insert a row, do a few updates to it (up to 5 updates), and move on. I always update recent rows. After a few seconds, I never update the row again. I do occasional selects, but my main problem is with the inserts and updates, even when there are no selects happening.
The queries look like:
insert into Table (Id, Field1, Field2, ...) values (@Id, @Field1, @Field2, ...) update Table set Field3 = @Field3, Field4 = @Field4 where Id = @Id
Id is the only column in the clustered index. There are no other indices on this table. Id is a uniqueidentifier. I changed it from
bigint identity after seeing a huge amount of pagelatch_ex waits.
It's doing about 45k requests/sec with the CPU at 90% usage and IO at 50%. This is in AWS where I'm on c3.2xlarge and 2000 IOPS. I don't have much room to grow CPU wise, but I do have another 10x IOPS. What can I do to decrease CPU usage?