SharePoint Health Analyzer rules reference SharePoint 2013

Advertisements

Windows server Task scheduler Monitoring event id 111

The history of a task is tracked by events. These events can be viewed in Task Scheduler for each task to track when the task was registered, run, and when it completed or failed. The progress of a task can be monitored through its history. A task can be controlled by running or stopping the task manually (on-demand).

Event Details

Product:  Windows Operating System
ID:  111
Source:  Microsoft-Windows-TaskScheduler
Version:  6.1
Symbolic Name:  JOB_TERMINATION
Message:  Task Scheduler terminated the “%2” instance of the “%1” task due to exceeding the time allocated for execution, as configured in the task definition. Increase the configured task timeout or investigate external reasons for the delay.

Resolve

Fix task configuration settings

The task was stopped due to a configured setting. Possible causes include:
•The task ran for longer than the maximum configured run time.
•The task was configured to stop when the computer switched to battery power.
•The task was configured to stop when the computer is no longer idle.
•The task was configured to stop when a new instance of the task is triggered.

This behavior might be as expected. However, if the behavior was unexpected you can reconfigure the task configuration settings.

To update the task settings and conditions:

  1. Click the Start button and type Task Scheduler in the Start Search box.
  2. Select the Task Scheduler program to start Task Scheduler.
  3. Select the task to configure by locating the task in the task folder hierarchy. Right-click the task, and select Properties.
  4. On the Settings and Conditions tabs, update the task settings and conditions.
  5. Click OK.

Verify

To verify that the execution of a task has completed as expected:

  1. Click the Start button and type Task Scheduler in the Start Search box.
  2. Select the Task Scheduler program to start Task Scheduler.
  3. Select the task to run by locating the task in the task folder hierarchy.
  4. On the Actions menu click Run. You can also click Run in the Actions pane.
  5. Click the History tab for the task to verify that it contains events indicating the task was registered successfully. Also, ensure that the task completed successfully or that the task timed out as expected.

Windows server Task scheduler Monitoring event id 102

The history of a task is tracked by events. These events can be viewed in Task Scheduler for each task to track when the task was registered, run, and when it completed or failed. The progress of a task can be monitored through its history. A task can be controlled by running or stopping the task manually (on-demand).

Event Details :

Product:               Windows Operating System
ID:                           102
Source:                  Microsoft-Windows-TaskScheduler
Version:                 6.1
Symbolic Name:  JOB_SUCCESS
Message:               Task Scheduler successfully finished the “%3” instance of the “%1” task for user “%2”.

Resolve :

This is a normal condition. No further action is required.

 

Slow SharePoint improve performance without upgrading hardware

what you can do if your SharePoint is sometimes very slow.

E.g.: on the first start of a Site
Sometimes during the day a search query will take about a minute until you get results…..

Just look on that article: http://support.microsoft.com/kb/2625048

it will improve “feeled” performance (site response times) massive, if you’re going to implement both solutions.

Disabling CRL Check is just necessary if the SP Server does not have internet connectivity, that means proxy settings must be configured for the server itself

http://technet.microsoft.com/de-de/library/bb430772(v=exchg.141).aspx, and your proxy must allow traffic from the server of course.

Delete cbs.log file its growing large ?

System File Checker is a utility in Windows that allows users to scan for corruptions in Windows system files and restore corrupted files.Run the System File Checker tool (SFC.exe) to scan your system files and to repair missing or corrupted system files.CBS.Log file is generated by the Microsoft Windows Resource Checker (SFC.exe).

The SFC.exe program writes the details of each verification operation and of each repair operation to the CBS.log file. The CBS.persist.log is generated when the CBS gets to be around 50 meg in size. CBS.log is copied to cbs.persist.log and a new cbs.log file is started.

Solution 1 :

you can try compressing the file:

1.Right click on the CBS.log file
2.Click on Properties
3. On the General tab, click Advanced
4. Check “Compress contents to save disk space” and click on OK

Solution 2 :

Another Workaround is to turn off unnecessary services that may calling into CBS;

To disable CBS log, you may change registry key in

[HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Component
Based Servicing] EnableLog=dword:00000000

