1

For example, I have table A with 80 million rows and table B with 200 million rows.

Assume both tables have 30+ stat tables (all manually generated by SQL Server). I randomly take the first 10 stat tables from each and run the two tasks separately:

Update Statistics A (stat1, stat2, ...) with sample 5000000 rows
Update Statistics B (stat1, stat2, ...) with sample 5000000 rows

Even though both are scanning 5000000 rows only, the second task is taking much longer to complete. Is this difference in performance solely from the difference in the contents of the tables, or does the number of rows in the table also play a part even if we are limiting the number of rows to scan?

  • Is anything else accessing those tables while you update statistics? – Randolph West Oct 29 '18 at 22:36
  • As I see it, we have two contributing factors here: row size and what indexes you have. SQL Server will decide on the most narrow index that contain the column(s) in the statistics and then decide how many pages it need to sample in order to get to the number of rows you specified. I guess we could coma up with examples to show this, having two copies of the same table and the same statistics, one with narrow indexes to support the data it need and the other not. And also when not having indexes one could have two tables where one has a "filler" column to make row bigger. – Tibor Karaszi Oct 30 '18 at 7:40
0

Update statistics is a single threaded task (You can run multiple instances of it in parallell though) so duration of the statistics run will be correlated with the number of records the task has to scan in order to build the statistics and selecting the rows to sample.

Even if you are only sampling a fixed number of rows the population you are sampling from is larger therefore the scan will be more time consuming.

There are some interesting details about effects and use of tempdb in statistics scanning in Erin Stellato's post about statistics but almost all you want to know is contained in this Robert Sheldon's article

  • 1
    FWIW as of 2016 it can go parallell, and can be controlled using MAXDOP. – Tibor Karaszi Oct 30 '18 at 7:37
  • Even if you are only sampling a fixed number of rows the population you are sampling from is larger therefore the scan will be more time consuming.--Why, do you have more details on this? – SqlWorldWide Oct 30 '18 at 13:47
  • As stated in the BOL Create statistics "SAMPLE number { PERCENT | ROWS } Specifies the approximate percentage or number of rows in the table or indexed view for the query optimizer to use when it creates statistics. [...] The actual percentage or number of rows the query optimizer samples might not match the percentage or number specified. For example, the query optimizer scans all rows on a data page." For update the scan can will use tempdb to sort the rows to select see Erin's article or the BOL on update statistics – Spörri Oct 30 '18 at 16:08

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.