Increase SQL Server Performance 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?
It's doing about 45k requests/sec


Do not do 45k requests per second.

On the insert side you can batch them and use batches of inserts.

Possibly use a stored procedure - that should be a little less intense on the CPU (less parsing).

But mostly consider whether you can cut down the number of operations. BulkCopy is quite nice and you can basically do two operations per second with all inserts and updates in one run (bulkcopy into temp table, merge into target).

This would likey liminate nearly all your CPU.

Yes, this may require some serious reprogramming. But if you need to get down the CPU usage, this is the way to do it.