THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Joe Chang

Build your own server with Supermicro motherboards

I used to build white box servers because there were usually enough spare parts left over from upgrade projects. (management did not see the need for non-production systems, so I arranged for there to be spare parts). But since 2002 or so, I have been buying Dell PowerEdge servers for my own test environment. This was in part because of the hassle of troubleshooting connections to multiple SCSI HDDs, was it the cable or connector?

In the Nehalem/Westmere time frame 2009-10, I decided to step down from a 2-socket system of the previous generation (PowerEdge T2900) to a single socket system, the T110 II. In principle, this was because single socket systems had become powerful enough for me to demonstrate important characteristics I need for my papers, such as generating 2.4GB/s in IO bandwidth. In practice, it was also because I sit in the same room as the servers, and the T2900 had noisy fans while the T110 II was whisper quiet.

Processors - Intel Xeon E3-1200 series v3, Haswell

For the current generation processor, the Intel Xeon E3 v3 based on Haswell, Dell decided to focus on pre-built ready to ship systems rather than build to order systems. The only E3 processor option in the Dell T20 is the E3-1225 3.2GHz nominal and 3.6GHz Max Turbo. This system has 1 PCI-E x16 gen3 slot and 1 x4 G2 slot.

Graphics is not normally important in a server, as it usually resides in a server room and is accessed via remote desktop or even completely remote administration. The previous generation T110 II used an old Matrox G200eW 8MB graphics (based on a 1998 design?) that only supports normal video resolutions (1280x1024?, ok I am getting 1600x1200 on the T100II). The new T20 with E3-1225 has the Intel P4600 graphics.

For some strange reason my T20 would only power up with 1 DIMM socket populated. I opened a case with Dell Technical Support, but they seem to have lost track of the ticket. I wonder if the people are still there. Or have they been outsourced?

So I thought that I would give building my own server another try. I got the Intel Xeon E3-1275 v3 3.5GHz nominal and 3.9GHz Max Turbo ($350 versus $224 for the 1225, but less than the $552 price tag of the 1285). The 1225 to 1275 processors have the P4600 graphics, which support 3 displays.

Supermicro X10SAE Motherboard

My motherboard of choice is the Supermicro X10SAE with PCI-E 1 x16 and 1 x8 gen3 slots. The E3 v3 only has 16 PCI-E gen3 lanes. The Supermicro motherboard has an ASMedia Switch (ASM1480) that redirects 8 lanes from one slot to another slot so that all 16 lanes connect to a x16 slot if that slot has a x16 adapter and the x8 slot is unpopulated? Otherwise, both slots are x8?

If the ASM chip is a PCI-E expander, then in principle, both the x16 and x8 slots have all lanes always connected, its just that half of the x16 lanes are shared with the x8? The ASMedia website describes the ASM 1480 as a multiplexer/demultiplexer. But there is not a detailed document. I would hope that in the situation of simultaneous traffic, priority is given to the x8 slot, as the x16 slot should direct traffic to its x8 dedicated lanes? but there is no protocol to support this mode?

What I like about Supermicro is their deep lineup of server class motherboards with almost every conceivable slot arrangement. I recall that when Intel spent a huge amount of money to focus on one motherboard for an entire processor class, not optimal for any particular purpose.

Display - Dell P2815Q 3840x2160

I also got the new Dell P2815Q monitor currently $699. It had priced higher, but Dell offered a second monitor for a discount, so I bought two. This has a 28in diagonal, and maximum resolution of 3840x2160 at 30Hz. The low refresh rate at maximum resolution would not be suitable from gaming. Neither does the P2815Q have the glossy display popular in home entertainment.

But it is perfect for viewing SQL Server execution plans. At standard zoom (80%) I can see 17 execution plan operators horizontally across the 3840 pixels. Connecting two of the monitors would display 34 operators? Of course, it might be more important to have dual monitors in portrait mode, but I do not know where to get the stands. (per Dave, the P2815Q does rotate)

I might give the UP2414Q at $1149 a try. The UP3214Q at $2799 is too expensive for my needs. The other large screen with high-resolution is the U2713H at $999 supporting 2560x1440. I have two XPS 27in AIO with apparently the same 27in display?

Storage - LSI 9361 PCI-E gen3 12Gbps SAS

My preference would be to plug in 2 PCI-E gen 3 SSDs capable of the full (or nearly) x8 slot bandwidth of 6.4GB/s, at least on the large block read side. This is to avoid a jungle of SATA power splitters and cables inside the system. However, for some reason, there are no PCI-E gen3 SSDs?

There are PCI-E gen 3 RAID controllers with either 2 x4 12Gb/s SAS interfaces and also some with 24 (x1) 6Gbps ports. There are no 12Gbps SSDs so if I used the standard 2 x4 ports at 12Gbps, I would have to find some enclosure with 12Gbps capable expanders, which will of course escalate the cost.

All of this is rather unfortunate for building cost optimized high bandwidth storage system. NAND chips currently operate at up to 333MHz. This means 20 channels could saturate the full PCI-E gen 3 x8 bandwidth, even though we would probably use 24 for RAIN and general downstream over-configuration. At 32GB per channel (4 x 64Gbit die) and 24 channels, the raw capacity is 768GB would be a very inexpensive storage yet capable of 6GB/s read? Previous generation PCI-E NAND controllers supported 16 and 32 channels.

