Optimizing Sharepoint Performance: 5 Critical Areas To Address
Efficient Indexing for Faster Search Queries
The search indexing functionality in SharePoint allows users to quickly find documents and information stored in the platform. However, inefficient indexing can result in slow and inaccurate search results. By optimizing the search index, administrators can achieve faster query response times and improved relevance for search users.
Overview of Search Indexing in SharePoint
SharePoint’s search function relies on crawlers to catalog and add content to the search index. Crawlers extract text, metadata properties, and statistics to build an index of all accessible content. Items like documents, list items, and pages are indexed by default while other file types can be added through crawl rules and file type inclusion lists.
The search index is stored in databases on the search server. The query and index components work together to provide rapid responses to search requests. When a query is entered, the search functionality matches the query text semantically and linguistically against the indexed content. Relevant results are then returned to the user based on ranking models.
Best Practices for Optimizing Search Index
Follow these best practices to optimize the performance of SharePoint search indexing:
- Schedule incremental crawls frequently to keep content indexed and search fresh
- Use exclusion rules to avoid indexing unnecessary content
- Monitor index size and partition indexes that grow too large
- Create optimized crawl schedules to maximize resources
- Validate relevance tuning to return high quality results
Example Code for Improving Crawl Efficiency
“`
$site = Get-SPSite https://mysite
$contentSource = Get-SPEnterpriseSearchCrawlContentSource -Identity $site.RootWeb.Url
$contentSource.IncrementalCrawlSchedule.Interval = 15
$contentSource.FullCrawlSchedule.StartHour = 1
$contentSource.FullCrawlSchedule.StartMinute = 0
$contentSource.FullCrawlSchedule.EndHour = 5
$contentSource.FullCrawlSchedule.EndMinute = 0
$contentSource.Update()
“`
This PowerShell script configures incremental crawls to run every 15 minutes to keep search results current. It also restricts full crawls to run daily between 1-5 AM to minimize disruption.
Minimizing Page Load Times
Slow page load times negatively impact the end user experience in SharePoint. Optimizing page performance involves identifying issues that bottleneck requests and configuring options to speed up page loads.
Identifying Performance Bottlenecks
Use the following methods to pinpoint components dragging down page load speeds in SharePoint:
- Check the SharePoint developer dashboard for request durations
- Use browser debug tools to analyze resource load times
- Review IIS logs to identify long running requests
- Capture ULS log data during page loads
Troubleshooting page loads at the user level provides visibility into the areas introducing the longest delays. Factors like unoptimized web parts, complex page layouts, large file sizes, and excessive custom code are common sources of latency.
Optimizing Page Layouts and Web Parts
Simplify page designs to streamline performance. Consider these optimizations:
- Reduce number of web parts per page
- Set web part cache duration to 1 hour minimum
- Choose lighter weight web parts
- Minify custom web part code
- Ensure list view thresholds are not exceeded
- Remove unnecessary list view fields
Configuring Caching Settings
Enabling caching mechanisms helps minimize round trips to the server, reducing page load times. Recommended caching options include:
- Browser file caching for 1 week
- Browser output caching for 1 hour
- Object cache enabled with 512 MB * minimum allocation
- Page output cache set to 300 seconds
- Database object cache enabled
Optimizing SharePoint Databases
As SharePoint databases grow from content updates, versioning, and metadata, administrators need to proactively monitor and optimize databases to prevent performance dips.
Monitoring Database Size and Growth
Use the following SQL queries to track SharePoint database sizes and growth rates:
“`sql
— Current database size
SELECT SUM(CAST(mf.size AS BIGINT)*8/1024 AS BIGINT))/1024 as [SizeGB]
FROM sys.master_files mf WHERE mf.database_id = DB_ID()
— Database growth per day
SELECT DB_NAME([database_id]) AS [Database],
CAST(SUM([size])* 8.0/1024/1024 AS DECIMAL(15,2)) AS [Size GB],
CAST(SUM([size])* 8.0/1024/1024/CAST(DATEDIFF(DAY,[create_date],GETDATE()) AS DECIMAL(15,2)) AS DECIMAL(15,2)) AS [Growth GB/Day]
FROM sys.[master_files] WHERE [database_id] = DB_ID()
GROUP BY [database_id]
“`
Database Indexing Strategies
Apply the following database indexing best practices to enhance database response times:
- Only index columns frequently filtered or sorted on
- Clustered indexes on ever-increasing unique IDs
- Non-clustered field indexes for repetitive filters
- Covering indexes combining commonly grouped fields
- Columnstore indexes for large analytics workloads
Script Examples for Index Maintenance
“`sql
–Weekly index rebuilds
DECLARE @IndexName NVARCHAR(128)
DECLARE curIndexes CURSOR FOR
SELECT Name FROM sys.indexes WHERE index_id > 0
OPEN curIndexes
FETCH NEXT FROM curIndexes INTO @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (‘ALTER INDEX [‘ + @IndexName + ‘] ON [dbo].[TableName] REBUILD’)
FETCH NEXT FROM curIndexes INTO @IndexName
END
CLOSE curIndexes
DEALLOCATE curIndexes
— Nightly stats update
DECLARE @TableName NVARCHAR(128)
DECLARE curTables CURSOR FOR
SELECT TABLE_SCHEMA + ‘.’ + TABLE_NAME FROM information_schema.tables
OPEN curTables
FETCH NEXT FROM curTables INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (‘UPDATE STATISTICS ‘+@TableName+’ WITH FULLSCAN’)
FETCH NEXT FROM curTables INTO @TableName
END
CLOSE curTables
DEALLOCATE curTables
“`
These maintenance scripts rebuild indexes weekly and update table statistics nightly to improve performance.
Enabling Remote Blob Storage
Storing blob content externally in Azure Storage instead of SharePoint SQL databases enables administrators to minimize expensive database growth.
Understanding BLOB Storage in SharePoint
Binary large objects (BLOBs) containing media, images, attachments, and other files can rapidly bloat SharePoint content databases. BLOBs are ideally suited for remote storage providers compared to metadata and text content best stored relationally.
Migrating Content to Azure Storage
SharePoint offers a Remote Blob Storage (RBS) API to integrate external BLOB providers. Follow these steps to migrate BLOBs to Azure Storage:
- Create Azure Storage account
- Install RBS on SharePoint servers
- Enable RBS on content databases
- Run RBS migration tool
- Point databases to Azure containers
- Disable externalization on DBs to direct transfers
Script for Automating Azure Storage Sync
“`PowerShell
# Variables
$webAppUrl = “https://mysite.sharepoint.com”
$azureAccountName = “storageaccount”
$azureAccountKey = “1234abcd…”
$containerName = “my-container”
# Connect to Azure account
$ctx = New-AzureStorageContext -StorageAccountName $azureAccountName -StorageAccountKey $azureAccountKey
# Get all BLOBs in container
$blobs = Get-AzureStorageBlob -Context $ctx -Container $containerName
# Iterate through BLOBs
Foreach($b in $blobs) {
# Copy BLOB to SharePoint library
Move-SPAzureStorageFile -SourceUri $b.ICloudBlob.Uri -DestUrl “$webAppUrl/sites/site/doclib/folder” -OverwriteIfExists
}
“`
This script syncs Azure Storage container contents to a SharePoint document library for externalized BLOB access.
Monitoring Performance with Built-in Tools
SharePoint offers several built-in tools to help administrators detect issues and monitor system health.
Using Usage Analytics to Identify Issues
The Usage Analytics dashboard in the SharePoint admin center highlights activity spikes indicating performance hot spots. Analyze metrics like:
- Total requests and throttled requests
- Failed requests
- Most active pages
- Most active sites
Triaging usage data identifies web applications, sites, libraries, or pages to optimize further through improvements or throttling limits.
Interpreting Diagnostic Logging Data
SharePoint generates abundant log data containing diagnostic information captured at various verbosities. Common logs to mine for issues include:
- IIS request logs
- SharePoint trace logs
- Search crawl logs
- Workflow instance management logs
Log analytics tools help visualize trends and bring together data from these different sources.
Configuring Health Analyzer Rules
SharePoint’s health analyzer can automatically detect and trigger alerts for common performance issues. Enable rules like:
- Page load timeouts
- Cache utilization caps
- Search index partition warnings
- Job execution bottlenecks
- Site collection throttling events
Proactive health monitoring through analyzer policies helps quickly identify and respond to emerging problems before they disrupt users.
