Since update statistics uses dirty reads (transaction isolation level 0) for data-only-locked tables, you can execute it while other tasks are active on the server; it does not block access to tables and indexes. Updating statistics for leading columns in indexes requires only a leaf-level scan of the index, and does not require a sort, so updating statistics for these columns does not affect concurrent performance very much.
However, updating statistics for unindexed and nonleading columns, which require a table scan, worktable, and sort, can affect concurrent processing.
Sorts are CPU-intensive. Use a serial sort, or a small number of worker processes to minimize CPU utilization. Alternatively, you can use execution classes to set the priority for update statistics.
See Chapter 3, “Using Engines and CPUs,” in Performance and Tuning Series: Basics.
The cache space required for merging sort runs is taken from the data cache, and some procedure cache space is also required. Setting the number of sort buffers to a low value reduces the space used in the buffer cache.
If number of sort buffers is set to a large value, it takes more space from the data cache, and may also cause stored procedures to be flushed from the procedure cache, since procedure cache space is used while merging sorted values. There are approximately 100 bytes of procedure cache needed for every row that can fit into the sort buffers specified. For example, if 500 2K sort buffers are specified, and about 200 rows fit into each 2K buffer, then 200 * 100 * 500 bytes of procedure cache are needed to support the sort. This example requires about 5000 2K procedure cache buffers, if the entire 500 data cache buffers are filled by a sort run.
Creating the worktables for sorts also uses space in tempdb.
