Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2013-07-09 : 17:19:33
|
| We have many integrations with our application that involve nightly syncs. These syncs often do bulk updates and inserts into our database. These often cause locking until they are complete (sometimes for a few minutes). If I ran the inserts / updates through a stored proc that only performed one update/insert at a time in a distinct transaction with a modest (50ms) delay between records would that solve the locking by allowing other SPIDs access to the table between records? I realize that a 5 minute process may now take 20 minutes but at least the application is usable for the duration.Thoughts?Edit: not sure if I should have put this in TSQL forum. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-07-09 : 18:37:45
|
| I'm assuming table partitioning is not an option?I have done bcp out/truncate table/bcp in for very large tables, but it's not really an online operation. You can do this to a staging table and then swap the two tables by name or schema, we do this now for daily data loads. Aaron Bertrand has a nice write-up on it here:http://www.sqlperformance.com/2013/04/t-sql-queries/schema-switch-a-roo-part-2Also read Part 1 linked in that article. You get the same performance as partition switching but it works in all 2005+ editions, however it has to work on the entire table. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2013-07-09 : 19:17:59
|
quote:
Originally posted by ferrethouse
We have many integrations with our application that involve nightly syncs. These syncs often do bulk updates and inserts into our database. These often cause locking until they are complete (sometimes for a few minutes). If I ran the inserts / updates through a stored proc that only performed one update/insert at a time in a distinct transaction with a modest (50ms) delay between records would that solve the locking by allowing other SPIDs access to the table between records? I realize that a 5 minute process may now take 20 minutes but at least the application is usable for the duration.Thoughts?Edit: not sure if I should have put this in TSQL forum.
You might use an "in-between" method of loading the data into staging tables and then doing small batches of inserts/updates in a short transaction to keep the locking time to a minimum. This would probably be much faster that doing a single insert/update at a time.You can adjust the batch sizes up or down till you get good load speed with minimum application impact. You might start with 5000 rows and work up or down from there.You might also consider setting your database to read committed snapshot to minimize the number of locks taken.Using Row Versioning-based Isolation Levelshttp://msdn.microsoft.com/en-us/library/ms179599.aspxCODO ERGO SUM |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-10 : 03:32:43
|
You can also consider using snapshot isolation. Then all rows are modified "off row" in tempdb and original data is still accessible. Then commit the transaction and in a few milliseconds all updated are written to the original table.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-07-10 : 17:59:31
|
quote:
Originally posted by SwePeso
You can also consider using snapshot isolation. Then all rows are modified "off row" in tempdb and original data is still accessible. Then commit the transaction and in a few milliseconds all updated are written to the original table.
N 56°04'39.26"E 12°55'05.63"
We are using snapshot isolation. The problem I'm encountering is write-write locking. Snapshot isolation only solves write-read locking if I'm not mistaken. |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-07-10 : 18:05:36
|
quote:
Originally posted by robvolk
I'm assuming table partitioning is not an option?I have done bcp out/truncate table/bcp in for very large tables, but it's not really an online operation. You can do this to a staging table and then swap the two tables by name or schema, we do this now for daily data loads. Aaron Bertrand has a nice write-up on it here:http://www.sqlperformance.com/2013/04/t-sql-queries/schema-switch-a-roo-part-2Also read Part 1 linked in that article. You get the same performance as partition switching but it works in all 2005+ editions, however it has to work on the entire table.
Don't see schema swapping as an option since writes will be concurrently happening you would have to reconcile the staging and prod for the duration of the inserts into staging (5 minutes or whatever).I realize that my proposed solution still causes locking but the locking is in small enough chucks that users will still be able to use the application.Edit: I should also note that many of our tables do have triggers on them so the schema swapping wouldn't work. The triggers are actually the primary reason for the slowness and locking. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-07-10 : 21:07:47
|
| Can you disable the triggers for these updates? Or rewrite them to detect it's a bulk update and skip whatever processing they do?You can create triggers on schema swap tables. The trick is to make sure to only use one-part names inside the trigger body. When you schema swap those references will still be valid, and the trigger's schema is tied to the table. |
|
|
|
|
|
|
Strategies for bulk updates without locking