question

Cyborg avatar image
Cyborg 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?
extended-eventsupdate-stats
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Actually, turning on Auto Update IS a good practice in the vast majority of instances. What makes you think it isn't? Links?
0 Likes 0 ·
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.
0 Likes 0 ·
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.
0 Likes 0 ·
Yes, we have recompile issue. Now I need to compare the cost associated with update stats and the recompile.
0 Likes 0 ·

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wonderful! Let me wait for others inputs. Thanks @KenJ!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.