Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Auto Create/Update Statistics for TempDB Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 9:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 12, 2013 2:28 PM
Points: 12, Visits: 159
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?

Post #1433880
Posted Monday, March 25, 2013 3:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 3:28 PM
Points: 148, Visits: 287
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.
Post #1434790
Posted Monday, March 25, 2013 6:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1434864
Posted Monday, March 25, 2013 11:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 12, 2013 2:28 PM
Points: 12, Visits: 159
I concur.. We should leave them ON unless we can prove that turning OFF will improve general performance...
Thanks for sharing your thoughts..
Post #1435073
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse