SQL-Network Performance problems when orphan solutions are still present in the database

  • Article

Consider the following scenario:

You have deployed a number of solutions on your Sharepoint Site. During the time, you deactivate the solutions, delete them, add new ones, and so on. For unknown reasons, some of the solutions still remain in the database but they do not get displayed in the user interface ( what we call orphans )

What you can notice is , from time to time , the SQL server is sluggish to respond to Sharepoint requests.

Monitoring the network traffic you notice an increase in the number of bytes transferred between Sharepoint and SQL.

Why ?

The explanation lies within the caching mechanism.

When a page is requested, if the objects dependent are not in the Object cache, we will go to the database and retrieve these objects. Among those are the solutions deployed within the site.

This is done through the execution of the stored procedure proc_GetSolutionsData that will retrieve all sandboxed solutions in the sitecollection.

if the size of the solutions is considerable ( if you saved sites as template with content for example, the limit is set to 50 MB https://office.microsoft.com/en-us/sharepoint-designer-help/save-a-sharepoint-site-as-a-template-HA101782501.aspx) the data will be transferred over the network for each request and, depending on the number of requests can potentially impact the network interface on the SQL server ( easiest way to observe this is through a full crawl).

How to check?

Go to site settings->Solutions Gallery

Check the number of solutions displayed

Open Sharepoint Administrative PowerShell

execute

$site = Get-SPSite https://yoursite

$site.Solutions

you will receive a list of the sandboxed solutions , check if the results from Powershell match the results in UI. If the results are different ( i.e. you receive more results in PowerShell it means there are probably orphans),so you can remove them through PowerShell

$sol = $site.Solutions["GUID_OF_ORPHAN_SOLUTION"]

$site.Solutions.Remove($sol)