In addition,also recommended to run sfc/scannow to check the health of system files.

Solution 3 :

If you are sure your system is running fine, you can delete this file. SFC.exe will create a new one, next time it is run.

Please follow below method to delete:

  1. Stop TrustedInstaller.exe (Windows Module Installer) in Taskmanager Services tab. Additionally if you cannot, open the Taskmanager Processes tab and End Task the same process.

  2. Delete or move all the .log files in the C:\Windows\Logs\CBS directory, you can delete the .persist and .cab files as well.

  3. Start Windows Module Installer service (TrustedInstaller.exe) again

search diagnostics and reports sharepoint

We can access and analyze several query and crawl health reports, logs and usage reports from the Search service application in the SharePoint Central Administration to monitor the health of the search system.

The health reports and logs only contain information after a full crawl has completed. To run a full crawl, we have to set up a Search service application, add at least one content source, and then start a full crawl.

To view the health reports and the crawl log, one have to be an administrator of the Search service application. Alternatively, an administrator who is a member of the Farm Administrators group can grant user accounts Read permissions on the Search service application. A user account that has Read permissions can only view the Search service application status page, the health reports and the crawl log.

Query health reports:

  1. Trend
  2. Overall
  3. Main Flow
  4. Federation
  5. SharePoint Search Provider
  6. People Search Provider
  7. Index Engine

To view query health reports:

  1. Verify that the user account that is performing this procedure is an administrator of or has Read permissions to the Search service application.
  2. In Central Administration, under Application Management, click Manage service applications.
  3. On the Service Applications page, click the Search service application.
  4. On the Search Administration page, in the Quick Launch, in the Diagnostics section, click Query Health Reports.
  5. On the Search Service Application: Query Latency Trend page, click the query report that you want to view.

The following table shows which reports are available.

query-health-report

Crawl health reports:

SharePoint 2013 provides the following reports about crawl health:

  1. Crawl Rate
  2. Crawl Latency
  3. Crawl Queue
  4. Crawl Freshness
  5. Content Processing Activity
  6. CPU and Memory Load
  7. Continuous Crawl

To view crawl health reports

  1. Verify that the user account that is performing this procedure is an administrator of or has Read permissions to the Search service application.
  2. In Central Administration, under Application Management, click Manage service applications.
  3. On the Service Applications page, click the Search service application.
  4. On the Search Administration page, in the Quick Launch, in the Diagnostics section, click Crawl Health Reports.
  5. On the Search Service Application: Crawl Reports page, click the crawl health report that you want to view.

The following table shows which reports are available.

crawl-health-report

Crawl log:

The crawl log tracks information about the status of crawled content. This log lets you determine whether crawled content was successfully added to the index, whether it was excluded because of a crawl rule, or whether indexing failed because of an error. The crawl log also contains information such as the time of the last successful crawl and whether any crawl rules were applied. You can use the crawl log to diagnose problems with the search experience.

To view the crawl log

  1. Verify that the user account that is performing this procedure is an administrator of the Search service application, or has Read permissions to it.
  2. In Central Administration, under Application Management, click Manage service applications.
  3. On the Service Applications page, click the Search service application.
  4. On the Search Administration page, in the Quick Launch, in the Diagnostics section, click Crawl Log.
  5. On the Crawl Log – Content Source page, click the view that you want.

crawl-log-views

Additional columns in the Content Source, Host Name and Crawl History views:

content-source-host-name-crawl-history-view

Usage reports (search report):

To view usage reports

  1. Verify that the user account that is performing this procedure is an administrator of or has Read permissions to the Search service application.
  2. In Central Administration, under Application Management, click Manage service applications.
  3. On the Service Applications page, click the Search service application.
  4. On the Search Administration page, in the Quick Launch, in the Diagnostics section, click Usage Reports.
  5. On the View Usage Reports page, click the usage or search reports view that you want view.

usage-report-search-report

 

Configure diagnostic logging SharePoint 2016

The SharePoint Server 2016 environment might require configuration of the diagnostic logging settings after initial deployment, after upgrade, and if a change is made to the environment, such as adding or removing a server.

