Question :
It has come to my attention that a number of my databases (2008 & 2012) do not have the setting AUTO_CREATE_STATISTICS set to ON for the tempDB.
In the Microsoft documentation it is set to ON as default.
Does turning the setting off have any performance enhancements/reductions?
What are the implications of turning this OFF?
Will any temporary tables or internal objects be suffering?
Thanks
Answer :
http://searchsqlserver.techtarget.com/tip/SQL-Server-tempdb-best-practices-increase-performance
You can further increase tempdb performance by disabling the auto update stats, which will save your tempdb some work. Usually objects created in the tempdb are fairly small and, as such, the statistics will not reach the threshold that causes the statistics to update automatically. By disabling the setting, you stop SQL Server from having to check to see if it needs to update the statistics. However, you’ll need to manually update statistics if you occasionally use large temporary objects.
You can also set the auto create statistics option to false. That, too, will increase tempdb performance. By disabling the setting, you stop SQL Server from having to produce the statistics when the object is first created.
Settings should be changed with care. Depending on the kind of load you place on your tempdb, changing settings could adversely impact system performance. You should test the settings in a non-production environment before they are deployed to the production environment. After deployment, monitor these changes closely to ensure the system is running at peak performance.