2

What locks are taken during the various statistic operations below in SQL Server assuming the default options of auto create stats and auto update stats are set to true?

  1. Automatic creation of a statistic object
  2. Automatic update of a statistic object
  3. Manual creation of a statistic object
  4. Manual update of a statistic object

Is there any difference in the locks taken in scenarios 1 & 2 above when statistics are set to update asynchronously?

2

AFAIK, the only real X lock that is taken is on the 'statistics resource'. The rest of the locks can be read around and don't block.

That is a resource that only statistics operations care about. It exists to limit the number of stats operations in progress on a single table to one at a time. Auto-statistics operations do not wait for it but just abort if they cannot get it. Manual statistics operations do wait for the resource.

https://connect.microsoft.com/SQLServer/feedback/details/174619/update-statistics-holding-exclusive-lock-and-blocking

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.