MSSQL statistics steps and tempdb usage

Question

Can we control the number of steps during update stats ( max should be 200 ) ?

how to estimate the the tempdb usage during update stats?

??

Answer 1

Can we control the number of steps during update stats ( max should be 200 ) ?

No. SQL Server decides which number of steps it thinks are needed. This number can be very low. I looked at the statistics for the id column of a table, where the ids are perfectly contiguous. The statistics were updated with FULLSCAN. There were only two steps. And when you think of it, that is all that is needed.

how to estimate the the tempdb usage during update stats?

When I first saw this question, I was not sure that statistics update would even require space in tempdb, but the link to Kendra Little's blog post that Dirk provided gave me a clue. Here is a command from Kendra's post:

SELECT StatMan([SC0]) FROM 
(SELECT TOP 100 PERCENT [Title] AS [SC0] 
FROM [Person].[Person] WITH (READUNCOMMITTED)  
WHERE ([title]='Mr.') ORDER BY [SC0] ) 
AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 16)

I have seen these SELECT StatMan commands many times, but I have not paid attention to the ORDER BY clause. If this is statistics for a leading column in an index, there is no problem as data is already sorted. But for other columns, this means that this query needs a Sort operator. That operator should get a memory grant that permits the sort operation to take place in memory. But the memory grant is based on estimates (from the old statistics!), and may be too low, which leads to the sort operation spilling to disk.

Try to predict how big that spill will be ...sounds difficult.

Answer 2

Hmm,

question here is: how do you run statistics updates now? Are FULLSCANs needed?

I'd say: take a closer look if you need e.g. fullscans every time ore not
https://www.brentozar.com/archive/2014/01/update-statistics-the-secret-io-explosion/

The number of steps is quite difficult, since you may need to examine what stats are available for a table and update eacht stat individually.

Take a look at the maintenance solution provided by Ola Hallengren
Here you can at least tell the maintenance procs to run only on specific databases or even specific tables.
That's a way of controlling the statistic updates.

Also keep in mind: you do not need to update the stats for your indices if those where rebuild before.