0

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?

  • Are you still seeing pagelatch* waits or has it gone after change you made ? – Shanky Aug 9 '15 at 10:46
  • 6
    Why are you inserting a row and then updating it 5 times? Why don't you do all that work before you insert the row? Can you give us a better idea of all the things your code is doing? Right now there's not really any way for any of us to guess what you might be able to do to decrease CPU usage. It's like asking you what I can do to get better gas mileage, but you have no idea what kind of car I drive, where I go, how heavy my foot is, etc. – Aaron Bertrand Aug 9 '15 at 13:32
  • @Shanky pagelatch* are gone now. – John Tseng Aug 9 '15 at 15:40
  • 1
    I am with Aaron in performing the manipulation locally before the final insert. By storing initially and then running a series of updates, presumably from the same client, you are incurring a performance penalty by involving remove storage too soon from the client. It is always more efficient to update locally stored data than remote data across a network link. A second vote for performing all processing locally and then have a final commit. – James Pulley Aug 15 '15 at 15:44
1

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.

1

As others have suggested, I would try batching the incoming transactions.

One approach could be storing incoming rows in an in-memory table (requires upgrading to Enterprise Edition) and then move batches of rows into their final storage using something like a SQL Server Agent job every five minutes or whenever you are sure they won't change again.

In-memory tables considerably reduce locking and latching issues, which make them ideal for the type of insert-update-update-update-delete workload you're describing. This is particularly efficient with natively compiled stored procedures.

0

You have PK clustered index as a uniqueidentifier?
That index has massive fragmentation.

You want a PK that is inserted in the order of the PK
Why not just use an identity?

Why are are you updating a value rather than just insert the correct value?

Are you inserting just one row at a time?
One row one round trip is not efficient.
Fast inserts with parameters

  • 1
    Can't say for sure why you got a down vote. Your answer is somewhat vague. Expound on your answer (e.g. why do you think the CI has massive fragmentation) to provide more information to your reasoning. – Shawn Melton Aug 9 '15 at 19:37
  • @ShawnMelton stackoverflow.com/questions/11938044/… – paparazzo Aug 9 '15 at 19:51
  • 1
    Thanks for the answer. I switched from identity to uniqueidentifier specifically to get that fragmentation. Identity was causing all my threads to block on each other. – John Tseng Aug 10 '15 at 3:20
  • 1
    Good for you - you got fragmentation. Really identity was causing threads to block? – paparazzo Aug 10 '15 at 3:29
  • 1
    @Paparazzi, page contention is a problem when you get to a certain amount of iops. Short of a complete redesign, fragmentation may be an acceptable compromise. Agree with batching, if this is possible from the application. – Daniel Hutmacher Apr 6 '16 at 6:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.