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...

1) partitioning the table won't help, since the query at hand will still have to read all the data. Well, it could help a tiny bit if the partitions...

This is WAYYYY to open-ended a question. See here in BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5530f484-5bc4-467c-ae50-d708dafb24d0.htm for the starting topic on SQL Server locking. There also is no default type of...