March 21, 2013 at 9:51 am
I'm getting inconsitent information on this..
By default these two options are ON, and for the best practice of TempDb, some say these should be turned OFF, but others say leave them ON..
My server is handling reporting (which uses loarge temp tables) and OLTP...
Any thoughts?
March 25, 2013 at 3:33 am
As with most things in SQL Server, it's more of an art than a science.
Simple answer is, "it depends".
In most cases, where tempdb is handling relatively small objects where statistics aren't necessarily needed, then it makes sense to turn it off, as you can see a performance improvement, but from the info you mentioned above, I suspect you're going to need to leave them on.
An option in this scenario though, to help improve performance (if you have the hardware capacity to support it), is to use tempdb in RAM;
http://support.microsoft.com/kb/115050
You'll probably need some trial and error to see if some or all reports improve using both/either the tempdb in ram and auto-update/create stats, and to see whether any are impacted adversely.
March 25, 2013 at 6:21 am
Honestly, I'd leave them both on unless you have a bloody good reason to turn them off and you have code that creates stats on every temp table that you create and use or unless your temp tables only every have less than about 100 rows in them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 25, 2013 at 11:28 am
I concur.. We should leave them ON unless we can prove that turning OFF will improve general performance...
Thanks for sharing your thoughts..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply