Not sure what you mean.

I had SSMS opened and I had 5 connections...

I won't have time to finish those tests but I seem to be getting as much as 10-15% throughput increase with that little change and 1M updates in 2 minutes...

Hence you'll need more hardware....

Or a difference solution.

40% is not "much" but as soon as you get a burst or cpu heavy query you're toasted.

But using a tinyint here wouldn't...

Also I guess that you don't need a Datetime for the create date.

Smalldatetime might save you a few page reads here and there as well.

I know this seems...

Any way to move the PK from GUID to TINYINT or SMALLINT?

That would give "less" work for the server as the key would be ±18 times smaller.

I know it's not...

Can we see a little more info about the table design and which table is having the issue?

Maybe you could go around the issue with other tools of sql server.

And...

robbemorris (10/12/2010)

---

The table and the procedure really are this simple. the primary key is a clustered index on a single column.

Keep in mind, these are thousands of simultaneous updates...

Then you need more hardware power...

Checkout where the server is waiting (IO, RAM, CPU). You'll likely find the problem in that order.

It's also possible to do the insert solution...

Well 2 things.

One thing that will likely cause a lock is to do update... then select where.

Depending on the transaction isolation levels that can lock it up enough to slow...

WayneS (10/12/2010)

---

Ninja's_RGR'us (10/12/2010)

---

Are you looking at last date and then last date minus 1 day or even minus 1 business day?

If not you could always do "select top 2 distinct...

I can't hardly see this really locking up...

Are we seeing the whole procedure?

Are you looking at last date and then last date minus 1 day or even minus 1 business day?

If not you could always do "select top 2 distinct order by...

That won't get any more female DBAs in the...

Do you have a trigger on the table?

Phil Parkin (10/12/2010)

---

Ninja's_RGR'us (10/12/2010)

---

Quicksort on a single...