The guidelines in the following list can help you form best practices for the specific environment.

* Change the drive to which the server writes logs:

By default, SharePoint Server 2016 writes diagnostic logs to the same drive and partition on which it was installed. Because diagnostic logging can use a large amount of drive space and compromise drive performance, you should configure SharePoint Server 2016 to write to another drive on which SharePoint Server 2016 is not installed.

You should also consider the connection speed to the drive on which SharePoint Server 2016 writes the logs. If verbose-level logging is configured, the server records a large amount of data. Therefore, a slow connection might result in poor log performance.

* Restrict log disk space usage:

By default, the amount of disk space that diagnostic logging can use is unlimited. Therefore, restrict the disk space that logging uses, especially if you configure logging to write verbose-level events. When the disk reaches the restriction, SharePoint Server 2016 removes the oldest logs before it records new logging data.

* Use the Verbose setting sparingly:

You can configure diagnostic logging to record verbose-level events. This means that SharePoint Server 2016 records every action that it takes. Verbose-level logging can quickly use drive space and affect drive and server performance. You can use verbose-level logging to record more detail when you are making critical changes and then reconfigure logging to record only higher-level events after you make the change.

* Regularly back up logs:

Diagnostic logs contain important data. Therefore, back up the logs regularly to ensure that this data is preserved. When you restrict log drive space usage, or if you keep logs for only a few days, SharePoint Server 2016 automatically deletes log files, starting with the oldest files first, when the threshold is met.

* Enable event log flooding protection:

When you enable this setting, SharePoint Server 2016 detects repeating events in the Windows event log, and suppresses them until conditions return to a typical state.

You can set the level of diagnostic logging for the event log and for the trace log. This limits the types and amount of information that are written to each log.

The following tables define the levels of logging that are available for the event log and trace log.

event-log-levels

trace-log-levels

Configure diagnostic logging by using Central Administration :

  1. In Central Administration, on the home page, click Monitoring.
  2. On the Monitoring page, in the Reporting section, click Configure diagnostic logging.
  3. On the Diagnostic Logging page, in the Event Throttling section, configure event throttling as follows:To configure event throttling for all categories:
    1. Select the All Categories check box.
    2. Select the event log level from the Least critical event to report to the event log list.
    3. Select the trace log level from the Least critical event to report to the trace log list.

    To configure event throttling for one or more categories:

    1. Select the check boxes of the categories that you want.
    2. Select the event log level from the Least critical event to report to the event log list.
    3. Select the trace log level from the Least critical event to report to the trace log list.

    To configure event throttling for one or more subcategories (you can expand one or more categories and select any subcategory):

    1. Click the plus (+) next to the category to expand the category.
    2. Select the check box of the subcategory.
    3. Select the event log level from the Least critical event to report to the event log list.
    4. Select the trace log level from the Least critical event to report to the trace log list.

    To return event throttling for all categories to default settings:

    1. Select the All Categories check box.
    2. Select Reset to default from the Least critical event to report to the event log list.
    3. Select Reset to default from the Least critical event to report to the trace log list.
  4. In the Event Log Flood Protection section, select the Enable Event Log Flood Protection check box.
  5. In the Trace Log section, in the Path box, type the path of the folder to which you want logs to be written.
  6. In the Number of days to store log files box, type the number of days (1-366) that you want logs to be kept. After this time, logs will automatically be deleted.
  7. To restrict the disk space that logs can use, select the Restrict Trace Log disk space usage check box, and then type the number gigabytes (GB) you want to restrict log files to. When logs reach this value, older logs will automatically be deleted.
  8. After you have made the changes that you want on the Diagnostic Logging page, click OK.

Configure diagnostic logging by using Windows PowerShell :

  1. Verify that you have the following memberships:
  • securityadmin fixed server role on the SQL Server instance.
  • db_owner fixed database role on all databases that are to be updated.
  • Administrators group on the server on which you are running the Windows PowerShell cmdlets.

An administrator can use the Add-SPShellAdmin cmdlet to grant permissions to use SharePoint Server 2016 cmdlets.

  1. On the Start menu, click All Programs.
  2. Click SharePoint 2016.
  3. Click SharePoint 2016 Management Shell.
  4. To change the drive to which the server writes logs, at the Windows PowerShell command prompt, type the following command:

