Troubleshooting “Database Performance Issues” in SharePoint
Issue: SharePoint relies heavily on SQL Server. If the database is slow, SharePoint experiences slow page loads, delayed search results, workflow lag, and timeouts.
Causes: Poor indexing, excessive database growth, unoptimized queries, high transaction logs, and resource contention.
1. Identify the Root Cause of SQL Performance Issues
Before applying fixes, determine which SQL components are causing slow performance.
Symptoms of Database Performance Issues:
✅ Slow site navigation and list queries.
✅ Delays in search indexing and workflow execution.
✅ Frequent SQL timeouts in SharePoint logs.
✅ High CPU or memory usage on the SQL Server.
✅ Large database size due to excessive transaction logs.
Tools for Diagnosis:
🔹 SQL Server Profiler → Detects slow queries.
🔹 SQL Server Activity Monitor → Shows real-time CPU, memory, and query stats.
🔹 Performance Monitor (perfmon.msc) → Tracks SQL Server resource consumption.
🔹 SharePoint ULS Logs → Identifies SQL-related errors.
🔹 Event Viewer (eventvwr.msc) → Logs SQL connection issues.
Check SQL Server for Performance Bottlenecks:
🔹 Find long-running queries:
SELECT TOP 10 *
FROM sys.dm_exec_requests
ORDER BY total_elapsed_time DESC;
🔹 Identify missing indexes:
SELECT * FROM sys.dm_db_missing_index_details;
🔹 Monitor disk latency:
SELECT * FROM sys.dm_io_virtual_file_stats(NULL, NULL);
2. Optimize SQL Server Configuration for SharePoint
Fixes:
🔹 Ensure SQL Server is dedicated to SharePoint (avoid hosting other databases).
🔹 Disable Auto-Close on SharePoint databases:
ALTER DATABASE SharePoint_Content SET AUTO_CLOSE OFF;
🔹 Enable Instant File Initialization for faster database growth.
🔹 Increase SQL Max Degree of Parallelism (MAXDOP) to 1:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max degree of parallelism', 1;
RECONFIGURE;
🔹 Set SQL Server Memory Allocation to avoid overuse:
EXEC sp_configure 'max server memory', 8192; -- Adjust based on available RAM
RECONFIGURE;
3. Optimize Database Indexing & Maintenance
Fixes:
🔹 Identify and rebuild fragmented indexes to speed up queries:
ALTER INDEX ALL ON [dbo.AllDocs] REBUILD;
🔹 Update statistics for better query performance:
UPDATE STATISTICS dbo.AllDocs;
🔹 Run Database Consistency Check (DBCC) to detect corruption:
DBCC CHECKDB ('SharePoint_Content');
🔹 Enable Database Auto-Growth but set a reasonable size increment.
4. Reduce Transaction Log Growth & Shrink Large Databases
Fixes:
🔹 Check large transaction log size:
DBCC SQLPERF(LOGSPACE);
🔹 Set Simple Recovery Mode for non-critical databases:
ALTER DATABASE SharePoint_Content SET RECOVERY SIMPLE;
🔹 Shrink the transaction log:
DBCC SHRINKFILE ('SharePoint_Content_log', 1000);
🔹 Schedule regular log backups to prevent excessive growth.
5. Optimize SQL Queries & Reduce Heavy Workloads
Fixes:
🔹 Identify long-running queries:
SELECT TOP 10 *
FROM sys.dm_exec_requests
ORDER BY total_elapsed_time DESC;
🔹 Optimize large list queries by enabling indexing.
🔹 Move archived data to separate databases for better performance.
🔹 Offload read-heavy operations to read replicas (if using SQL Always On).
6. Reduce SharePoint Load on SQL Server
Fixes:
🔹 Enable Object Caching to reduce database queries.
🔹 Configure List View Threshold to prevent expensive queries.
🔹 Limit Event Receivers & Workflows that make frequent database calls.
🔹 Move Search Indexing to a separate SQL instance if possible.
7. Improve Disk I/O Performance for SQL Server
Fixes:
🔹 Store database and transaction logs on separate high-speed disks (SSD/NVMe).
🔹 Increase TempDB size and allocate multiple TempDB files:
ALTER DATABASE TempDB ADD FILE (NAME = tempdev2, FILENAME = 'D:\TempDB2.mdf', SIZE = 500MB);
🔹 Monitor disk latency using PerfMon.
8. Monitor & Automate SQL Performance Tuning
8.1. Set Up Performance Monitoring
✅ Use SQL Server Agent Alerts for high CPU, slow queries, or transaction log growth.
✅ Schedule Automated Index Maintenance using SQL jobs.
✅ Enable SQL Extended Events for deeper analysis of query performance.
8.2. Automate SQL Health Checks with PowerShell
Schedule a script to monitor SQL performance:
Get-WmiObject Win32_PerfFormattedData_MSSQLSERVER_SQLServerBufferManager | Select-Object Name, BufferCacheHitRatio