Disabling auto statistics in tempdb

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

Error: 602, Severity: 21, State: 30.
Could not find an entry for table or index with partition ID 489800837038080 in database 2. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.

followed by

Error: 3449, Severity: 21, State: 1.
SQL Server must shut down in order to recover a database (database ID 1). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.

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)

asked Mar 22 '10 at 07:06 AM in Default

Kev Riley ♦♦
46k ● 38 ● 43 ● 69

Ask Jeff Moden his opinion: http://www.sqlservercentral.com/Forums/Topic884950-338-2.aspx

Mar 23 '10 at 10:44 AM Blackhawk-17

(comments are locked)

1 answer:

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.

answered Mar 22 '10 at 07:42 AM