Conor vs. Stats NORECOMPUTE

  • Article

It’s been a busy summer.  I got back from vacation and have been hard at work on new features for a future version of SQL Server.  You can always still send me questions about the existing product, of course, and I will answer them here (conorc (at) microsoft (dot) com).

 

Today is a question that one of the MVPs asked – How do I find out if a statistics object was created with the “don’t recompute” flag?  Initially, I tried dbcc show_statistics, but it isn’t stored within the statistics blob itself and doesn’t show up there.  Instead, you need to look at sys.stats (2005 and above):

 

 

    1:  use tempdb

    2:  go

    3:  create table t1(col1 int)

    4:  insert into t1(col1) values (1)

    5:  insert into t1(col1) values (2)

    6:  go

    7:  create statistics s1 on t1(col1)

    8:  create statistics s2 on t1(col1) with norecompute

    9:  go

 

Which yields:

 name        no_recompute
------------------------
s1          0
s2          1

(2 row(s) affected)

Funnily enough, the internal build on which I first tried this had a bug and did not ever return 1… It had already been fixed, but I had not installed that build on my machine.  I spent an hour trying to grok the metadata code and figure out what had gone wrong :).

 

Now, the auto-stats logic in SQL Server is generally good for the vast majority of our customers.  It updates statistics when the data has changed, causing plans referencing those statistics to recompile when they are next used.  This works great – so, why would I not want that??? 

 

Well, there could be a few cases where you want to override the default behavior of the system.  Perhaps you find that your application is heavily dependent on avoiding too many recompiles in the middle of the day (recompiles take CPU resources).  If you wanted all statistics to not recompute, you can just change the database-level flag for auto-update stats.  However, if you want to change only specific statistics, this norecompute flag is the way to do so.  Usually, I would only recommend doing something like this if you know what you are doing and have a specific reason to change this, as you would want to pair something like norecompute with your own custome stats update job that runs at night, for example.

 

I hope that gives you some insight into why this flag is there.

 

Happy Querying!

 

Conor