Multiple threads with ‘update statistics table_name index_name”.
The command “update statististics table_name” updates the histogram for leading column of each index (as well as information about total number of rows in the table).
The command “update statististics table_name index_name” updates the histogram for the leading column of that index (as well as information about total number of rows in the table).
Therefore, given a table
Create table table_a
create clustered index idx_1 on table_a (col_1)
create nonclustered index idx_2 on table_a (col_3, col_2)
Either approach is fine for a single table, but when you have a hundreds of tables (and support personnel complaining that update statistics takes too long), most DBAs (including me), break out the single job into multiple jobs. These can be run at the same time or on multiple days of the week.
Splitting the “update statistics tablename” command into 2 threads usual results in a time savings of approximately 45% to 50%. This is usually due to slightly uneven halfs more than the contention caused by running 2 threads.
Splitting the ‘update statistics tablename indexname’ into 2 threads ends up saving approximately 40% more than the ‘update statistics tablename’ approach. The total savings over the single threaded approach is 70% of the needed time. (My suspicion is that this cause an index scan over a table scan, but this is only a guess as the showplan only says “UPDATE STATISTICS”).
If you are running “update statistics”, you can add additional threads. If you are using “update index statistics”, you need to be careful on how many threads you run as statistics on “non leading” columns of indexes are generate by sorts in your tempdb.
Since you mentioned sampling, has anyone seen much benefit in using sampling? My tests have only resulted in approximately a 10% reduction, even when I am using the tricks mention above and sampling at 20%. I haven’t seen much benefit, but again, I don’t routinely run “update index statistics”.