Timeline for Locks caused by async stats update
Current License: CC BY-SA 3.0
8 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jul 4, 2017 at 13:47 | history | edited | S4V1N | CC BY-SA 3.0 |
added 4 characters in body
|
| Jul 4, 2017 at 10:11 | comment | added | 0wl | You're right, daily maintenance plan is a more robust scenario and I already have one. But I want to have autoupdate as some kind of insurance during daytime. But if I find, I couldn't miss its bottlenecks, I'll surely abandon it | |
| Jul 4, 2017 at 10:02 | comment | added | S4V1N | You should monitor how many rows are inserted/updated/deleted on a daily basis on that table/tables, and how often and when is that query executed and try to come up with a plan when to update statistics | |
| Jul 4, 2017 at 9:42 | comment | added | 0wl | "Your problem however is that you have auto update stats enabled" -- Autoupdate statistics is exactly what I am trying to implement. And my goal is to make it create as less locks as possible. I thougth, async update would help me, but, you see, there are some complications. I agree, It has nothing with Schema-Stability. Problem occurs when system spid acquires Schema-Modification lock (to change, like I said, old statistics to a new one). And, as I see, this lock can cause a lot of other users stall on Schema-Stability lock, which is incompatible with SCH-M. | |
| Jul 4, 2017 at 9:42 | comment | added | S4V1N | It will Erik, but at least he can be prepared for it, and update whenever he believes that these queries are not executed | |
| Jul 4, 2017 at 9:37 | comment | added | 0wl | "Will updating stats manually not cause blocking? " -- it will | |
| Jul 4, 2017 at 9:33 | comment | added | Erik Reasonable Rates Darling | Will updating stats manually not cause blocking? | |
| Jul 4, 2017 at 9:13 | history | answered | S4V1N | CC BY-SA 3.0 |