Network Management
SQL - Performance optimization and best practices
This article provides various points and other useful information to enhance the performance of SolarWinds Orion products with SQL database.
First published date
Last published date
Overview
Product section
Resolution
The following sections provide recommendations and information about configurations that can disrupt your SQL Server performance.
Configurations that are not recommended
We do not recommend the following setups because they can cause performance issues:
- SANs depending on setup
- If utilizing physical we do not recommend speed under 10K RPM
- RAID 5/6 (especially with NTA)
- SQL Server installed on the same server as the Orion Platform with NTA
Best SQL Server Disk Setup
- 2 x Raid 1 disk (mirroring) for operating system
- 2 x Raid 1 disk (mirroring) -- place pagefile here and also can be used for applications and ad hoc stuff
- 4 x RAID 1+0 Striping and mirroring for database files (2 partitions 1 for data file and 1 for log file)
For large multi-poller deployments of the Orion Platform, the SQL Server performance quickly becomes the governing factor in the overall performance of the system. It is critical that the database server be able to handle the large quantities of data that can be generated by a multi-poller, multi-module Orion Platform implementation. It is therefore desirable to optimize the SQL system for write performance:
- RAID 1+0 with 4 or more spindles. The more spindles it has, the better the performance will be.
- Use of a battery backed-up write-back caching controller. This will greatly improve write performance regardless of RAID level.
- High RPM hard-drives.
- Store the data and log files on separate disks.
- Maximize the available RAM.
- Utilize a 64-bit based architecture.
Why Not RAID 5?
- Because of the way that SQL Server works with the Orion Platform there are a lot of read/writes going on between the database and the Orion server.
- RAID 5 is best used in an environment where there are not a lot of read/writes. With the Orion Platform there are enormous amounts of very small pieces of data being written to the database.
- The Orion Platform will perform a large number 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 Platform deployment will lead to a high-traffic database (high throughput). The more elements the Orion Platform polls, the more information it will need to write to the database each polling cycle.
- Raid 5 can become a bottleneck, especially if NTA is installed. The best option is a RAID 10 configuration.
- Stick with RAID 1 or 10 or some other RAID that offers higher disk I/O performance.
- RAID 10 is the recommendation for SQL databases. The Orion Platform will function on any form of RAID, but due to the way that RAID 10 works (a mirrored stripe set) it provides greater performance and fault tolerance at the same time.
- Ideally you would have two RAID 10 'sets', one for the data files (.MDFs) and the other for the log files (.IDFs).
Top 5 Ways to Improve SQL Performance
| #5 | Add more RAM. It doesn't really matter how much you have. Adding more will almost always help. Be sure that your SQL instance and OS are capable of consuming the additional RAM , and if not, make it so. |
| #4 | Just say no to RAID 5. It's great for application servers but horrible for database servers where I/O performance is important. |
| #3 | Place the data and log files (.MDF and .IDF) on separate logical drives and separate channels or controllers. |
| #2 | Unless your SAN is optimized for high I/O vs. large I/O, stick with a locally attached disk array. |
| #1 | Buy disk controllers with battery backed-up write-back cache. The more the better -- but at least 256 MB. |
Five simple tips to address storage performance issues
The following THWACK post provides additional tips:
Orion Platform Optimization guide
This includes:
- A short discussion of Orion and SQL
- Measuring and monitoring SQL
- Optimizing SQL for the Orion environment
- RAID choices RAM, disk, SSD, drive types, memory management
- Orion server performance
- Additional resources
SQL Server and Virtual Machines
Our findings so far is that 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 can’t 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 the Orion Platform itself will perform a large number 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. Because of this, a large Orion Platform deployment will lead to a high-traffic database (high throughput). The more elements the Orion Platform polls, the more information it will need to write to the DB 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 disk. This is different than most other applications in that the OS will control how the other applications read to disk, through a cache. However, once you place SQL Server in a virtual environment, the VM itself ‘sits’ on an external operating system. This external operating system controls the disks – so the SQL Server writes could be cached without 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 VM’s 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.
This blog addresses the question of virtualizing SQL:
https://blogs.technet.microsoft.com/...irtualize-sql/
(© 2010 Microsoft, available at https://blogs.technet.microsoft.com, obtained on February 25, 2019.)
The conclusion is summarized here:
“While virtualization has many benefits, it is not the right solution for every case. For very high throughput applications and database applications that must be highly scalable, virtualization may not be the best choice. In these scenarios, running multiple instances of SQL Server would be a better choice.”
Tips & Tricks
https://thwack.solarwinds.com/commun...ql-performance
Detecting CPU and Memory Pressure On A SQL Server
https://erikdarling.com/quickly-detecting-cpu-and-memory-pressure-on-a-sql-server/