Network Management

SQL Server - Performance optimization and best practices

This article describes information on SQL Server performance optimization and best practices and can be sent to customers if needed.

First published date

12/16/2018 3:38 AM

Last published date

9/4/2025 8:34 PM

Overview

For large SolarWinds Platform installations with multiple polling engines, the SQL Server must be able to handle large quantities of data that can be generated by the SolarWinds Platform deployment with multiple modules and multiple polling engines. To cope with the data, optimize the SQL system for write performance:

  • Use RAID 1+0 with 4 or more disks. The more disks, the better the performance.

  • Use a battery backed-up write back caching controller. This improves write performance regardless of the RAID level.

  • Use high RPM HDDs or preferably, Solid-State Disks.

  • Separate the data, tempdb,  and Transaction Log files on separate LUNs.

  • Maximize the available RAM.

  • Utilize a 64-bit-based architecture.

Product section

Orion Platform

Resolution

To optimize the performance of your SQL Server, review the following steps:

  • Step 1: Identify Performance Issues
  • Step 2: Review SQL Server settings
  • Step 3: Review hints on SQL Server and Virtual Machines
  • Step 4: Review links to further details

Step 1: Identify Performance Issues 

Symptoms of performance issues

Search for the following symptoms:

  • Web Performance will be slow
  • Long-Running Queries in SolarWinds Platform log files
  • SQLClient Timeout errors in SolarWinds Platform log files

Use SolarWinds Database Performance Analyzer 

Use SolarWinds Database Performance Analyzer (DPA) to monitor and analyze the performance of your database. Find out more about the latency, disk queue length, or sessions. See Database Performance Analyzer Getting Started Guide.

Use the SQL Activity Monitor 

Activity Monitor is a tool in SQL Management Studio that shows you activity in real time. Real-time data might not be enough to troubleshoot performance issues, consider using DPA instead.

Open Activity Monitor and right-click the SQL Server in Object Explorer to launch Activity Monitor. 

Review Processes in the SQL Activity Monitor

Expand Processes to display all active SQL Processes currently running on the server. There are several useful columns here for troubleshooting.
 

Session ID - This is the SQL Process ID or SPID
Login - SQL Login used for this SPID
Database - Database the process is running on
Task State - Current state of the process
Command - Type of SQL statement
Application - Application on the host running the process
Wait Time - Time the Process has spent waiting if suspended
Wait Type - Why the process is suspended
Blocked By - SPID Blocking the process of suspended
Hostname - Hostname executing the process

 

To filter processes, click on the column header drop-down.

Review Suspended Processes in the Activity Monitor

Processes go into the Suspended state when there are not enough resources available to complete the task. The wait time counter shows how many milliseconds the process has been waiting, the Wait Type explains why the process is waiting. These waits will often result in the Blocking of other processes which will show in the BlockedBy column.

Common Wait Types:
 

  • ASYNC_NETWORK_IO—This is usually a sign of network latency between the client and the server.
  • CXPACKET—Indicates that SQL is waiting on a Parallel process to complete. This can be a sign of resource issues on the SQL server (CPU, MEM, DiskIO) or the query itself is poorly written. 
  • PAGEIOLATCH_EX—Buffer latches including the PAGEIOLATCH_EX wait type are used to synchronize access to BUF structures and associated pages in the SQL Server database. This can be a sign of resource issues on the SQL server or a poorly written query. When seen in conjunction with the CXPACKET wait Index Fragmentation is often the cause.
  • WRITELOG—When a SQL Server session waits on the WRITELOG wait type, it is waiting to write the contents of the log cache to disk where the transaction log is stored. This is almost always a sing of poor disk performance. 
  • LCK_(X) —This Occurs when a resource is in use by another query, usually the result of a UPDATE, INSERT, or DELETE statement. LCK waits are usually caused by resource contention but may also be the results of process blocking from any of the above wait types.

 

Review Resource Waits in the Activity Monitor

This view will show you the total active and cumulative wait time for each waittype. This can be useful for troubleshooting resource issues. Keep in mind that anything less than 1000 ms (Recent Wait Time) is considered normal.


Review Data File I/O in the Activity Monitor

This view displays the response time for all of the SQL database files. This is a useful tool for troubleshooting disk I/O latency. Response times greater than 10 cause slowness, greater than 100 cause problems.

