Never turn off "Auto create & Auto Update Statistics"

As you all know, in SQL Server (any version actually) you have the options "Auto Create Statistics" and "Auto Update Statistics". By default this is enabled (these parameters are set to true on the model database and thus to all newly create databases).

So, what happened?

At a customer they reinstalled their farm with the purpose of starting on a clean server environment when upgrading to SP1 and the Infrastructure Update. So basically they had setup a new farm (with all the necessary updates) and they moved all the databases to that farm, "migrating" them to the latest build versions. They did it like this, so the old farm was never to be touched, providing a perfect fall-back scenario.

We (the customer and myself) did this in staging environment some time ago, and thus making the correct procedures. The customer would do this in production themselves (so without my assistance). In staging environment (of course) everything worked perfectly.

And then, they did it in the production environment.

Everything worked well, all the sites were up and running, ...
... but then they started a full crawl of the content.

A full crawl of a small database of about 3 GB, took 5 hours!!!
A full crawl of a large database of about 60 GB was running for 25 hours and then freezed!!!

In the meanwhile, crawling the same content on the staging environment did not have any problems at all...

So, what was the problem?


As it turned out, the model database of the SQL Server where the databases were moved at, had "Auto Create Statistics" and "Auto Update Statistics" turned off. Since the farm was reinstalled, only restoring the content databases and the SSP database, a new SSP Search database was created. Thus taking over the settings of the model database.

Hence, the "Auto Create Statistics" and "Auto Update Statistics" parameters of the SSP Search database were turned off. This causing performance problems on that database (SQL Server CPU went 100 % when crawling), and eventually freezing... (Actually, the crawl started good, went slower, slower, slower, dead...)

When we turned these properties back to "true", indexing worked again.

The 5 hour crawl of the small database only took 12 minutes.
The 25 hour crawl (+ freeze) took only 4 (and a bit) hours.


So, conclusion:

For performance for filling up the Search database (what happens when crawling), SQL Server really relies on the database statistics. Of course, when no statistics are being created or updated, then you're in a whole lot of trouble.

And now for some shouting the lesson of today:

NEVER EVER TURN AUTO CREATE AND AUTO UPDATE STATISTICS OFF!
At least not for your SharePoint databases and especially not for your Shared Services Search database.

btw: this is a default setting, so normally you shouldn't have a problem with this. You never know of course Wink


Keep on partying
Tom

Published woensdag 22 april 2009 17:00 by Tom Vandaele

Comments

# http://www.webanywhere.it/scriptcase/info.php?a%5B%5D=%3Ca+href%3Dhttp%3A%2F%2Fwww.socialcheats.com%2Fjetpack-joyride-cheats%2F%3Eusing+the+jetpack+joyride+cheats%3C%2Fa%3E

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# Racing Rivals Cheats

dinsdag 16 september 2014 9:29 by Racing Rivals Cheats

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# http://pleskwin16.hospedagemdesites.ws/test/php/php_info.php?a%5B%5D=%3Ca+href%3Dhttp%3A%2F%2Fconfidencegames.com%2Fracing-rivals-hack%2F%3EHack+Racing+Rivals%3C%2Fa%3E

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# http://unix-ipo.testdomain.be/?a%5B%5D=%3Ca+href%3Dhttp%3A%2F%2Fconfidencegames.com%2Fclash-of-clans-hack%2F%3Ehttp%3A%2F%2Fconfidencegames.com%2Fclash-of-clans-hack%2F%3C%2Fa%3E

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# learn more

zaterdag 4 oktober 2014 1:25 by learn more

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# additional resources

zaterdag 4 oktober 2014 14:42 by additional resources

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# have a peek at these guys

zondag 5 oktober 2014 4:38 by have a peek at these guys

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# http://kino-ilmenau.de/info.php/RS=ADAuwoAdd1pKJMm8Fx1?a%5B%5D=%3Ca+href%3Dhttp%3A%2F%2Fwww.socialcheats.com%2F8-ball-pool-money-hack%2F%3Ehttp%3A%2F%2Fwww.socialcheats.com%2F8-ball-pool-money-hack%2F%3C%2Fa%3E

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# Xbox Live free codes

maandag 6 oktober 2014 6:16 by Xbox Live free codes

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# Dota 2 Cheats

maandag 6 oktober 2014 10:32 by Dota 2 Cheats

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# look at here now

dinsdag 7 oktober 2014 6:58 by look at here now

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# Hack Boom Beach

woensdag 8 oktober 2014 11:48 by Hack Boom Beach

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# http://www.ch-carpentras.fr/test.php?a%5B%5D=%3Ca+href%3Dhttp%3A%2F%2Fwww.socialcheats.com%2Fsubway-surfers-cheats%2F%3Ehttp%3A%2F%2Fwww.socialcheats.com%2Fsubway-surfers-cheats%2F%3C%2Fa%3E

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# free netflix accounts

woensdag 8 oktober 2014 16:50 by free netflix accounts

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# http://smado.de/scripts/phpinfo.php?a%5B%5D=get+clash+of+clans+hack+no+download%2C+%3Ca+href%3Dhttp%3A%2F%2Fwww.socialcheats.com%2Fclash-of-clans-hack%2F%3Ewhy+not+try+these+out%3C%2Fa%3E%2C

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# apple watch screen protectors

donderdag 9 oktober 2014 22:36 by apple watch screen protectors

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# générateur de crédits fifa 15

vrijdag 10 oktober 2014 1:33 by générateur de crédits fifa 15

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# apple watches

zondag 12 oktober 2014 23:14 by apple watches

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# generateur credit fifa 15

maandag 13 oktober 2014 13:01 by generateur credit fifa 15

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# buy apple watch cases

dinsdag 14 oktober 2014 15:10 by buy apple watch cases

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# apple watch screen protectors

donderdag 16 oktober 2014 6:49 by apple watch screen protectors

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# buy apple watch screen protectors

donderdag 16 oktober 2014 8:04 by buy apple watch screen protectors

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# apple watch

donderdag 16 oktober 2014 12:04 by apple watch

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# apple watches

donderdag 16 oktober 2014 13:42 by apple watches

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

# badge aide soignante

zondag 16 november 2014 22:43 by badge aide soignante

Never turn off "Auto create & Auto Update Statistics" - SharePoint Stuff

Powered by Community Server (Commercial Edition), by Telligent Systems