locked
Update Statistics blocks update queries? RRS feed

  • Question

  • Hi All,

    "update statistics requires table scans internally leaf-level scans of indexes and column scans, may increase I/O contention, may use the CPU to perform sorts, and uses data and procedure caches. Use of these resources can adversely affect queries running on the server if you run update statistics when usage is high. In addition, some update statistics commands require shared locks, which can block updates.

    To reduce I/O contention and resources, run update statistics using a sampling method, which can reduce the I/O and time when your maintenance window is small and the data set is large. "

    Is the above given information is true/reliable.

    Please suggest your views on this.

    Regards,

    Kalyan


    ----Learners Curiosity Never Ends----

    Monday, August 19, 2013 12:57 PM

Answers

  • Hi Kalyan,

    I have never seen this occur.  UPDATE STATISTICS effectively runs "WITH (NOLOCK)" and therefore does not take out locks on the data.  Therefore it should not cause blocking if you try to update any records whilst it is executing.

    The remainder of your quote is correct though as, when doing a FULLSCAN, it will read all records and potentially perform sorts and therefore this is quite IO and CPU intensive and can slow down queries if run during busy periods...  but it should not block them.

    Thanks,

    Kevin

    Monday, August 19, 2013 1:12 PM

All replies

  • Hi Kalyan,

    I have never seen this occur.  UPDATE STATISTICS effectively runs "WITH (NOLOCK)" and therefore does not take out locks on the data.  Therefore it should not cause blocking if you try to update any records whilst it is executing.

    The remainder of your quote is correct though as, when doing a FULLSCAN, it will read all records and potentially perform sorts and therefore this is quite IO and CPU intensive and can slow down queries if run during busy periods...  but it should not block them.

    Thanks,

    Kevin

    Monday, August 19, 2013 1:12 PM
  • The only thing I can think of is DDL being blocked by a schema-stability lock, which is taken by pretty much anything.  So, you wouldn't be able to change a table's definition whilst UPDATE STATISTICS is running, for example.


    Thanks, Andrew

    Monday, August 19, 2013 1:17 PM
    Answerer