Lexa (8/15/2012)
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.
If you have multiple processes competing to open latches on the same pages, especially one as intense as an index rebuild/org routine, that could be the problem. When doing a mass insert you'll likely be doing lots of random allocations in your nonclustered indexes, and depending on your DDL lots of sequential or random allocations in your clustered index. Can you please provide the DDL for the table (incl indexes and partition scheme and function)? Does it have an identity or sequential GUID column?
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?
Not necessarily. The buffer pool does just that, it buffers us from the I/O subsystem so when you're waiting on a buffer latch it has to do with memory, not disk. I think your system would be in a world of hurt if you were seeing buffer latch waits as a result of upstream IO latch waits.
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%5B/quote%5D
Use the query in the article to find out what exactly your query is waiting on.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato