asked
Extended Events to Capture update stats details.
Is there a way to capture Update Stats info using extended events (like how long it took, how frequent, wait time etc.)? The objective is to find out the impact of having Auto Update stats on. Few of my DBs have auto update stats turned on, I am aware that turning on Auto-Update stats is not a good practice, I need math here too prove my point and shows the performance impact with my current system. Did someone used extended events to capture these kind of details?
Actually, turning on Auto Update IS a good practice in the vast majority of instances. What makes you think it isn't? Links?
Does it depends up on the rate of data change, for OLTP environment with high transaction rate, does the frequent stats update introduce frequent recompile?
http://support.microsoft.com/kb/195565 Feel free to correct me if I am wrong.
Yeah, it can. But I wouldn't turn it off until I knew for sure that I was facing serious recompile issues. Have you already made that determination? Usually, the most frequent problem I see, is that stats don't update often enough, not that they update too often. That's why TRACEFLAG 2371 was such a big deal. In most cases, for most systems, having more up to date statistics is worth more than the recompile costs.
Maybe try the "Auto Stats" event, if you haven't already -
http://technet.microsoft.com/en-us/library/ms190721.aspx It doesn't contain wait time, but does include duration which is a direct proxy for query wait time unless update statistics async is set.
16 People are following this question.
