Sterling International Consulting Group

SharePoint Search and Why Does It Tap Out the CPU at 100%?

Email | Print

In many installations, I’ve found that for some content databases, the SharePoint Search (not FAST – that’s another animal all together) can seem to take an exceptional amount of time to crawl. The causes can be widespread – from errors in the crawled content to broken links.

For example, we had one database that is about 70 GB of data. Though fully crawled weekly, the overall time to complete a full crawl was some 12 hours. Incremental crawls took 3 to 4 hours. In addition, whenever this crawl started (full or incremental), CPU would hit 100% and stay there – not only causing performance issues on the site but also as a side effect, it kept tripping the SQL Mirror fail-over.

Suspecting a number of issues, we did the following to resolve the problems:

First, isolate the content sources so that you can figure out which content database is tapping out when it starts. This will at least help you to narrow down exactly where the problem is (i.e. separating content vs. people search, etc.). 

Next, check the errors generated by the search – broken links within the index can lead to this problem. A good way to correct this is to determine WHY the links are broken and try to correct the root cause (permissions, bad URL’s, deleted site, etc.). Sometimes simply dropping the indexes and rebuilding them will correct the issue – while in our example, this corrected the errors but not the CPU 100% problem.

Next, if you have more than one server available, try changing the server roles to isolate the crawl – this may eliminate the problem or at least alleviate it enough to work on the real cause.

Last, check the site for Closed Web Parts – these are parts that end users have closed on pages vs. deleting them. 

In our case,  the last item was the golden ticket. Using a quick PowerShell script, we were able to determine that in this particular site, there were over 651 closed web parts. Since this certainly would be impossible to correct manually, the PowerShell script can be used to delete the parts found. Now this is not without risk – if users intended to close web parts (not sure why but…) deleting them will cause them problems. In our particular case, the end users are not sophisticated enough to be using closed web parts so the risk was minimal. 

NOTE: You should also check for “bad web parts” – in Central Administration, you can see this under the “Missing Assemblies” message in the Health Analyzer. You can also use the PowerShell script to test the database itself:

Test-SPContentDatabase -Name <Content DB Name> -WebApplication http://<your URL>

The above will provide a list of missing web parts and other errors if detected.

For the Closed Web Parts, here’s a step by step to fix it:

  1. Run the following PowerShell script to generate a CSV file that lists all of the closed parts
  2. If you have a development environment:
    1. Restore the database to a new application using the database attach method (the same as if converting).
    2. Setup the content database for search and verify if the problem still occurs (not necessarily CPU but in time to crawl).
    3. Run the script to delete the closed parts; afterwards run an IISReset.
    4. Drop the Search indexes and run a full crawl as an isolated content source (crawling ONLY the single content DB).
    5. Compare times!
  3. Once tested in Development or directly in production (if you don’t have a dev you can do that with)
    1. Make a full backup of the content database.
    2. Run the script to generate the CSV file – verify the number of items found.
    3. Log off all users if possible (not 100% needed but good idea).
    4. Run the script to delete the parts.
    5. Drop the Search indexes and start a full crawl on the content database – monitor the CPU/time.
    6. When complete, re-run an incremental crawl and again monitor the CPU/time.

This MAY NOT fix the entire problem for you, but it will also not hurt – the potential downside is that it simply takes as long as usual.

In our case however, the results were DRAMATIC:

Before:

  • Content DB Full Crawl: 12-13 hours
  • Content DB Incremental Crawl: 3-4 hours
  • CPU Usage: 80-100% with sustained peaks
  • Closed Web Part Count: 651

After:

  • Content DB Full Crawl: 1 hour 20 minutes
  • Content DB Incremental Crawl: 2 minutes 40 seconds avg
  • CPU Usage: 60-100% with NO sustained peaks
  • Closed Web Part Count: 0

NOTE: This script was modified from another found; sorry our research did not record the original author.

SCRIPT TO LIST CLOSED WEB PARTS ON ALL PAGES:

# Write the Header line to a new CSV File (this drops it in the current directory):

#

“Page URL,  Closed Web Part Name” | out-file ClosedWebParts.csv

 

# Get all Webs from the Site:

#

$webs = Get-SPWebApplication “http://<your URL>” | Get-SPSite -Limit All | Get-SPWeb -Limit All

 

# Loop through each of the Web Sites found (note: you MUST be SCA when running this!)

#

foreach ($web in $webs)

