x

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)

more ▼

asked Mar 22, 2010 at 07:06 AM in Default

Kev Riley gravatar image

Kev Riley ♦♦
54k 47 49 76

Mar 23, 2010 at 10:44 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Mar 22, 2010 at 07:42 AM

sp_lock gravatar image

sp_lock
9.3k 25 28 31

???Disabling stats in tempdb is recommended,???

Could I see the Microsoft document that makes that recommendation, please?
Apr 10, 2010 at 03:47 PM Jeff Moden
Jeff... My apologies... Typo... With edit now!
Apr 10, 2010 at 05:28 PM sp_lock
Heh... makes all the difference in the world. Thanks for the edit, Jonlee.
Apr 10, 2010 at 11:32 PM Jeff Moden
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x50
x34

asked: Mar 22, 2010 at 07:06 AM

Seen: 3512 times

Last Updated: Mar 22, 2010 at 07:31 AM