http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx

Check out CXPACKET waits. Also the best practice on (properly tuned) OLTP systems is to set max dop = 1.

If your IO subsystem can't keep up with parallelism of...

When you are modifying the code in QA, are you using the guts of the stored procedure and executing it as a batch? I often do this. You...

CU7 was released just a few weeks ago.

Peace - have your company hire a mentor for you for a short period of time. He/she can not only help improve performance in the short term but also...

Definitely agree with this!!

Didn't the OP state it was a HEAP table, in which case fillfactor isn't applicable. It would be interesting to...

you are missing that the OP is inserting 10M rows in a single transaction. I do agree that if you did a series of single-row inserts the overhead stuff...

There was a VERY nasty bug (related to spin locks?) that was fixed in CU 3? or 4? (last Oct??) that exhibited this exact behavior. I am unfortunately unable...

Michael Earl (4/30/2008)

---

You have a table that has a single integer field and it is a heap.

Every time a transaction is logged against this table, it has to include both...

I wonder if the initial 'pause' in query response isn't simply because the data is having to be lifted up from disk to RAM. Have you checked for I/O...

One additional comment: it doesn't do much good to defrag indexes in a database that doesn't have sufficient free space in it either. And given the number of...

Database collation settings could be different too.

Get and read these two documents: http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc and http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx.

Setup and execute track_waitstats_2005, which can be found in the above. It will help you narrow down what...

It seems pretty obvious to me that this issue absolutely, positively cannot be related to the sql server itself. When the app was running locally you got significantly more...

Larry, you are correct. the constraint used for the date partitioning would allow the optimizer to know that the only possible date for each of the 8 partitions would...

Glad you found a workable solution and didn't have to roll your own from scratch. For completeness, IIRC ApexSQL Diff does have a commandline interfact so you can automate...