{

# Get All Pages from site’s Root into $AllPages Array

#

$AllPages = @($web.Files | Where-Object {$_.Name -match “.aspx”})

 

# Search All Folders for All Pages

#

foreach ($folder in $web.Folders)

{

#Add the pages to $AllPages Array

$AllPages += @($folder.Files | Where-Object {$_.Name -match “.aspx”})

}

 

# Loop through all of the pages and check each:

#

foreach($Page in $AllPages)

{

$webPartManager = $web.GetLimitedWebPartManager($Page.ServerRelativeUrl,[System.Web.UI.WebControls.WebParts.PersonalizationScope]::Shared)

 

# Use an array to hold a list of the closed web parts:

#

$closedWebParts = @()

foreach ($webPart in $webPartManager.WebParts | Where-Object {$_.IsClosed})

{

$result = “$($web.site.Url)$($Page.ServerRelativeUrl), $($webpart.Title)”

Write-Host “Closed Web Part(s) Found at URL: “$result

$result | Out-File ClosedWebParts.csv -Append

$closedWebParts += $webPart

}

}

}

 

SCRIPT TO DELETE CLOSED WEB PARTS ON ALL PAGES:

 

# Write the Header line to a new CSV File (this drops it in the current directory):

#

“Page URL,  Closed Web Part Name” | out-file ClosedWebParts.csv

 

# Get all Webs from the Site:

#

$webs = Get-SPWebApplication “http://<your URL>” | Get-SPSite -Limit All | Get-SPWeb -Limit All

 

# Loop through each of the Web Sites found (note: you MUST be SCA when running this!)

#

foreach ($web in $webs)

{

# Get All Pages from site’s Root into $AllPages Array

#

$AllPages = @($web.Files | Where-Object {$_.Name -match “.aspx”})

 

# Search All Folders for All Pages

#

foreach ($folder in $web.Folders)

{

#Add the pages to $AllPages Array

$AllPages += @($folder.Files | Where-Object {$_.Name -match “.aspx”})

}

 

# Loop through all of the pages and check each:

#

foreach($Page in $AllPages)

{

$webPartManager = $web.GetLimitedWebPartManager($Page.ServerRelativeUrl,[System.Web.UI.WebControls.WebParts.PersonalizationScope]::Shared)

 

# Use an array to hold a list of the closed web parts:

#

$closedWebParts = @()

foreach ($webPart in $webPartManager.WebParts | Where-Object {$_.IsClosed})

{

$result = “$($web.site.Url)$($Page.ServerRelativeUrl), $($webpart.Title)”

Write-Host “Closed Web Part(s) Found at URL: “$result

$result | Out-File ClosedWebParts.csv -Append

$closedWebParts += $webPart

}

# Delete Closed Web Parts

#

foreach ($webPart in $closedWebParts)

{

Write-Host “Deleting ‘$($webPart.Title)’ on $($web.site.Url)/$($page.Url)”

$webPartManager.DeleteWebPart($webPart)                 

}

}

}

by Sterling International Consulting Group http://www.sterling-consulting.com

Related Posts



3 questions

  1. Sharepoin says:

    Hi All,

    we have a 1 App server , 2 wef servers and sql server.

    we have only one web application in SP2010 (SP2010.eu.com) in that 29 Site Collections.

    The issue is:

    when the search (full/inc)crawl is running the share point server database CPU Utilization is spikes to 100%.

    So we contacted with SQL Team, they given this information (The Problem with only one SC content database(WSS_content_HR), it is taking more CPU Usage)

    SQL Team had killed the all the select statements in that DB.then CPU Utilization came to normal.

    After 5 Min it’s again reached to 100%.

    we did stopped the all the timer jobs but it’s not came back.

    then Yesterday we did paused the search crawl then it came to normal.

    If we exclude search crawl on that Site collection CPU Utilization is going to normal.

    the problem with only one site collection

    could you please help me anyone.

    [Reply]

  2. Sorry for the long delay but for someone else, this is usually caused by problems with the Content Access account – typically not having the proper permissions to the Database and/or Content.

    [Reply]

  3. Pat says:

    This may be an issue with my SP 2013 crawl. We have 750+ closed web parts across our intranet. But when I run the script to delete the web parts on our staging farm, I get errors that I do not have pages checked out. I’ll have a look at updating the script to do so. Not sure I want to run this against production though..

    [Reply]

Ask This Expert a Question or Leave a Comment