April 29, 2011 at 9:14 am
Ninja's_RGR'us (4/29/2011)
Maybe I missed it but I can't see a with async option on the create/update stats command. Does that mean that the setting is only at the db level and that if you have async ON and then you issue an update stats command, then it does like sp_startjob and does't wait for completion before completing the batch?
It only applies to auto stats updates processes. If you have both AUTO_UPDATE_STATISTICS and AUTO_UPDATE_STATISTICS_ASYNC on and you run a query that happens to result in SQL updating a stat (or creating a new stat if that's enabled) then it'll run in the background. To my knowledge there's no instance where an explicitly called stats update will run in the background.
At my last place we actually had an issue where SQL needed a stat on a text column and it took a while to run. In that case we wanted to let it complete so it was there but under different circumstances we may have wanted to kill it and handle it different so we weren't waiting the 10 minutes for it to run. Took me a while to remember we had run into someplace this may have come in handy if we didn't want to let it complete.
April 29, 2011 at 9:21 am
cfradenburg (4/29/2011)
Ninja's_RGR'us (4/29/2011)
Maybe I missed it but I can't see a with async option on the create/update stats command. Does that mean that the setting is only at the db level and that if you have async ON and then you issue an update stats command, then it does like sp_startjob and does't wait for completion before completing the batch?It only applies to auto stats updates processes. If you have both AUTO_UPDATE_STATISTICS and AUTO_UPDATE_STATISTICS_ASYNC on and you run a query that happens to result in SQL updating a stat (or creating a new stat if that's enabled) then it'll run in the background. To my knowledge there's no instance where an explicitly called stats update will run in the background.
At my last place we actually had an issue where SQL needed a stat on a text column and it took a while to run. In that case we wanted to let it complete so it was there but under different circumstances we may have wanted to kill it and handle it different so we weren't waiting the 10 minutes for it to run. Took me a while to remember we had run into someplace this may have come in handy if we didn't want to let it complete.
Ok, but what's the impact of letting this run async in the background? Does it lock the whole table, does it slow the server WAY down? Eat up all the CPUs?
April 29, 2011 at 10:20 am
It won't lock the table but it could be a significant resource drain depending on the statistic that's being worked on. There may be other reasons to kill it but I haven't ran into any.
April 29, 2011 at 1:57 pm
Wow....
I thought this was a real easy question until I saw the results.
I saw a lot of people asking if this is used a lot in the real world.
My Answer:
In the perfect world no, it is not used. In the real world I have never had to use it, but auto stats update is not enable on my large Transactional tables. We update Indexes and stats nightly during a maintenance window.
😎
April 30, 2011 at 10:03 am
SanDroid (4/29/2011)
Wow....I thought this was a real easy question until I saw the results.
its an easy question if you happen to have used the command or googled it but I bet 99% of people have never used it. I for one had never heard of it so I've learned something. Not sure I will ever use it but maybe one day I will pull it out of the hat and astound someone with my deep knowledge of SQL 🙂
If you are still on SQL2000 you would not have heard of this for sure.
---------------------------------------------------------------------
May 3, 2011 at 2:48 pm
Thanks for the question!
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply