Submit a ticketCall us

AnnouncementsChange Is Inevitable

Get valuable help when it comes to tracking and monitoring changes. SolarWinds® Server Configuration Monitor (SCM) is designed to help you: detect, track, and receive alerts when changes occur, correlate system performance against configuration changes, compare server and application configuration against custom baselines, and verify application and system changes.

Learn more.

Home > Success Center > Network Performance Monitor (NPM) > NPM - Knowledgebase Articles > SQL Server - Performance optimization and best practices

SQL Server - Performance optimization and best practices

Updated February 14th, 2017

Summary


For large Orion Platform installations with multiple polling engines, the SQL Server must be able to handle large quantities of data that can be generated by the Orion 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 spindles  (© 2017 Wikimedia Foundation, Inc., available at https://en.wikipedia.org, obtained on February 24th, 2017.). 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 hard-drives.
  • Separate the data, tempdb (© 2017 Microsoft, available at https://msdn.microsoft.com, obtained on February 24, 2017.),  and Transaction Log (© 2017 Microsoft, available at https://msdn.microsoft.com, obtained on February 24, 2017.) files on separate LUNs.
  • Maximize the available RAM.
  • Utilize a 64-bit based architecture.

 

More Information


  1. Identify performance issues
    1. Symptoms
    2. Tool 1: SolarWinds Database Performance Analyzer
    3. Tool 2: Activity Monitor
  2. SQL Server Configuration
  3. SQL Server and Virtual Machines
  4. References

 

How to spot Performance Issues

Symptoms

Search for the following symptoms:

  • Web Performance will be slow
  • Long Running Queries in Orion log files
  • SQLClient Timeout errors in Orion log files

Use SolarWinds Database Performance Analyzer

 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 (© 2017 Microsoft, available at https://msdn.microsoft.com, obtained on February 24, 2017.) 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 (© 2017 Microsoft, available at https://msdn.microsoft.com, obtained on February 24, 2017.) and right-click the SQL Server in Object Explorer to launch Activity Monitor

Processes

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 if suspended

Hostname - Hostname executing the process

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

activity mon.png

**This may show as Solarwinds for all servers, if so use the following: Solarwinds SQL Connection String
 

Suspended Processes

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 Blocking of other processes which will show in the BlockedBy column.

Common Wait Types (© 2017 Microsoft, available at https://msdn.microsoft.com, obtained on February 24, 2017):

  • 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) (©  2006 – 2017  SQLAuthority.com, available at https://blog.sqlauthority.com, obtained on February 24th, 2017.)—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.
Resource Waits

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 then 1000 ms (Recent Wait Time) is considered normal.

resources.png

 

The following SQL Queries (© 2017 Refsnes Data, available at https://www.w3schools.com, obtained on February 24th, 2017.)

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

 

Data File I/O

This view displays the response time for all of the SQL database files (© 2017 Microsoft, available at https://msdn.microsoft.com, obtained on February 24, 2017.). This is a useful tool for troubleshooting disk I/O latency. Response times greater than 10 cause slowness, greater than 100 cause problems.

data file io.png

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 (© 2017 Wikimedia Foundation, Inc., available at https://en.wikipedia.org, obtained on February 24th, 2017) (SAN/NAS) Arrays are supported if throughput is high enough. See SCSI, Fiber Channel (© 2017 Wikimedia Foundation, Inc., available at https://en.wikipedia.org, obtained on February 24th, 2017).

Why not RAID 5?

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

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 (© 2017 Microsoft, available at https://technet.microsoft.com, obtained on February 24, 2017.)

Properties

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

  Setting Recommendation
checkbox.gif

Maximum Degree of Parallelism (MAXDOP)

Server > Advanced

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

Cost Threshold of Parallelism

Server > Advanced

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

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.
checkbox.gif

Data files initial size and autogrowth

Database > Properties > files

Data files - autogrowth 1024 MB
checkbox.gif 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
checkbox.gif Initial size and autogrowth for tempdb
  • Data files - initial size 1024 MB, autogrowth 1024 MB 
  • Log file - initial size 2048 MB, autogrowth 1024 MB
checkbox.gif Recovery mode

Use SIMPLE if possible. FULL requires further measures.

checkbox.gif Query Optimizer fixes
Database > Properties > Options
  • SQL 2016 and 2017
    Query Optimizer Fixes = ON
     
  • SQL earlier versions:
    Enable trace flag 4199 at service level: 
DBCC TRACEON (4199);
checkbox.gif

Compatibility level

Database > Properties > Options

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

 

SQL Server and Virtual Machines

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

The Orion 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 Orion Platform installation thus leads to a high-traffic database (high throughput). The more elements Orion polls, the more information it needs to write to the database in each polling cycle.

Internal virtual machine vs public cloud

For larger Orion Platform deployments, running the SolarWinds Orion 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 on 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.


Learn more

References

Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.  You elect to use third party content at your own risk, and you will be solely responsible for the incorporation of the same, if any.

 

Last modified

Tags

Classifications

Public