Set-SPDiagnosticConfig -LogLocation D:\DiagnosticLogs

  1. To restrict log disk space usage, at the Windows PowerShell command prompt, type the following command:

Set-SPDiagnosticConfig -LogMaxDiskSpaceUsageEnabled

Or assign the maximum disk space for logs:

Set-SPDiagnosticConfig -LogDiskSpaceUsageGB 500

  1. To view the current logging level, at the Windows PowerShell command prompt, type the following command:

Get-SPLogLevel

  1. To change the logging level, at the Windows PowerShell command prompt, type the following command:

Set-SPLogLevel -TraceSeverity Monitorable

To set all categories back to default levels, at the Windows PowerShell command prompt, type the following command, and then press ENTER:

Clear-SPLogLevel

9. To enable event log flooding protection, at the Windows PowerShell command prompt, type the following command:

Set-SPDiagnosticConfig -EventLogFloodProtectionEnabled

Monitor cache performance SharePoint 2016

SharePoint Server 2016 provides three types of caches that help improve the speed at which web pages load in the browser: the BLOB cache, the ASP.NET output cache, and the object cache.

The BLOB cache is a disk-based cache that stores binary large object files that are used by web pages to help the pages load quickly in the browser.

The ASP.NET output cache stores the rendered output of a page. It also stores different versions of the cached page, based on the permissions of the users who are requesting the page.

The object cache reduces the traffic between the web server and the SQL database by storing objects such as lists and libraries, site settings, and page layouts in memory on the front-end web server. As a result, the pages that require these items can be rendered quickly, increasing the speed with which pages are delivered to the client browser.

The monitors measure cache hits, cache misses, cache compactions, and cache flushes. The following list describes each of these performance monitors.

A cache hit occurs when the cache receives a request for an object whose data is already stored in the cache. A high number of cache hits indicates good performance and a good end-user experience.

A cache miss occurs when the cache receives a request for an object whose data is not already stored in the cache. A high number of cache misses might indicate poor performance and a slower end-user experience.

Cache compaction (also known as trimming), happens when a cache becomes full and additional requests for non-cached content are received. During compaction, the system identifies a subset of the contents in the cache to remove, and removes them. Typically these contents are not requested as frequently.

Compaction can consume a significant portion of the server’s resources. This can affect both server performance and the end-user experience. Therefore, compaction should be avoided. You can decrease the occurrence of compaction by increasing the size of the cache. Compaction usually happens if the cache size is decreased. Compaction of the object cache does not consume as many resources as the compaction of the BLOB cache.

A cache flush is when the cache is completely emptied. After the cache is flushed, the cache hit to cache miss ratio will be almost zero. Then, as users request content and the cache is filled up, that ratio increases and eventually reaches an optimal level. A consistently high number for this counter might indicate a problem with the farm, such as constantly changing library metadata schemas.

You can monitor the effectiveness of the cache settings to make sure that the end-users are getting the best experience possible. Optimum performance occurs when the ratio of cache hits to cache misses is high and when compactions and flushes only rarely occur. If the monitors do not indicate these conditions, you can improve performance by changing the cache settings.

The following sections provide specific information for monitoring each kind of cache.

Monitoring BLOB cache performance:

monitor-blob-cache

Note:
For the BLOB cache, a request is only counted as a cache miss if the user requests a file whose extension is configured to be cached. For example, if the cache is enabled to cache .jpg files only, and the cache gets a request for a .gif file, that request is not counted as a cache miss.

Monitoring ASP.NET output cache performance :

monitoring-asp-net-output-cache-performance

Note:
For the ASP.NET output cache, all pages are cached for a fixed duration that is independent of user actions. Therefore, there are flush-related monitoring events.

Monitoring object cache performance :

The object cache is used to store metadata about sites, libraries, lists, list items, and documents that are used by features such as site navigation and the Content Query Web Part.

This cache helps users when they browse to pages that use these features because the data that they require is stored or retrieved directly from the object cache instead of from the content database.

The object cache is stored in the RAM of each web server in the farm. Each web server maintains its own object cache.

You can monitor the effectiveness of the cache settings by using the performance monitors that are listed in the following table.

monitoring-object-cache-performance

Content databases contain orphaned Apps SharePoint 2013

SharePoint Health Analyzer rule “Content databases contain orphaned Apps.”

Some situation content database may become corrupted. The corrupted database may contain orphaned apps. Orphaned apps are not accessible, which causes unnecessary resource and license consumption and may result in failures in SharePoint upgrade.

Solution:

Remove app for SharePoint instances from a SharePoint 2013 site.

A user must have the Manage Web site permission to remove an app for SharePoint. By default, this permission is only available to users with the Full Control permission level or who are in the site Owners group.

To remove an app from a SharePoint site

  1. Verify that the user account that is performing this procedure is a member of the Site owners group.
  2. On the site, on the Settings menu, click View Site Contents.
    In the Apps section, point to the app that you want to remove, click …, and then 3. click Remove.
  3. Click OK to confirm that you want to remove the app.

To remove an app by using Windows PowerShell

Verify that you have the following memberships:

a. securityadmin fixed server role on the SQL Server instance.
b. db_owner fixed database role on all databases that are to be updated.
c. Administrators group on the server on which you are running the Windows PowerShell cmdlets.
d. Site Owners group on the site collection to which you want to install the app.

An administrator can use the Add-SPShellAdmin cmdlet to grant permissions to use SharePoint 15 Products cmdlets.

On the Start screen, click SharePoint 2013 Management Shell.

type the following commands, and press ENTER after each one:

$instances = Get-SPAppInstance -Web
#Gets all apps installed to the subsite you specify.

$instance = $instances | where {$_.Title -eq ”}
#Sets the $instance variable to the app with the title you supply.

Uninstall-SPAppInstance -Identity $instance
#Uninstalls the app from the subsite.

is the path site collection or subsite that contains the app.
is the title of the app you want to remove.

At the question “Are you sure you want to perform this action?”,
type Y to uninstall the app.

Locate and remove app instances in all locations

An app for SharePoint in the App Catalog is available for users to install.Users can install apps for SharePoint on many sites.

Below two Windows PowerShell scripts can be used to find all locations for a specific app and then uninstall all instances from every location.

First script to locate all instances of a specific app in a SharePoint environment. Then use the second script to uninstall all instances of the app from the SharePoint environment.

To locate specific apps by using Windows PowerShell (save as script and run script)

Verify that you have the following memberships:

a. securityadmin fixed server role on the SQL Server instance.
b. db_owner fixed database role on all databases that are to be updated.
c. Administrators group on the server on which you are running Windows PowerShell cmdlets.

An administrator can use the Add-SPShellAdmin cmdlet to grant permissions to use SharePoint 2013 cmdlets

1. save the below script as “Get-AppInstances.ps1”

This Windows PowerShell script gets all app instances from your SharePoint 2013 farm for a specified App ID on a specified web application. You specify the App ID and the web application URL and the script will remove all of the instances of the App for all webs in that web application.

param(
[Parameter(Mandatory=$true)] [Guid] $productId,
[Parameter(Mandatory=$true)] [String] $webAppUrl
)

function GetAllInstances($productId = $null, $webAppUrl = $null)
{
$outAppName = “”;
$sites = Get-SPSite -WebApplication $webAppUrl
$outWebs = @()
foreach($site in $sites){
if($site.AdministrationSiteType -ne “None”){
continue;
}
$webs = Get-SPWeb -site $site
foreach($web in $webs) {
$appinstances = Get-SPAppInstance -Web $web
foreach($instance in $appinstances) {
if($productId -eq $instance.App.ProductId) {
if ($outAppName -eq “”) {
$outAppName = $instance.Title;
}
$outWebs += $web;
}
}
}
}
return ($outAppName,$outWebs)
}
Write-Host “This script will search all the sites in the webAppUrl for installed instances of the App.”
$confirm = Read-Host “This can take a while. Proceed? (y/n)”
if($confirm -ne “y”){
Exit
}

$global:appName = $null;
$global:webs = $null;

