Submit a ticketCall us

Have You Auto Renewed? If not, you're missing out.
The SolarWinds Renewal Program comes with a host of benefits including the most recent product updates, 24/7 technical support, virtual instructor-led training and more. Experience all of this with the convenience of Auto Renewal, and never worry about missing any of these great benefits. Learn More.

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

SQL Server - Performance optimization and best practices

Updated February 14th, 2017

 

Summary


This article describes information on SQL Server (© 2017 Microsoft, available at https://docs.microsoft.com/, obtained on February 24, 2017.) performance optimization and best practices and can be sent to customers if needed.

 

For large multi-poller installations of Orion, the SQL Server performance quickly becomes the governing factor in the overall performance of the system. It is critical for the SQL Server to be able to handle large quantities of data that can be generated by a multi-poller, multi-module Orion implementation. It is therefore desirable to optimize the SQL system for write performance:

  • RAID (© 2017 Wikimedia Foundation, Inc., available at https://en.wikipedia.org, obtained on February 24th, 2017.) 1+0 with 4 or more spindles. The more Disks the better the performance will be.
  • Use of a battery backed-up write back caching controller. This improves write performance regardless of the RAID level.
  • 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


How to spot Performance Issues

Symptoms

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

Using 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 will show you activity in real time. Open Activity Monitor (© 2017 Microsoft, available at https://msdn.microsoft.com, obtained on February 24, 2017.) and Right Click on the SQL Server in Object Explorer to launch Activity Monitor

Processes

Expanding Processes will show you all active SQL Processes currently running on the server. There are several useful columns here for troubleshooting.

 

activity mon.png

 

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

Wait Type - Why the process is Suspended

Blocked By - SPID Blocking the process if suspended

Hostname - Hostname executing the process

 

Each of these columns can be filtered by clicking on the column header drop down.

Suspended Processes

Processes go into Suspended state when there is not enough resources available to complete the task. The wait time counter will show how many milliseconds the process has been waiting and the Wait Type will show you 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.

 

*more info on Suspended process: Performance tuning wait queues doc download

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 1000ms (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 will show you 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 then 10 cause slowness, greater than 100 cause problems.

data file io.png

SQL Server Configuration

  • Having more files in the filegroup help the SQL server to 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, a RAM disk or an SSD disk can be used. 
  • An SSD disk can be used for data files, but it is not effective for the transaction log where sequential access is most important. 
  • 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 (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.)

SQL Server and Virtual Machines

Based on our investigation, running an Orion database on a virtualized SQL server will cause performance issues for larger installations. Unfortunately, because SQL Server is a Microsoft product, we cannot provide solid rules for getting the best performance from this, but we can provide some general guidelines from experience.
 
The first thing to consider about this environment is that Orion itself will write a large amount of very small writes to the SQL database. When using SQL to insert data to a table, this requires a few reads before the write operation can be done – due to this, a large Orion installation will lead to a high-traffic database (high throughput). The more elements Orion polls, the more information it will need to write to the database in each polling cycle.

Next, consider how SQL Server works – it will manage its own space in the database files itself, reading and writing directly to the disk. This is different to most other applications in that the OS will control how the other applications read to disk, through a cache. However, once you place the SQL Server in a virtual environment, the Virtual Machine (© 2017 VMware, Inc, available at https://www.vmware.com, obtained on February 24th, 2017.) itself sits on an external OS. This external OS controls the disks – so the SQL Server writes could be cached without the SQL Server’s knowledge, by the host OS. This in itself would delay writes to disk on your virtual environment, without taking into consideration that the virtual environment would also be affected by all other VMs attempting to read/write to the same disks – typically in virtual environment the disk space provided to one VM is carved from a larger array and that array would be used for other VMs also.

The following articles and documentation can be used as reference and for more information on how this works:

 

Properties


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