Auto create statistics and Auto update statistics

Question

Auto create statistics and Auto update statistics

AAkula

SSCertifiable

Points: 5394
More actions
January 8, 2009 at 3:13 pm

#126350

Hello,
Autocreate statistics and Auto update statistics or set to False in database properties->Options page for 2 databases in our one of the sql server 2005 instances and for the rest of the databases these two options are set to True.
I did not change any configuration settings after the installation,But I did not get why for these two databases autocreate statistics and auto update statistics are turned off.
Finally, should we set these two options to TRUE or FALSE?
plz suggest me
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lilly-440557 Old Hand Points: 397 More actions · Answer 1

kotlas7 (1/8/2009)
Hello,
Autocreate statistics and Auto update statistics or set to False in database properties->Options page for 2 databases in our one of the sql server 2005 instances and for the rest of the databases these two options are set to True.
I did not change any configuration settings after the installation,But I did not get why for these two databases autocreate statistics and auto update statistics are turned off.
Finally, should we set these two options to TRUE or FALSE?
plz suggest me
Thanks

By default in SQL Server 2005, those 2 settings should be set to true to help with performance. Infact, you may also need to update statistics even with those 2 settings = true based on the activity of your DB.

GSquared SSC Guru Points: 260824 More actions · Answer 2

They should almost always be set to "on". I can't think of a specific reason to have them off, but it might be required for some obscure problem.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

Better to leave it ON.

http://www.sqlskills.com/blogs/kimberly/post/Auto-update-statistics-and-auto-create-statistics-should-you-leave-them-on-andor-turn-them-on.aspx

MJ

AAkula SSCertifiable Points: 5394 More actions · Answer 4

Thanks,

I got it.. for Biztalk server databases in sql server these 2 otions should be OFF by default..

From BOL:

Auto Update Statistics/Auto Create Statistics:

The Auto Create Statistics and the Auto Update Statistics options should be disabled on the BizTalk Server Messagebox database. To determine if these settings are disabled, execute the following stored procedures in SQL Server: