[dba-SQLServer] SQL Server compression

jwcolby jwcolby at colbyconsulting.com
Mon Sep 5 12:30:01 CDT 2011


The other day I populated the second socket of my (AMD) server with another 8 core chip.  With cores 
to spare I decided to give compression a whirl.  I am finding a lot of stuff out there now saying 
that it reduces the I/O a ton and that if you are not cpu limited then it makes the database faster 
because more stuff fits into memory.  Basically the objective is to get as much of the stuff that is 
being actively used to stay resident in memory.

One immediate benefit is that it cuts the total size of my databases on disk roughly in half.  This 
is a good thing because I keep the major players on SSD which is expensive.  Keeping them compressed 
means that I have more room on the SSD.

When I got into this business in October 2004 I had servers with AMD single core 3.8 ghz and 4 gigs 
or RAM running x32 windows and sql server.  I was trying to run count queries on 100 gb databases 
and while it worked, it was... not fast. I would run counts that took a half hour.

Through knowledge acquired on these lists I rebuilt my database tables, learned indexing, and made 
the databases much more efficient.

Seven years later I have 16 cores and 32 gigs RAM running X64 windows and SQL server.  A year ago I 
bought 32 gigs of DDR3 1300 registered ECC ram for $1000.  Today I ordered another 32 gigs for $400.

It is incredible to me that using brute force and ignorance, I can now keep multiple largish 
databases entirely in memory.  While I have no benchmarks recorded to compare yesteryear to today, I 
am doing things in minutes or even seconds that would have taken me a half hour or even hours seven 
years ago.

These are exciting times.

BTW I found something called GeekBench which I ran on my machine.  It is the only thing I have found 
that is a reasonable cost ($13) to give me numbers to compare to others.  My Geekbench number is 
~13,500, and it pointed out to me that I am currently running the server with 1/2 of the memory 
"bandwidth" I could be getting (the biggest reason I am adding more).

I had 4 DIMMS and a single processor.  The processor has a 4 port memory controller.  When I added 
the new processor I redistributed the memory so that each chip had 1/2 of the memory (2 dimms) but 
in doing so I "crippled" the memory controller.  The price of memory is dropping like a rock so now 
seems like a good time to top up.  It will be interesting to see what filling out the memory 
controller does to the numbers.

I "retired" my previous server to be my VM server, and it just occurred to me that I can run 
Geekbench on that to get a comparison of my previous server against my current server.  My previous 
server is an AMD quad core with 16 gigs of RAM and the geekbench on that is 7469.



-- 
John W. Colby
www.ColbyConsulting.com


More information about the dba-SQLServer mailing list