Configuring any SQL Server – the forgotten homework…

By -

Often developers just toss SQL Server on any handy drive and forget about it.  Later when they are asked to provide guidance on installing SQL Server they provide zero often, or slip into pro-forma “mom’s apple pie” statements.

 

Today, I decided to do a little homework and downloaded SQLIO tool from Microsoft to get some numbers on my drives. I have two RAID-0 logical drives, one RAID-1 logical drive, 1 SSD Drive and an external SATA drive. In general, SATA drives are developer grade and definitely not production grade – production grade is SCSI drives.

 

So running just one test (using random access writes) on the drives, I got the following interesting table. I opted for random writes because it tends to bring out the nasty.

 

Drive Latency Average Latency Max Ios/Sec MBs/Sec
SSD (Intel) 32 414 966 121
Mirror @ 5200 RPM 207 515 153 20
Strip @ 7200 RPM 57 649 552 69
Strip @ 7200 RPM 63 841 500 62
eSata @ variable RPM 69 816 457 57

The RAID controller was the same for all drives.

 

The thing that amazed me was the sluggishness of the Mirrored Drive (yes, I checked, it is 0% fragmented – so that is not it). I expected it to be better then the eSata drive, instead it’s 1/3 the write rate, 3x the latency.

 

The mirrored drives happened to be the drives that came with the machine… which actually suggests that if you want performance, get your own drives!

 

The stripped drives performed 15% better than the eSata, so stripping paid off – but not as much as I would have expected.

 

Now the SSD did double the performance of the hard drives for writes, which is what I was expecting – doing writes is much slower for SSD then reads where 3 to 4x improvement is seen.

 

Drive Model Cache
SSD (Intel) INTEL SSDSA2M160G2GC  
Mirror @ 5200 RPM WDC WD10EARS-00Y5B1 32 MB
Strip @ 7200 RPM WDC WD100FAEX099Z3A0 64 MB
Strip @ 7200 RPM WDC WD100FAEX099Z3A0 64 MB
eSata @ 5-7200 RPM ST315005N4A1AS 32 MB

 

For reference, I did some read tests:

Drive Latency Average Latency Max Ios/Sec MBs/Sec
SSD (Intel) 12 1147 2384 298
Strip @ 7200 RPM 44 1372 710 89
Mirror @ 5200 ROM 101 1326 314 39
eSata @ 5-7200 RPM 74 1489 426 53

Now the SSD is on a 300 MB/sec SATA bus … so 298 is about as good as it can get! The read time for Stripped is twice that of Mirrored – which follows theory, but not quite twice of the eSata.

So what do these numbers means? Well, forget putting TempDB etc on my mirrored drives. One of the stripped drives will be for TempDb and the other one for TempDb log.

 

There are more experiments that I will likely do, using 4 drives for stripping instead of 2. Trying RAID 5 (which I expect to be worst than Mirror), etc. None of these conclusions can be safely generalized. You have to do the grunt work of testing each drive/configuration – and expect some surprises for technology has gotten so complex, nothing is obvious any more.

 

Remember, be a fact-based developer!

Comments

Post a Comment

Popular posts from this blog

Yet once more into the breech (of altered programming logic)

By -
Once upon a time, long long ago, I wrote 8-bit programs in Tiny-C with absolute addressing being the only reality.  Then we had Borland Turbo-C with relative addresses and the joys of relative assembly jumps. Enter Windows and the joys of locking and unlocking memory blocks that you must allocated and manage yourself. Then came Visual Basic which evolved into .Net CLR and decreasing awareness of what is happening under the hood.   With VS2010 including F# and enhanced parallelism in .Net4, we actually have to go retro a bit – back to the world of having to understand the hardware. CPU speeds are no longer increasing – I have an old laptop from six+ years ago that ran at 3.2 GHz. This year I updated one of my machines off-the-shelf from Costco.com, an I7-920 with 4 cores and hyper-threading (8 CPUs appears in Task Manager) and 12 GB of DDR3 RAM. This week I ordered from Costco a Phenom II X6 (Yes – 6 REAL CORES) for one of my daughters.  This CPU will (according to YouTube demos) o
Read more

Simple WP7 Mango App for Background Tasks, Toast, and Tiles: Code Explanation

By -
Image
This is a continuation of this post , explaining how the sample app works. Caution : This sample isn’t meant as an example of best patterns and practices. If you see something in the code you would never do, then don’t. Solution File Organization The app has three Visual Studio Projects: the app, the agent, and the shared code library between them. One of the great things about Mango is that now the built-in Visual Studio Unit test functionality is available. By separating out the meat of the work into the SampleShared project, I can also focus my unit testing there. SampleApp1 The app has a MainPage, a ViewModel, and the WMAppManifest.xml file modified to note my ExtendedTask of BackgroundServiceAgent. The Agent is extremely simple with a call to the shared library, an update to the Tile, and a call to pop up Toast. The meat of the work is in the SampleShared library. The AgentMgr manages the background agent : add, remove, find, run now. It is only called f
Read more

How to convert SVG data to a Png Image file Using InkScape

By -
Image
Introduction The project required the need to put the visual pie chart on a web page on the client and in a Pdf file created on the server. I eventually choose to use a Telerik Kendo DataViz Chart control on the client, and use a PNG file on the server for the Pdf file. This blog post will explain the process and code to convert the client-side pie chart Svg data to a Png file, and provide a download to the working code. Prerequisites You need to have a trial version of the Telerik Kendo library installed in order to make the sample code work. You also need InkScape installed. InkScape is the application that does the actual conversion. You will need to change the Web.config appSetting of “ExeDirectoryForInkscape” to represent your own installation location. Any SVG Data Will Work The pie chart was created using the Telerik Kendo DataViz chart control. While I name a specific control, as long as you have access to the SVG data, the same conversion process on the server will
Read more