Optimizing SQL Server
After you understand the important metrics to measure, you can address areas in SQL to maximize performance. Again, as SQL is very storage intensive, this section focuses a great deal on SLQ storage enhancement. Following are some of the basic recommendations for using SQL with Orion. Many of these can be found in the Administrator’s Guide for NPM.
| Item | Recommendation |
|---|---|
| Dedicated SQL Server | Except for small NPM installations (SL250 or lower) without NTA we recommend a dedicated hardware server be used. |
| RAID Array | RAID 10. The more drives the faster the maximum read and write rates. RAID 1 alone offers no performance increase over a single spindle. Do not use RAID 5 or 6. |
| Hardware RAID Controller | The RAID controller should always be hardware based and server quality. |
| Disk Speed | 15 KRPM (15K). While the cost of 15K drives is higher, this will partially be offset by the need for fewer spindles. |
| SQL Version | SQL 2008 is supported. 64-bit SQL should be used for all Orion installations over SL 250 or smaller Orion license levels with NetFlow. |
| RAM | SL 500 and below – 2GB, SL 2000, 5GB+, SLX – 20GB+. The more RAM the better. For SL 2000 and above set the minimum SQL memory to eighty to ninety percent of the total server memory, but allow sufficient total memory outside this setting for the OS. |
| CPU | 3 GHz, dual core processor or better. This will work for all size licenses. Do not use less than a 2 GHz processor for any Orion SQL server. |
You might wonder, is RAID 01 is a good choice for your SQL database storage? RAID 01 and RAID 10 both yield the same number of effective spindles for any given even number of drives greater than 4. RAID 10 is actually shorthand for RAID 1+0, and RAID 01 is actually RAID 0+1. If you think of the RAID arrays in this way the difference is easier to understand. RAID 1 is mirrored drives. Raid 0 is striped drives. For a RAID 10, 4 drive array the 2 primary drives are mirrored with the 2 secondary drives, then the mirrored pairs are striped to primary and secondary striped sets. With Raid 01 the drives are striped in 2, 2 drive sets, and then those sets are mirrored to the secondary 2 striped drives.
The important thing to remember is that in RAID 10 you can lose one or more drives out of either striped set and the mirror of that drive will continue. In RAID 01 if you lose one drive from a striped set the set is dead. If you lose one drive from both sets, the storage array is dead.
This is shown in the following graphic.
SQL server can be further optimized using several of the following methods.
Enable Advanced Windows Extension (AWE) for 32-bit SQL
AWE allows MS SQL enhanced access to memory under 4GB. Users enabling AWE may see dramatic improvements in MS SQL 32-bit server performance. AWE is not used for 64-bit servers. Enabling AWE depends on the version of Windows server you are running.
Separate Drives for Orion, SQL log (temp) and SQL data
Separating the temp files from the data files can help improve performance. A typical installation of this type looks like the following:
- C: drive – Orion and OS. 2 drive RAID 1
- E: drive – SQL log files. 4 15K drives RAID 0
- F: drive – SQL data (Orion data). 6 15K drives RAID 10
The size of the drive space for each of the SQL drives can be determined by examining the current SQL database files.
Using RAMDisk® for SQL log (temp)
RAMDisk, a third-party software package, allows you to place the temp SQL files onto a logical drive that exists completely in RAM. This requires 64-bit SQL and a good amount of RAM. See the RAMDisk documentation for further requirements. This tool is very useful as it takes the most performance intensive part of SQL storage and moves them from physical disk performance levels to RAM performance levels. Physical drive (spindle) IOPS are measured in the hundreds per second where RAM IOPS are measured in the hundreds of thousands per second.
Solid State Drives (SSDs)
Solid state drives yield the IOP levels seen in RAM drives. These are becoming more common as the prices continue to drop. Unlike RAM drives, SSDs are nonvolatile, so the data written to SSDs remains after the system is powered down. SSDs are made of billions of microscopic transistors which store individual data bits by being in an uncharged state or a charged state. This works well for program files and data that is static. One issue with most SSDs is that the barrier that holds the charge within each transistor breaks down upon successive writes. When the transistor changes from a 1 to a 0 or a 0 to a 1 the charge state of the transistor changes and some erosion of the charge barrier occurs. After many write cycles, the transistor loses the ability to hold a charge and becomes useless for storage. SQL is very read/write intensive and so creates challenges for SSDs. New technologies aimed at maximizing SSD life span are coming onto the market. These tools can help high-end SSD arrays reach life expectations up to 8 to 10 years.