Home > Data Warehouse, Grade of the Steel, Modeling, OLTP, SQL Server > Grade of the Steel: Introduction

Grade of the Steel: Introduction

There are a lot of things I want to say on this blog, so I do apologize in advance for jumping a bit between subjects. I will shortly write the next installment of my data modeling series – but before that, I want to let you know about some exciting new tests that I am working on in my copious amount of spare time Smile.

FusionIO has kindly granted me access to what can only be described as a beast of a machine! Here is the configuration I am looking at in my terminal session:

  • An HP DL980G7 box with 8 Nehalem-EX CPU at 8 Cores each (16 in HT mode). Clock speed of 2.0 GHz.
  • 22 PCI Slots, 7 which are currently filled with FusionIO cards (with at least 2 ioDIMM in each slot)
  • A good pile of RAM (still not sure how much we will run on, but the box is capable of 2TB)
    Along with Tom Scribner of FusionIO (one of the guys who wrote the FIO Windows driver) – I will be doing some tests on this monster box. This is one of the largest scale-up systems out there – and the biggest scale-up x64 box that HP currently ships.
    We still don’t know exactly how many IOPS we can squeeze out of this, but it is looking promising. You know you are heading the right way with your tuning, when both tuners are looking at 100K Random IOPS, at 64K blocks, consistent 0.6ms latency – and say: “Nice, but not impressive”. This is basically a system with no bottlenecks.

Here are a few screenshots:

imageimage

Notice the little green light in the top right corner, with the number 22 next to it.That is the number of IODimms.

Some of you may have participated in the “Crappy Code Games” during SQL Bits and seen some pretty amazing results. But now, I want to up the bar and try “Great Code Game”. I want to put SQL Server to the test and discover the grade of the steel of basic operations that we all need to do when designing databases.

Here are some of the questions I plan to answer:

  1. How fast can we run the ETL World Record on this? (I hope to hit 4 minutes for 1TB if the CPUs will do it)
  2. How many singleton INSERT / sec can this box sustain?
  3. How many single row UPDATE statements / sec can we do?
  4. How fast can you table scan fact tables?
  5. How fast can you do backup?

Ad 1) Since I don’t have any blade servers to use for ETL clients, I will run BULK INSERT commands. This will be the (current) ultimate test of a scale-up ETL load

Ad 2,3) This will of course depend a lot on the table design and file layout. If you have seen me present at SQLBits, you will know that I have some unorthodox design techniques to boost scale. I plan to put them through extreme testing and write more about them. For all you OLTP people out there – this is going to be important if you design for scale.

Ad 4) I have no clue how fast this box will run for table scans. But I know that the PCI bus can do at least 36GB/sec.

Ad 5) Again, I have no clue what the result will be and what bottlenecks I might hit. Only testing will show.

I am quite confident that the standard “out of the box” designs you generally see recommended will not scale on this box. But I am equally confident that there are ways to break through the scale roof and shut up the voices that would claim that SQL Server does not scale. Be prepared for some surprising design hints.

The box also has Hyper Threading (HT). Time allowing, I will see how well the claim that the new HT actually boosts scale will measure up to actual testing. Also, I know a lot of people have asked me about NAND devices and what can be done with them. Feel free to suggest ideas for tests in the comment section.

Anyone else have a box like this out there? I challenge you to compete and explore with me.

Let the games begin!

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s