Tuesday, December 22, 2009

Why 64 Bit SQL Server is faster

So this morning we had an interesting talk about whether or not Microsoft SQL Server x64 was any faster than x32. You better believe it is. Since 2005 Microsoft has been presenting videos like crazy on the speed advantages of x64. They are pretty dramatic. You can view a nice webcast on this very subject here.

Whilst certain lazy cretins on StackOverflow.com continue to question whether there is anything to be gained out an upgrade, let's understand why there is a tremendous speed boost in going to 64bit SQL Server.
  1. You move 8 bytes of data around per operation or cycle, not 4. That is twice as much raw binary moving per cycle. A lot of SQL Server actions simply move bytes from point A to point B. A lot of things SQL does involves picking up an arbitrary bucket of bits and throwing it down somewhere. In the simplest scenario, given a fixed number of cycles, moving twice the number of bits per cycle automatically doubles the speed of movement, cutting movement time in half. Of course, other bottlenecks--such as IO bottlenecks--can express themselves, reducing this gain.
  2. Your pathetic 32 bit chip and software still works like an x87. Consult any good CPU historians on the subject of the Intel 8087 math coprocessor. The bomb blast you will get from them will border on hysteria. The Intel 8087 is quite possibly the worst floating-point engine ever to make it to market. Most will tell you it is the slam-dunk worst, full-stop. Inside the body of your nice shiny 32bit CPU still v-fibs the heart of a wretched x87 coprocessor. In the 32 bit world, this is the only floating point unit guaranteed to be there for you, if you presume a i80486 processor or better. What does this mean? It means all your nice money, interest, passing percentages, quarterback efficiency indexes, Loan-to-Value calcs, etc. run slower than they might otherwise. I don't even want to talk about something real-number intensive like 3d.
  3. AMD64 has a nice new shiny math coprocessor. When they created the x64 instruction set, AMD decided to dispense with the x87 architecture completely. They replaced it with a beautiful architecture entirely predicated on the SSE2 instruction set. Everybody likes SSE2/SSE3. It is pretty straight-forward, and you get kick-ass performance. I don't know of a better way to do Floating Point. This means all your loan-to-value, interest, index rate, real profit, liability calcs execute faster; a hell of a lot faster.
  4. You can use a ton of RAM, and you better. This is the target Microsoft has pounded with countless artillery shots. Every last time you hit the RAM cache for data rather than the hard disk, you get a speed-up of 100:1. This is no joke. High-speed electronic RAM is several orders of magnitude faster than a mechanical disk drive. When you go over 8GB of RAM, which is cheap and easy to do, it is possible for many corporations to cache entire mission critical databases in RAM. It is the easiest way in the world to speed up your transactions 100 fold and save a ton of wear and tear on your disk array. Everything gets written through to your RAID array in a lazy fashion, after the sparks fly.
  5. Your price per tpmC drops through the floor. If you consult the Transaction Processing Performance Counsil, you will discover the lowest cost solutions always involve x64 code. I don't give a damn whether it is Windows, Linux, Oracle, or Microsoft: The solution is always 64 bit, and not 32 bit. Have a look here and prove me wrong.
So there you have it friends. If you haven't gone 64 bit, you are just plain wrong. Don't give me any shit about Visual Fox Pro either.