The standard SATA-NAND controller has 8 channels. This was a good choice when NAND was 100MHz. Now this means we have too much (but unusable) downstream bandwidth.

The new NVMe NAND controllers might offer the option of connecting to either 6Gbps SATA or x2 PCI-E gen 3, which would be 1.6GB/s, but I am not sure when we would have supporting infrastructure.

The upcoming (now) LSI SandForce SF3700 can interface to either PCI-E gen2 at x2 in the 3719 & 3729 models or x4 (3739 & 3759) and SATA at 6 Gb/s (all models) (SF3700 datasheet). There are 9 channels on the NAND side.

SQL Server 2014

I just installed SQL Server 2014 RTM on this system. I notice that SQL Server 2014 does not show the Parallelism (Repartition Streams) operator, same with the Bitmap. The Parallelism (Distribute Streams) and (Gather Streams) operations are still displayed.

Below is part of a SQL Server 2012 execution plan with both the Parallelism (Repartition Streams) and Bitmap operations.

Natural

In SQL Server 2014, the execution plan for the same query does not show these two operations.

Natural

I imagine that the parallelism and bitmap operations are still there, just no displayed because they do not contribute to understanding the execution plan, while wasting valuable screen real estate.

Of course, having the option to reduce the spacing between operations without reducing the display font would be very valuable. I do not think the Program Manager for SSMS looks at complex query plans to understand why this would be a valuable feature?

Wish

I would like to find someone willing to build a system with the Supermicro X9DRX+-F 2-socket motherboard with 10 PCI-E g3 x8 slots, filling most of these with storage controllers. This would be massive overkill as I am not sure SQL Server can consume 20GB/s from 4 controllers, let alone 40GB/s from 8 controllers.

Interpret this as I do not want to pay for 2 12/15-core processors, 16-32x16GB DIMMs, 8 controllers, and 64 SSDs out of my own pocket.

Addendum

I have ordered a LSI 9361-8i PCI-E gen3 - 12Gpbs RAID controller that I will use in the Supermicro system w/the Xeon E3 v3 (Haswell), although I have no means of using the 12Gbps SAS signaling rate. If anyone has a 12Gbs expander board, I would appreciate it (there is not a pressing need for SSDs to support 12Gbps, we would just like to connect 12 or SSDs at 6Gbps to the 2 x4 SAS 12Gbps ports.

I also ordered OCZ Vector 150 SSDs. I will probably mix these with the original Vectors that I already have. In my previous generation system, the Dell PowerEdge T110 II, I had the LSI 9260 controller initially with a mix of OCZ Vertex 3 and Crucial m4 SSDs. The Crucial m4's would occasionally show as offline on the LSI RAID controller, but there was nothing wrong with the m4 when attached to a SATA port. Eventually I replaced the m4 with OCZ Vectors, and since then all 8 SSDs have worked fine with the LSI 9260.

The recently announced SanDisk CloudSpeed SSDs are also of interest, but I suspect these will be OEM only products.

Plextor has a PCI-E gen2 SSD for a x2 slot (x4 connector?), rated for 770MB/s. Tom's Hardware says its a M.2 SSD on a PCI-E board. If is the case, then I think the correct SSD product for now are PCI-E boards on which we can plug in 1-4 or perhaps even 8 M.2 SSDs.

The M.2 form factor supports x2 PCI-E lanes. A simple board could wire up to 4 directly the lanes in the PCI-E slot. A more flexible mode would have a PCI-E expander, so that the number SSDs (each PCI-E x2) can exceed the slot width (x4, x8 or even x16).

2014-05-06

I am seeing just under 4GB/s from the LSI9361 with a mix of 4 OCZ Vector 150 and 4 older OCZ Vertex 3 Max IOPS SSDs. Technically the SSDs are capable of over 500MB/s each, but in an array (2 actually, the 4 Vectors in one, and the 4 Vertex 3 in the other) with SQL Server driving IO, that's pretty good. I got 2.4GB/s with the previous generation LSI 9260. Presumably the controller could not drive the full 3.2GB/s PCI-E gen 2 x8 limit?

The Dell P2815Q connected to my Supermicro X10SAE motherboard's display port connector does operate at the full 3840x2160 resolution, but not when connected to the HDMI connector. I do not know if it is possible to have 2 displays at 3840x2160 with just the Supermicro motherboard, or if I need to get a separate video card?

Published Wednesday, April 02, 2014 2:47 PM by jchang

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Dave said:

That P2815Q monitor comes with a stand that swivels to portrait...just try twisting it.

April 2, 2014 10:42 PM
 

George said:

Nice build with Supermicro WBox. Still looking for those non-fusion PCIe-3 SSDs.....!!

April 3, 2014 9:03 AM
 

Joe said:

Wow! the P2815Q does rotate. thanks Dave. Dual monitor it is. However, it looks awkward in portrait mode. I might either stack one on top or just go super wide. Will see what kind of execution plans I am looking at.

April 3, 2014 10:13 AM

Leave a Comment

(required) 
(required) 
Submit

About jchang

Reverse engineering the SQL Server Cost Based Optimizer (Query Optimizer), NUMA System Architecture, performance tools developer - SQL ExecStats, mucking with the data distribution statistics histogram - decoding STATS_STREAM, Parallel Execution plans, microprocessors, SSD, HDD, SAN, storage performance, performance modeling and prediction, database architecture, SQL Server engine

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement