Had an issue over the weekend (why is it always when I'm not in the office!) where one of my production servers stopped.
On reviewing the logs I had multiple
This lead me to this KB article (916086), which describes the issue when you have a lot of turnover in tempdb.
Upshot is, there is a hotfix to download and patch, I'm reviewing when and if I should do this, however, one of the workarounds is to disable the Auto Create Statistics option and the Auto Update Statistics option in the tempdb database.
My question is this :- These options are enabled on tempdb by default, so what impact will I see on my server if tempdb doesn't have these options on? Would I see performance decrease (I know it depends!) - I know that might be favourable to having the server stop dead, but I would imagine it takes a while to get to this point (server was due a reboot soon)
Disabling stats in tempdb isnt recommended, but as you have already mentioned - it depends!
This should be done with caution as depending on your environment it could have performance issues. In our case most of the objects created in tempdb are relatively small and don’t always require stats to be created, so turning off "auto-stats" wasn’t an issue.
You will have to keep in mind that if your objects are large by nature would need manual updates to stats.
When looking at such changes I would advise to test in a staging environment first. I know it may be difficult to test the exact load, but in my case I tested some reasonably large queries I knew would hit tempdb. In most cases I had a 2-5 second increase in performance has tempdb didn’t have to compile the stats.
The above system is of small load therefore changing this wasn't an issue, but our high volume prod system hasn't been changed due to the hits it has on tempdb and me been cautious!
Hope this helps.