Step 2: Review SQL Server Configuration 

  • Having more files in the filegroup helps the SQL server distribute the load generated by multiple threads while working with files. 
  • The recommended ratio between the number of cores and the files in the filegroup is typically 4:1 or 2:1 (for example, 16 cores and four files, or 16 cores and eight files).
  • The Transaction log should have enough space to grow to 50% of the total database size.
  • The size and growth setting for all files in a filegroup must be set to identical values in order to distribute the load evenly. 
  • For the transaction log, it is not effective to create more files, because the SQL server can only use the first file. 
  • For the tempdb database, use an SSD disk.
  • RAID 1+0 - striping and mirroring for database files
  • Attached Storage (SAN/NAS) Arrays are supported if throughput is high enough. See SCSI, Fiber Channel.

Why not RAID 5? 

RAID 5 is the most common Disk subsystem topology deployed on servers. This topology is meant for availability and not performance and may cause IO issues in SQL. RAID 5 or 6 may be fine for small environments but will cause performance issues when scaled.

SQL and Memory Consumption 

SQL does not often release memory once granted. Because of this, it may appear that SQL is using all of the available memory on the system. This may not cause performance issues as long as the Operating System has sufficient memory to run effectively. You may need to limit the amount of memory SQL can use to prevent this from happening. How to limit SQL memory

Properties 

Review the following settings and recommendations for your SQL server and the database. See SQL Server configuration best practices for more details.

 

SETTING

RECOMMENDATION

Maximum Degree of Parallelism (MAXDOP)
Server > Advanced

Use the number of physical cores in a single CPU socket.

Cost Threshold of Parallelism
Server > Advanced

Change the default 5 to 50, and adjust as necessary.

Perform Volume Maintenance Task right for SQL user account
In the Windows Local Security Policy tool, navigate to Security Settings > Local Policies > User Rights Assignment.

Add the account under which the SQL Service is running to the Perform volume maintenance tasks policy.

Data files initial size and autogrowth
Database > Properties > files

Data files - autogrowth 1024 MB

Transaction log file initial size and autogrowth

  • Initial size 8192 MB, autogrowth 8192 MB for large environments

  • Initial size 2048 MB, autogrowth 1024 MB for medium environments

Initial size and autogrowth for tempdb

  • Data files - initial size 1024 MB, autogrowth 1024 MB 

  • Logfile - initial size 2048 MB, autogrowth 1024 MB

Recovery mode

Use SIMPLE if possible. FULL requires further measures.

Query Optimizer fixes
Database > Properties > Options

  • SQL 2016 and 2017
    Query Optimizer Fixes = ON

  • SQL earlier versions:
    Enable trace flag 4199 at service level: 
     

    -- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES=ON;

Compatibility level
Database > Properties > Options

Select the highest available option. For example, do not use SQL Server 2008(100) for SQL Server 2017.

 

Step 3: Review recommendations for SQL Server and Virtual Machines 

SQL Server is a Microsoft product, and SolarWinds can thus provide only general, experience-based recommendations. 

The SolarWinds Platform writes a large number of small writes to the SQL database. To insert data to a table, SQL requires a few reads before the write operation can be done. A large SolarWinds Platform installation thus leads to a high-traffic database (high throughput). The more elements the SolarWinds Platform polls, the more information it needs to write to the database in each polling cycle.

Internal virtual machine vs public cloud

For larger SolarWinds Platform deployments, running the SolarWinds Platform database on an internal virtualized SQL server might cause performance issues. 

To deploy your SQL Server in a public cloud, make sure the cloud instance meets the database requirements:
 

 

What affects the SQL Server performance on internal virtual machines?   

Host Operating System

The SQL Server manages its own space in the database files itself, it reads from and writes directly to the disk. Most other applications let the operating system (OS) control their reads to the disk, through a cache.

When you place the SQL Server in a virtual environment, the virtual machine itself is on an external OS. This external OS controls the disks, and so the SQL Server writes could be cached by the host OS without the SQL Server’s knowledge. This might delay writes to the disk in your virtual environment.

Disk space shared by VMs

In virtual environments, the disk space provided to one VM is carved from a larger array that is used for other VMs. If your SQL Server is installed on a VM, its performance could be affected by other VMs attempting to read/write to the same disks.

Step 4: Review further references 

SolarWinds Documentation:

Third-Party References: