SQL Server Performace on Solid State Drives (SSD)

Solid State Disks (SSDs) are devices that take advantage of the high speed nature of flash memory chips to provide fast persistent data storage. SSD’s come in a variety of forms that are suitable for different purposes. The most common forms of SSD are:

  • 2.5” disk replacements. These devices are SSD’s with the interface electronics of a standard 2.5 inch moving head disk. You commonly find them in laptops. They’re also installed in a few desktops and even a some servers. They’re made by companies such as OCZ and Intel. No special purpose drivers are needed for this type of SSD because it looks to the computer just like a fast disk. I installed a 200 GB SSD of this type when I upgraded to Windows 7. I’m very happy with it so far.
  • PCI-e boards. These devices use the same chips as other disk replacement products but put the chips on a PCI-express board. This allows the device to take advantage of the speed of the PCI-express bus to provide a higher number of I/O operations per second and more throughput than is possible when moving data through a disk interface. These devices are manufactured by companies such as Fusion-IO, OCZ, and Dolphin.
  • Rack mounted SSD devices. These devices have their own chassis and connect with the same Host Bus Adapters (HBA) that might be used to connect to a SAN. A typical manufacture is Texas Memory Systems. These systems tend to be expensive but fast replacements for SAN’s.
  • SSD enhancements to a standard SAN. Each SAN manufacturer implements SSD’s in a different way although some use 2.5” disk replacements. These give you a boost over the speed of rotating disks in the same SAN but the data still must navigate the narrow data path between the CPU and SAN.

I’ve had two chances to work with Fusion-IO boards. The first opportunity was about a year ago when Fusion-IO lent an 80 GB board to me for testing in my server. I ran a series tests on the board with SQLIO, a disk benchmarking tool, and was able to easily get it up to 450 MB/sec of throughput and 70,000 sequential I/O operations per second. Of course, the small size of each I/O needed to get the count up to 70,000 is unrealistic for SQL Server. Tests with SQL Server were also promising. With the larger I/O sizes used by SQL Server, bandwidth on the PCI-e bus became the limiting factor but I still got thousand’s of I/O’s per second.

As part of a recent client engagement I tested a 300 gigabyte board distributed by HP, but manufactured by Fusion-IO. This device is a card installed on a PCI-e Expansion Blade adjacent to the server blade. There was only one Fusion-IO board so a decision was made to test it as a location for tempdb. The expected MTBF for this board is similar to the MTBF for enterprise class rotating hard disks, around 1,000,000 hours. Device failures are still possible and the loss of tempdb was chosen as the least risky option.

The reason to use SSD cards with SQL Server is the high number of random I/O operations that can be performed with low latency and the improved throughput. Let’s look at a comparisons of LAN storage and SSD that were created using SQLIO. This first table is the performance of a 40 disk aggregate on a new NETAPP SAN. That is 40 drives in a RAID 6 configuration. You can see that for write the aggregate provide 2,337 IO/second and for read IO/s per second was 2,541. The throughput measured in megabytes per second from this test and others shows that it ranges from 280 megabytes per second up to 350 megabytes per second. From this test and others it appears that the throughput in megabytes per second is the limiting factor.

SAN Single Aggregate-40 Disks

SQLIO – Byte Size

64

128
Random Write
IOs/sec 4,443 2,337
MBs/sec 277 292
Min_Latency(ms) 0 2
Avg_Latency(ms) 3 6
Max_Latency(ms) 420 75

Random Read
   
IOs/sec 3,462 2,541
MBs/sec 216 317

Min_Latency(ms)
0 0
Avg_Latency(ms) 4 5
Max_Latency(ms) 1,050 1,085

(ms) is milliseconds

Here is the corresponding test with the Fusion-IO SSD board:

Fusion-IO Board/SSD
SQLIO – Byte Size 64 128
Random Write
IOs/sec 7,602 3,580
MBs/sec 475 447
Min_Latency(ms) 0 0
Avg_Latency(ms) 1 4
Max_Latency(ms) 74 72
Random Read    
IOs/sec 10,093 5,738
MBs/sec 630 717
Min_Latency(ms) 0 0
Avg_Latency(ms) 1 2
Max_Latency(ms) 47 50

The table shows that the SSD board is able to achieve 3,580 IO/Seconds for write and 5,738 IO/Second for read both on the 128 byte test. The throughput in megabytes/second is also much higher than for the disks at 447 for write and 717 for read. Again, the throughput seems to be the limiting factor. Another difference of the board level SSD’s is their short latency. The average latency for both read and write is less than an aggregate of 40 drives.

