Timeline for Any way to reduce sysallocunits contention under a heavy concurrent INSERT workload?
Current License: CC BY-SA 4.0
14 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Sep 14, 2022 at 8:00 | history | edited | Glorfindel | CC BY-SA 4.0 |
broken link fixed, cf. https://meta.stackoverflow.com/a/406565/4751173
|
| Jan 23, 2014 at 18:38 | vote | accept | Max Bolingbroke | ||
| Jan 23, 2014 at 15:01 | answer | added | Thomas Kejser | timeline score: 5 | |
| Jan 21, 2014 at 14:16 | comment | added | Thomas Kejser | Ok - this makes sense. I will add a detailed answer later today. | |
| Jan 21, 2014 at 9:39 | comment | added | Max Bolingbroke | @ThomasKejser: I'm very happy to give out more info! I've added something to the end of the question about the actual workload. I'm using clusters, and I am indeed doing a INSERT BULK operation + DML including INSERT and UPDATE in the insert process. Please let me know if there is anything else you need to know or if my explanations are unclear. | |
| Jan 21, 2014 at 9:36 | history | edited | Max Bolingbroke | CC BY-SA 3.0 |
added 1091 characters in body
|
| Jan 20, 2014 at 21:24 | comment | added | Thomas Kejser | @MaxBolingbroke: Unsurprisingly, sysallocunits is the underlying structure of sys.allocation_units. If you are seeing contention there, I am 90% sure you must be doing some form of DML or BULK work on the INSERT. I am sorry to trouble you for more data, but could you describe a bit about the workload that is causing this? Also, are you using a heap or a cluster? | |
| Jan 20, 2014 at 20:19 | comment | added | Max Bolingbroke | Thomas: I'm creating a fair number of temp tables (actually table variables), but tempdb is not showing up as a source of contention at all. I suspect that the SQL Server temp table cache is helping a lot here. | |
| Jan 20, 2014 at 20:18 | history | edited | Max Bolingbroke | CC BY-SA 3.0 |
added 1680 characters in body
|
| Jan 20, 2014 at 19:11 | history | tweeted | twitter.com/#!/StackDBAs/status/425344789266321409 | ||
| Jan 20, 2014 at 18:36 | comment | added | Thomas Kejser | Also, please add the metadata section of the DBCC PAGE output to the question | |
| Jan 20, 2014 at 18:34 | comment | added | Thomas Kejser | Do you happen to be creating a lot of temp tables? | |
| Jan 20, 2014 at 17:55 | comment | added | Vladimir Oselsky | Here is similar information, in addition to what you have done, it is recommended to restart SQL Server after all changes have been made. | |
| Jan 20, 2014 at 17:44 | history | asked | Max Bolingbroke | CC BY-SA 3.0 |