I got this in email a couple of weeks ago and thought I would share. Figured it would help a few people searching the web for troubleshooting if nothing else.

<email>

Hi Shane,

We are having a peculiar problem with our SharePoint Farm, that we are unable to resolve, we currently have a case logged with Microsoft, but after 3 weeks of no progress we are trying to find any assistance we can. If you have a few minutes would you be able to have a quick read of our problem and offer us any advice you may have?

We have 2 SharePoint Farms, one for production and one for Testing. Both are configured the same, with the exception of content, the test environment does not have the same content as the production.

Both farms are configured with one server for SharePoint services and a server for SQL Server.

Ok so here is the problem, when running a full crawl the CPU usage of the sqlsvr.exe process on the SQL server hits 100% and stays there.

*Stopping the search service on the SharePoint server results in an immediate drop in CPU usage on SQL. Restarting the service results in an immediate spike in CPU on SQL *Several times the search service has failed to stop and required end tasking the search process from task manager *The crawl appears to run without problem with low CPU usage on SQL and high CPU on SharePoint until it has crawled 3000+ items then the CPU on SharePoint drops and SQL CPU maxes out.

*Resetting the crawled content results in a drop in CPU on SQL. This process has occasionally hung as well requiring the restarting and end tasking of the search service.

*Symptoms are similar to this issue http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2364387&SiteID=17 with the following exceptions:

*There are no SQL errors logged

*And the authoritative pages refresh does not work.

*This problem does not occur in our test environment.

*I have setup our test SharePoint Server to crawl the content on production SharePoint server and it successfully completed the crawl without any problems.

*I have also tried separating the content sources and crawling each one separately. Any one of them causes the CPU spike *I have uninstalled and reinstalled the search service on SharePoint.

*Both our SharePoint Servers have the latest service pack for SharePoint installed already (I didn’t think it did, but I tried to install it and it said it was running the latest service pack already).

I know there is a service pack for SQL server available http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&DisplayLang=en but our test SQL server is running the same version as our production server and is not experiencing the problem, even when I used the test server to crawl the production server.

Many thanks for your time, and look forward to your reply.

</email>

I guessed it was his SQL Database Index as we discussed previously here. http://msmvps.com/blogs/shane/archive/2007/07/23/another-day-another-new-error-message.aspx

It was. 🙂

Shane – SharePoint Consulting