Cost can also be a factor in the decision to use SSD’s. The 300 GB SSD board costs around $14,000. That is a significant savings over using 40 SAN hosted drives for tempdb and there’s still a performance boost.

Theoretical I/O latency numbers may be a good indicator but real world examples should be more convincing. The following table compares the run times of several stored procedures from the development system, which has a heavy load. The procedures were chosen because they make heavy use of tempdb and thus are the best candidates for performance improvement. This should be considered anecdotal evidence because they comparisons are not based on processing the same data. However, the improvement did seem to hold up over time.


Procedure

Avg sec on Hard Disk (40 drive aggregate)

Avg sec on SSD

Improvement in seconds

Percent Improvement

DBX.update_ret_data

1218

526

692

56%

DBP.process_sec_data

1207

1035

172

14%

DBG.update_rates

1344

1062

282

21%

These examples show that it is possible to reduce the run time of procedures that make use of tempdb. Of course, since the Fusion-IO board only has tempdb it’s only when using tempdb that there will be any improvement. SQL Server uses tempdb for temp tables that that user code creates with names that begin with # or ##. It also uses tempdb to store table variables, table valued parameters, and xml variables. When needed tempdb is used for work tables for sorting, hashing, group by, distinct, snapshot isolation, and index rebuild operations.

Although still new SSDs are a hardware option with promise for significant speed improvements in SQL Server runtimes. There is also a potential for cost savings due to the ability of SSDs to provide a high number of I/O operations and throughput that would require a very large number of hard disks.

———————————————————————–

FAQ:

What type of chips are used in SSD products?
Today SSD’s are made with NAND flash chips but there are two types, MLC and SLC. Multi-Level cell (MLC) chips are the most common and they store two bits to each flash chip cell. You’ll find MLC chips in cameras, phones and most 2.5” SATA disk replacement products. They may wear out after being used for tens of thousands of erase/write cycles. Single-Level Cell (SLC) chips store only one bit per flash cell but last around one hundred thousand erase/write cycles.  The larger SSD’s boards are made with MLC chips.

I’ve heard that Flash disks wear out. How long does the Fusion-IO board last?
Although the 300 GB Fusion-IO board uses MLC chips from Samsung that’s only part of the longevity answer. SSD products don’t write the same disk “sector” onto the same flash chips each time the sector is modified. Instead it writes to the recently erased chips and keeps track of where each sector is located. Even if writing was taking place 100 percent of the time, 24×7, it would take several years to wear out the chips. Fusion-IO gave an example of a 320 GB board undergoing 5 terabytes of writes per day. It would last for almost 20 years. 5 terabytes is a realistic number. It equals a sustained rate of 60 megabytes per second of write 24 hours a day. Reads don’t wear out flash chips.

Can SSD’s be used in a SQL Server clustered environment?
SSD’s in LANs and External Rack Mounted SSD products can be used in clusters. However, the Fusion-IO board is internal to a single computer. Standard clustered configurations require that all databases, including tempdb, be on storage that can be shared between members of the cluster so the Fusion-IO board doesn’t qualify. There are non-standard cluster configurations (hacks that work but don’t call Microsoft) where tempdb doesn’t have to be shared and the Fusion-IO board could be used

About the Author

Andrew Novick is a SQL Server MVP and Principal of Novick Software where he develops business applications as a consultant using SQL Server ASP/ASP.NET, C#/VB.Net, and XML. He is the author of Transact-SQL User Defined Functions.
2010 marks his 39th year of computer programming, starting in High School with a PDP-8 and moving onto a degree in Computer Science, an MBA, and then programming mainframes, minicomputers, and for the last 20 years, PCs. When not programming, he enjoys coaching Little League baseball, woodworking, mowing the lawn, and the occasional movie with his wife. He can be reached at anovick@NovickSoftware.com.

where to buy accutane in hong kong rating
4-5 stars based on 120 reviews


The paravesical and pararectal spaces are opened bilaterally.This action identifies the ureters isolates the sigmoid, and ascertains the position of thesigmoidal vessels. The tea tree oil group showed antimicrobialactivity against both the mutans streptococci and other oral microorgan-isms

The tea tree oil group showed antimicrobialactivity against both the mutans streptococci and other oral microorgan-isms. Those found to be in-frame range in size from 252 bp to 1,095 bp, andwere named: MYO-1, MYO-4, MYO-8, MYO-9, MYO-11, MYO-12, MYO-18,MYO-20, MYO-23, MYO-24, MYO-25 and MYO-32

