August 15, 2012 at 5:31 am
We're experiencing high waits for this type on a user database during high volume inserts on a partitioned table. What are the general recommendations for preventing this type of wait?
August 15, 2012 at 7:10 am
You're experiencing I/O waits during I/O operations... The only way to speed that up is to get faster/more disks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 15, 2012 at 7:28 am
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.
That's what I thought. Thanks.
August 15, 2012 at 9:31 am
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?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 15, 2012 at 9:53 am
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
August 15, 2012 at 9:59 am
Typically no, Pagelatch is a wait for a latch on a page in memory. Need some idea what pages the latch waits are on to say much more.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2012 at 10:09 am
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
August 15, 2012 at 10:42 am
Oops. Missed the missing 'IO' & responded too quickly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply