But what happens if you compress your tables, specifically the clustered index of your big tables? The theory is that since the table/index will stay compressed in memory as well, more of the table should fit there at one, dramatically speeding up the process described in the previous paragraph. Obviously this is a slow and tedious process.

But since the table does not fit in memory, when the time comes to scan the table to find information about the second column, the table have to be read from disk again. So if you for example have statistics on 10 columns (index statistics aside), SQL Server will scan the table for the first column (from disk mostly), updating the statistics. And what seems to happen when you rebuild statistics on a table is that the statistics are rebuild one by one. The problem escalates when your tables are so big that they do not fit in the cache. And usually, you tend to be IO bound in this scenario. It is a common scenario today to have a SQL Server cluster with plenty of CPU available, on top of SAN disks. So too in this case. I am talking about how to optimize UPDATE STATISTICS performance by utilizing the SQL Server 2008 R2 Enterprise Edition Compression feature. Recently while working on a TB size SQL Server database, I got the opportunity to verify something that I have suspected for some time, but not yet looked into properly.

Update statistics sql server