Those found to be in-frame range in size from 252 bp to 1,095 bp, andwere named: MYO-1, MYO-4, MYO-8, MYO-9, MYO-11, MYO-12, MYO-18,MYO-20, MYO-23, MYO-24, MYO-25 and MYO-32. Inthe inactive state GDP is bound to the subunitat the exposed domain; activation through thereceptor leads to displacement of GDP by GTP.The activated -subunit carrying GTP dissociatesfrom the other two subunits and either activatesor inhibits the effector. Progesterone isknown to increase colonic transit times in non-pregnant women during the luteal phase ofthe menstrual cycle when progesterone is highest. No subjective or autonomic effects areproduced in individuals who have not received anopioid. Triple antiretroviral therapy is indicated for moreeffective management of HIV in the mother to drive the viral load to < 1,000. However where to buy accutane in hong kong second and third-generation constructs presentadditional problems due to their expression of CD28 and CD137 intracellular domains thatincrease the efficiency of T cell activation. In response, lay claims may be erroneous.But this is the price paid for past failures and problems, and is a countervailingforce in democratic participation

In response, lay claims may be erroneous.But this is the price paid for past failures and problems, and is a countervailingforce in democratic participation . Circuit diagrams,photographs, line drawings, and the like, should be included for this purpose. Thefather stated that they were Jehovah’s witnesses and that blood transfusion is notpermitted in their faith. Internal mammary artery harvesting and antibioticconcentrations in sternal bone during coronary artery bypass. The treating consultant andresident informed the patient clearly about the nature of his disease and the progno-sis of a likely unfavorable outcome.

Thesecond observation was that mature CD83+ DC with CD4 T cells clustered around them wasfound in the peri-tumoural area, which is near the pseudocapsule of the tumor. Viscosity is the resistance to flowoffered by the fluid in motion. Fail-ure of neuroleptic prescriptions to up-regulate dopamineD2 receptors has been highlighted as a major contributingfactor to severe neuroleptic sensitivity (Piggott et al. where to buy accutane in hong kong 1998,1999). Inthe earlyembryo, it forms thewall ofthe primitive gut and givesrisetoepithelial portionsorlinings ofthe organsarising from theprimitive gut tube. If blood supply is insufficient where to buy accutane in hong kong blood flow must be restoredproximally to the affected region through vascular bypass and/or intravascular stenting.If such a procedure is not feasible, amputation must be considered. As with the example in Section 5.7,there are no perfect definitions of either the exposure or outcome measure.The exposed group relied on participants deciding whether they classifiedthemselves as exposed where to buy accutane in hong kong so may have included someone who had only beenamong smokers (i.e. Inaddition where to buy accutane in hong kong the body has developed some relativelynonselective transporters, like P-glycoprotein(P-gp), to deal with xenobiotics. (2005b) Fish con-sumption and cognitive decline with age in a large communitystudy. The first part of the duodenum arises from the gastricpylorus to travel toward the right and posteriorly. The interdentalincisions involve the buccal aspect of the teeth neighboring the defect and do notinvolve the next papillae. Usually adenocarcinoma (90%), which arises from the epithelium of pancreatic duct

Usually adenocarcinoma (90%), which arises from the epithelium of pancreatic duct. A community-based screening initiative to identify mothers at risk for postpartum depres-sion

A community-based screening initiative to identify mothers at risk for postpartum depres-sion. Although there is no de?nite FiO2threshold associated with oxygen toxicity, mostpediatric intensivists use a FiO2 of 0.50–0.60 asan unacceptable oxygen level during prolongedmechanical ventilation.

Selenium plays a role in the development orfunction of 30 proteins that are required for optimal endocrineand immune responses. The osteoprogenitor cell cytoplasmchanges from eosinophilic to basophilic, and a clear Golgi areabecomes evident.

(2008) Normal pressurehydrocephalus: long-term outcome after shunt surgery.

Stabilization with appropriate fluidtherapy is ideal before anesthesia. Fear appeals seek to scare audiences into quittingsmoking, for example, or to never start

Fear appeals seek to scare audiences into quittingsmoking, for example, or to never start. In the nineteenth century it was not used.Instead lethargy where to buy accutane in hong kong weakness and low mood were labelled as ‘neurasthenia’ and extreme sadnessdubbed ‘melancholia’ by psychiatrists. The nurse asks Mrs.White if she knows where she is and what day it is becausesevere dehydration may cause confusion. Test sensations of forehead where to buy accutane in hong kong cheeks, and chin (CN V).6.

(2002) Abnormality of gait as a predic-tor of non-Alzheimer’s dementia.