opc.three (8/15/2012)
Grant Fritchey (8/15/2012)
You're experiencing I/O waits during I/O operations... The only way to speed that up is to get faster/more disks.Are you sure you're not thinking of PAGEIOLATCH_UP? I think PAGELATCH_UP is a memory latch (buffer, i.e. not I/O) caused when doing an update. At a guess there is potential for allocation contention issues when you see this wait. Can you provide the DDL for the partitioned table? How many rows are you inserting?
It wasn't many rows about 40K but I think the main issue was the fact that our dynamic re-indexing process started to rebuild an index in the middle of the insert process. That specific partition itself had only about 30K rows initially + the 40K that got added.
Partition is by store, and a store can have any number of items from 0 to 22 mil. It's not the best partitioning strategy so we're working on a new one to replace the current one.
Wouldn't PAGELATCH_UP be also tied to IO perhaps indirectly? This link gives some good info but we can't implement this type of partitioning. http://sqlcat.com/sqlcat/b/technicalnotes/archive/2009/09/22/resolving-pagelatch-contention-on-highly-concurrent-insert-workloads-part-1.aspx