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.
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
Keep on partying