{
$returnvalue = GetAllInstances -productId $productId -webAppUrl $webAppUrl;
$global:appName = $returnvalue[0];
$global:webs = $returnvalue[1];
}
);

$count = $global:webs.Count;
if($count -gt 0){
Write-Host “App Name:” $global:appName;
Write-Host “Product Id: $productId”;
Write-Host “Number of instances: $count”;
Write-Host “”;
Write-Host “Urls:”;

foreach($web in $global:webs) {
Write-Host $web.Url;
}
}
else {
Write-Host “No instances of the App with Product Id $productId found.”;
}
return;

  1. Now Open “SharePoint 2013 Management Shell”
  2. Change to the directory where you saved the file.

  3. At the Windows PowerShell command prompt, type the following command:

./ Get-AppInstances.ps1 -productId -webAppUrl

is the GUID ID of the App
and is the full URL of the web application.

To uninstall specific apps from all locations by using Windows PowerShell (save as script and run script)

1. Verify that you have the following memberships:

a. securityadmin fixed server role on the SQL Server instance.
b. db_owner fixed database role on all databases that are to be updated.
c. Administrators group on the server on which you are running Windows PowerShell cmdlets.

An administrator can use the Add-SPShellAdmin cmdlet to grant permissions to use SharePoint 2013 cmdlets

2. save the below script as “Remove-App.ps1”

This Windows PowerShell script removes all app instances from your SharePoint 2013 farm for a specified App ID on a specified web application. You specify the App ID and the web application URL and the script will remove all of the instances of the App for all webs in that web application.

param(
[Parameter(Mandatory=$true)] [Guid] $productId,
[Parameter(Mandatory=$true)] [String] $webAppUrl
)

function RemoveInstances($productId = $null, $webAppUrl = $null)
{
$outAppName = “”;
$sites = Get-SPSite -WebApplication $webAppUrl
$outWebs = @()
foreach($site in $sites){
if($site.AdministrationSiteType -ne “None”){
continue;
}
$webs = Get-SPWeb -site $site
foreach($web in $webs) {
$appinstances = Get-SPAppInstance -Web $web
foreach($instance in $appinstances) {
if($productId -eq $instance.App.ProductId) {
if ($outAppName -eq “”) {
$outAppName = $instance.Title;
}
$outWebs += $web;
Write-Host “Uninstalling from” $web.Url;
Uninstall-SPAppInstance -Identity $instance -confirm:$false
}
}
}
}
return ($outAppName,$outWebs)
}

$confirm = Read-Host “This will uninstall all instances of the App and is irreversible. Proceed? (y/n)”
if($confirm -ne “y”){
Exit
}

$global:appName = $null;
$global:webs = $null;

{
$returnvalue = RemoveInstances -productId $productId -webAppUrl $webAppUrl;
$global:appName = $returnvalue[0];
$global:webs = $returnvalue[1];
}
);

$count = $global:webs.Count;
if($count -gt 0){
Write-Host “All the instances of the following App have been uninstalled:”;
Write-Host “App Name:” $global:appName;
Write-Host “Product Id: $productId”;
Write-Host “Number of instances: $count”;
Write-Host “”;
Write-Host “Urls:”;

foreach($web in $global:webs) {
Write-Host $web.Url;
}
}
else {
Write-Host “No instances of the App with Product Id $productId found.”;
}
return;

  1. Open SharePoint 2013 Management Shell
  • Change to the directory where you saved the file.

  • At the Windows PowerShell command prompt, type the following command:

  • ./ Remove-App.ps1 -productId -webAppUrl

    is the GUID ID of the App
    is the full URL of the web application.

    If the issue still persists like as below

    If you have an orphaned app in the initialized state on a site and you delete the site, Health Analyzer reports that there’s an error and the auto-fix doesn’t work.”

    Apply CU November 2016 which will 100% resolve the issue

    SharePoint Server 2013 (KB3127933)
    SharePoint Foundation 2013 (KB3127930)

    Improving SharePoint performance using SQL Server settings

    SharePoint performance is a recursive problem and preoccupation. As a Database Administrator, we have to deal with SharePoint when configuring SQL Server databases.

    In this article, I will propose a list of best practices in SQL Server settings aimed to reduce SharePoint performance issues.

    Autogrowth

    Do not keep the default value which is 1 MB. We can illustrate with a simple example why this is a bad idea.

    When a document of 5 MB is uploaded, it means there are 5 Autogrowth which are activated. In fact, there are 5 allocations of space which must slow your system.

    Moreover, your uploaded document will be fragmented across your different data files. This configuration will decrease your performance a second time.

    To avoid performance issues and reduce fragmented data files, you should set the autogrowth value to a fixed number of megabytes.

    My recommendation is 1024 MB for data files and 256 MB for log files. But keep in mind, this is a global recommendation. In fact, the bigger the database, the bigger the growth increment should be.

    SQL Server disk cluster size

    The default value of SQL Server is 4 KB. But in fact, it is nearly the worst value you can choose for this configuration!

    Globally, 64 KB is a safe value. Indeed, the server reads 64 KB at the time and can deliver larger chunks of data to the SQL Server database.

    TempDB Optimization

    First, the TempDB recovery model should be set to simple. Indeed, this model automatically reclaims log space to keep space requirements small.

    Also, you should put your TempDB on the fastest disks you have, because TempDB is heavily used by SharePoint. Do not let SQL Server use this disk for any other needs, except TempDB utilization!

    Furthermore, each TempDB file should be 25% larger than the largest content database. Not many DBAs realize how a TempDB is used by SharePoint and to what extent a TempDB can grow!

    Index Fragmentation

    WSS_Content database, for example, is used to store site collection as well as lists and its tables are shared. Therefore, indexes are very important!

    So do not forget to manage the fragmentation of your databases.

    My recommendation is to perform a Reorganize when your fragmentation is between 10% and 30 % as well as a Rebuild index when your fragmentation is above 30%.

    Take care about indexes with more than 1’000 pages!

    Statistics

    Do not enable Auto-Create Statistics on an SQL Server that supports SharePoint Server! Let SharePoint Server configure the required settings alone.

    Auto-Create Statistics can significantly change the execution plan of a query from one instance of SQL Server to another.

    Therefore, do not enable Auto-Update Statistics and use instead SharePoint Auto-Update capability instead.

    SQL Server Memory Allocation

    The default values of SQL Server for memory allocation are 0 MB for Minimum server memory and 2147483647 MB for Maximum server memory.

    The default value of the Maximum server memory is not optimized at all!

    You should set a custom value depending on the total amount of physical memory, the number of processors, and the number of cores.

    To calculate your SQL Max Memory, I suggest you to read this article.

    Recycle Bin

    Be aware that items in the recycle Bin may affect the performance.

    Moreover, after a certain limit of days or after a deletion, these items are moved to a second stage recycle bin that may also affect your performance.

    As a result, you have to manage your recycle bin depending on your needs to ensure that the size of your recycle bin will not continue to grow out of control.

    MAXDOP

    The default value of your MAXDOP is 0. But for better performance, you should make sure that a single SQL Server process serves each request.

    Therefore, you must set MAXDOP to 1.

    Fill Factor

    The default value is 0, which is equal to 100. It means that you do not provide space for index expansion.

    But when a new row is added to a full index page, the Database Engine make a reorganization called Page Split.

    Page Split can take time to perform, and can cause fragmentation increasing I/O operations.

    I recommend to set a Fill Factor value of 80. It means that 20 % of each-level page will be left empty.

    Therefore, you can support growth and reduce fragmentation.

    Instant File initialization

    This feature, when enabled, allows SQL Server to initialize database files instantly, without physically zeroing out each and every 8K page in the file.

    Therefore, depending on the size of files you have, you can save a lots of time.

    Conclusion

    The default settings of the content database in SQL Server are pretty bad and far from what we really need. You should always opt for a pre-allocate size strategy and not rely on autogrowth.

    Monitoring your databases for space and growth to avoid bad surprises is very important.

    Also, do not forget to modify your model database for size allocation rules.

    Ans if you do not want to suffer from bad performances, do not use the Auto-Shrink capability.