Monthly Archives: May 2010

So, what book do you recommend?

Another excellent blogger here on SSC, Jeffery Yao, has posted up an interesting idea http://www.sqlservercentral.com/blogs/jeffrey_yao/archive/2009/04/20/database-administration-literature-criticism.aspx .

As a PASS chapter leader I often get asked the question, What book do you recommend for SQL Server? Several years ago you could recommend a single book to cover ALL SQL Server topics in depth.

Today you really need to ask specifically what topic you are trying to learn about, and if you are just starting, a seasoned pro trying to learn more or updating your skills to the next platform.

When I start looking for a book on a topic I start with authors I know and see if they have written on the subject. If that fails me I usually fall back to Amazon and read reviews there. But that is rarely the last step I have to take.

At some point I drive down to the old brick and mortar store and spend hours walking, reading and eventually buying something.

There is another option I’m trying to leverage, book giveaways at the user groups. I have given away a few books at the chapter meetings but this year I’m trying to be a little more proactive about getting training materials together to help out those looking for work or may be soon.

One of the things I plan on doing is getting everyone that gets a book to write a little review and maybe do a little presentation about it so we can share it with the group, and the SQL Server community as a whole.

I’m also trying to read every book that comes through the door, I don’t know if I’ll be able to before they go up as giveaways, but my intention is to try. The reason is two fold, one I can brush up on stuff I don’t use every day and help write reviews for the members that choose to participate in the review process.

So, the list of authors I read and recommend come in two flavors, technically sound and great communicators. I’ve gone back to these authors and their books time and time again. I’m sure there are a ton of other good authors, some I have read and missed and others that have written on technologies I personally don’t use regularly.

I will follow up this list with some specific books for 2005/2008 in the near future, covering what is on my book shelf that I have read and recommended to others.

Until then… The Short List:

Joe Celko

Kalen Delaney

Kimberly L. Tripp

Itzik Ben-Gan

Kevin Kline

Louis Davidson

Brian Knight

Robert Vieira

SSD, The Game Changer

I’ve often described SQL Server to people new to databases as a data pump.

Just like a water pump, you have limited capacity to move water in or out of a system usually measured in gallons per hour.
If you want to upgrade your pumping systems it can be a two fold process, the physical pump and the size of the pipes.

Our database servers also have several pumps and pipes, and in general you are only as fast as your slowest or narrowest pipe, hard drives.

To feed other parts of the system we have resorted to adding lots and lots of hard drives to get the desired IO read/writes and MB/sec throughput that a single server can consume.
Everyone is familiar with Moore’s law (Often quoted, rarely understood) loosely applied says CPU transistor counts double roughly every 24 months. Hard disks haven’t come close to keeping up with that pace, performance wise.

Up until recently, hard drive capacity has been growing almost at the same rate doubling in size around every 18 months (Kryder’s Law). The problem isn’t size is speed.

Lets compare the technology from what may have been some folks first computer to the cutting edge of today.

Time	Circa 1981	Today	improvement
Capacity	10MB	1470MB	147x
HDD Seeks	85ms/seek	3.3ms/seek	20x
IO/Sec	11.4 IO/Sec	303 IO/Sec	26x
HDD Throughput	5mbit/sec	1000mbit/sec	200x
CPU Speed	8088 4.77Mhz (.33 MIPS)	Core i7 965(18322 MIPS)	5521x

*These are theoretical maximums in the real world you mileage may vary.

I think you can see where this is going. I won’t go any further down memory lane lets just say that some things haven’t advanced as fast as others. As capacity has increased the speed has been constrained by the fact hard disks are just that, spinning disks.

So, what does this little chart have anything to do with SSD? I wanted you to get a feel of where the real problem lies. It isn’t capacity of hard drives it’s the ability to get to the data quickly. Seeks are the key. SSD’s have finally crossed a boundary where they are cheap enough and fast enough to make it into the enterprise space at all levels.

SSD compared to today’s best 15k.2 HDD from above.

	HDD	SSD	improvement
seek times	3.3ms/seek	85μs/seek	388x
IO/Sec	303 IO/Sec	35000 IO/Sec	115x
Throughput	1000mbit/sec	25000mbit/sec	2.5x

So, in the last few years SSD has caught up and passed HDD on the performance front by a large margin. This is comparing a 2.5” HDD to a 2.5” SSD. This gap is even wider if you look at the new generation of SSD’s that plug directly into the PCIe bus and bypass the drive cage and RAID controller all together. HOT DOG! Now we are on track. SSD has allowed us to scale much closer to the CPU than anything storage wise we have seen in a very long time.

Since this is a fairly new emerging technology I often see allot of confused faces when talking about SSD. What is in the technology and why it has now become cost effective to deploy it instead of large raid arrays?

Once you take out the spinning disks, the memory and IO controller march much more to the tune of Moore’s law than Kryder’s meaning cost goes down, capacity AND speed go up. Eventually there will be an intersection where some kind of solid state memory, maybe NAND maybe not, will reach parity with spinning hard drives.

But, like hard drives not all SSD’s are on the same playing field, just because it has SSD printed on it doesn’t make it a slam dunk to buy.

Lets take a look at two implementations of SSD based on MLC NAND. I know some of you will be saying why not SLC? I’m doing this to get a better apples to apples comparison and to put this budget wise squarely in the realm of possibility.

Intel x25-M priced at 750.00 for 160GB in a 2.5” SATA 3.0 form factor and the Fusion-io IoDrive Duo 640GB model priced at 9849.99 in a PCIe 8x single card.

Drive	Capacity in GB	Write Bandwidth	Read Bandwidth	Reads/sec	Writes/Sec	Access Latency (seek time)	Wear Leveling (writes-erase/day)	Cost per Unit	Cost per GB	Cost per IO Reads	Cost Per IO Writes
IoDrive Duo	640	1000MB	1400MB	126601	180530	80μs	5TB	$9849.99	$15.39	$0.08	$0.06
X25-M	160	70MB	250MB	35000	3300	85μs	100GB *	$750.00	$4.60	$0.02	$0.22
Improvement	4x	14x	5x	4x	55x	~	10x	13x	4x	4x	-4x

* This is an estimate based on this article http://techreport.com/articles.x/15433. Intel has stated the drive should be good for at least 1 petabyte in write operations or 10,000 cycles.

Both of these drives use similar approaches to achieve the speed an IO numbers.They break up the NAND into multiple channels like a very small RAID array. This is an over simplification but gives you an idea of how things are changing. It is almost like having a bunch of small drives crammed into a single physical drive shell with it’s own controller a mini-array if you will.

So, not all drives are created equal. In Intel’s defense they don’t plan the X25-M to be an enterprise drive, they would push you to their X25-E which is an SLC based NAND device which is more robust in every way. But keeping things equal is what I am after today.

To get the X25-M to the same performance levels it could take as few as 4 drives and as many as 55 depending on the IO numbers you are trying to match on the IoDrive Duo.

Wear leveling is my biggest concern on NAND based SSD’s. We are charting new water and really won’t know what the reliability numbers are until the market is aged another 24 to 36 months. You can measure your current system to see how much writing you actually do to disk and get a rough estimate on the longevity of the SSD. Almost all of them are geared for 3 to 5 years of usability until the croak.

At a minimum it would take 10 X25-M drives to equal the stated longevity of a single IoDrive Duo.

Things also start to level out once you factor in RAID controllers and external enclosures if you are going to overflow the internal bays on the server. That can easily add another $3000.00 to $5000.00 dollars to the price. All the sudden the IoDrive Duo really starts looking more appealing by the minute.

What does all this mean?

Not all SSD’s are created equal. Being constrained to SATA/SAS bus and drive form factors can also be a real limiting factor. If you break that mold the benefits are dramatic.

Even with Fusion-io’s cost per unit it, is still pretty cost effective in some situations like write heavy OLTP systems, over other solutions out there.

I didn’t even bother to touch on something like Texas Memory System’s RamSan devices at $275000.00 for 512GB of usable space in a 4U rack mount device the cost per GB or IO is just through the roof and hard to justify for 99% of most SQL Server users.

You need to look closely at the numbers, do in house testing and make sure you understand your current IO needs before you jump off and buy something like this. It may be good to also look at leveraging SSD in conjunction with your current storage by only moving data that requires this level of performance to keep cost down.

If this article has shown you anything it’s technology marches on. In the next 6 to 12 months there will be a few more choices on the market for large SSD’s in the 512GB to 2TB range by different manufacturers at ranging prices making the choice to move to SSD even easier.

Recently, Microsoft Research in early April published a paper where they examined SSD and enterprise workloads. They don’t cover SQL Server explicitly but they do talk about Exchange. The conclusion is pretty much SSD is too expensive to bother with right now. To agree and disagree with them it was true several months ago, today not so much.

The fact that the landscape has change significantly since this was published and will continue to do so, I think we are on the verge of why not use SSD instead of do we really need it.

With that said please, do your homework before settling on a vendor or SSD solution, it will pay dividends in not having to explain to your boss that the money invested was wasted dollars.

A little light reading for you:

SSD Primer
http://en.wikipedia.org/wiki/Solid-state_drive

James Hamilton’s Blog
http://perspectives.mvdirona.com/2009/04/12/WhereSSDsDontMakeSenseInServerApplications.aspx

-Wes

You thought SQL Server ate up memory…

Often I tell clients better to much memory than too little. This can be applied to any database engine essentially. If your data set is growing over time you will end up using any memory that is not consumed today.

I’m here to tell you I don’t think it is the biggest consumer of memory in the Microsoft software catalog, There is a new champion! Introducing in this corner SQL Server 2008 x64, and his opponent Exchange 2007 SP1!

It isn’t even a contest, Exchange by a knock out.

The Exchange team have made some major changes in the core engine to blow the lid of how much memory it will use.

One of the things I remind people of from time to time is that Exchange is also a database of sorts and shares some of the common performance bottlenecks that SQL Server has. As a matter of fact, I saw the magic of sector alignment for file systems flogged on the Exchange side of the house long before it became more common knowledge on SQL Server. Now they have leapfrogged us again.

I live and breath SQL Server but I also dabble in other Microsoft stuff time to time mostly out of need or curiosity. This particular instance, I was setting up exchange for my private business.

Basically what they have done is make Exchange a 64 bit only application and lifted any memory restrictions on it what so ever. So, if there is 8GB on the server it will start up and gobble up that memory like it was cotton candy and happily beg for more. This was kind of a shocker to me, a SQL Server administrator who is use to having boxes with 32GB of ram or more for SQL Server and not see it utilized until the database engine has determined that it needs a particular piece of data cached in memory.

Where Exchange differs it assumes, from what I can glean, that it will need EVERY piece of data (i.e. peoples mail) at some point so it might as well load as much as it can now.

Now I can hear you saying “So what? Exchange should run on it’s own server anyway.” and you would be right, kind of. I host my enterprise stack on a single machine running Hyper-V Server. Realize this machine only has to support about 10 users so I sized it at 2 quad core Xeons, and 16 GB of ram initially, knowing I could grow the memory as needed or even move virtual machines off onto a new server with very little pain. Microsoft and others are touting this as the way of the future, no better way to learn about it than by doing.

I assumed since I was only running a few mail boxes that Exchange wouldn’t need all that extra ram and I could give it to my SQL Server instance. Boy, was I wrong. So, I gave it the minimum 2GB it states in the requirements. I soon discovered that was like running Vista on 512MB of ram, sure you could do it if you didn’t mind going and getting a sandwich every time you clicked the mouse button.

I thought to myself “WOW, this thing is a real pig!” I looked at the size of all my data stores, a paltry 758MB, and wondered why 2GB wasn’t enough. As I type this I’m still not sure but I found a work around. Where in SQL Server you can easily tell it don’t dominate the memory by setting the min and the max available to it Exchange has no easy way (in the GUI) to do this. I eventually found a registry setting for how much memory the store (database engine) would take and set it to 1GB. And wouldn’t you know it, Exchange runs just fine that way. It doesn’t page to disk. It isn’t slow to send or receive mail. It didn’t cause the end of western civilization as we know it.

Finally, after years of telling people to get to be good friends with your Exchange admin.

They have more in common with you than you realize and may be able to help work with your SAN teams or your server admin teams on getting what you need for I/O or other hardware for your poor abused SQL Servers.

I can now tell the Exchange folks that they need to look at how SQL Server handles memory and data caching, you are running a mostly read only database with forward inserting rows(mail messages) and maybe, just maybe you shouldn’t cache every little thing right up front.

I’m not sure why the went with such an aggressive memory scheme, I am glad there was something I could do about it.

Who knew you needed a 64-way HP Superdome and 256GB of ram to really see Exchange shine?

-Wes

NULL is not = to anything

One of the fun facts about SQL Server and the relation model is the whole concept of three valued logic. Now I’m not breaking any new ground here I am just touching on something that trips people up when they expect a result and don’t get it due to the magic of NULL’s. To be honest, I’m no exception to falling into the unknown from time to time.

Codd laid out 12 fundamental rules of what a relational database system should conform to if it is to be considered truly relational.

Rule 3: Systematic treatment of null values:

The DBMS must allow each field to remain null (or empty). Specifically, it must support a representation of "missing information and inapplicable information" that is systematic, distinct from all regular values (for example, "distinct from zero or any other number", in the case of numeric values), and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way.

This rule, above all others has probably caused me the most heartburn over the years.

I’ve spent more time that I like to admit reading papers and books from pioneers of the relational model, E.F. Codd, C.J. Date. One of the great debates that carried on until the passing of Codd was over NULL and three valued logic in general. To give you an idea of how big and problematic NULL’s are, It’s been put forth that maybe we need to get rid of them all together or move deeper into the rabbit hole and make it 4 valued logic breaking NULL into different kinds of place holders for unknown.

Understand that every piece of data falls into a domain of some sort. 0 is a integer type. ‘’ is a empty string. NULL isn’t in any domain, or value type. It doesn’t represent something at all it is a place older period.

I’ve heard 3 valued logic described as yes/no/maybe but that isn’t accurate, it is true/false/UNKNOWN.

So the only logical thing that can happen to UNKNOWN is unknown. What’s even worse is UNKNOWN technically can’t be equal to UNKNOWN or NULL = NULL.

How do you know they are equal if they are both unknown?

For example:

select 1 where NULL = NULL

returns nothing since NULL can’t be equal to anything including NULL we don’t get a NULL back or the 1 back we tried to select.

select 6227 * 453 / 238 + NULL

returns NULL

which makes since on the surface to almost everyone I work with.

select NULL / 0

returns NULL

To some folks this is confusing in a traditional programming since anything divided by zero gives us an error of cannot divide by zero.

Since NULL is the place holder for UNKNOWN there is no way to evaluate the statement other than UNKNOWN or NULL!

This must also carry through for string manipulation as well.

For example:

select 'here' + 'is ' + NULL

returns NULL.

Again it is the old how can you concatenate something to the unknown problem.

Now with all this in our little busy heads we finally think we understand the problem in it’s fullness, but we don’t (or I always don’t).

Where things can get really sticky is in any kind of aggregate situation SUM(), AVG(). Generally, all aggregations have a NULL elimination step built into them.

So lets say we have a table that looks like this:

Col001	Col002
100	100
200	200
300	300
NULL	0

(this isn’t an article on table design so don’t sweat the lack of a key or the duplicate data in both columns)

create table myNumbers
(
    Col001 int,
    Col002 int
)
go
insert into myNumbers (Col001,Col002) VALUES (100,100)
insert into myNumbers (Col001,Col002) VALUES (200,200)
insert into myNumbers (Col001,Col002) VALUES (300,300)
insert into myNumbers (Col001,Col002) VALUES (NULL,300)

select avg(Col001) from myNumbers

select avg(Col002) from myNumbers

We get:

———–

200

Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)

———–

225

(1 row(s) affected)

What happens when the evaluation of the aggregation occurs there is no way to evaluate the NULL so that row is dropped and all the sudden you numbers look horribly wrong.

If I hadn’t put this to output to text we may have merrily trucked along and accepted that 200 was what we were expecting and not the 255 we really wanted due to the treatment of the NULL.

Since it is a warning and not an error our program won’t complain ether it will be more than happy to accept the result since it is valid one.

The only exception to this in general is using COUNT() since you can count a place holder just like you would a value type it isn’t evaluating the data held just that a row exists whether we know what is is in it or not.

I’ve only just scratched the surface of the unknown with this little post. I haven’t covered grouping or JOIN’s and I may in a later post.

My goal is simply to remind myself that dealing with NULL is never a trivial matter.

-Wes

It’s all magic to me.

Technorati Tags: SQL Server,I/O

"Any technology, no matter how primitive, is magic to those who do not understand it."

freefall

Arthur C. Clarke penned three laws of prediction

When a distinguished but elderly scientist states that something is possible, he is almost certainly right. When he states that something is impossible, he is very probably wrong.
The only way of discovering the limits of the possible is to venture a little way past them into the impossible.
Any sufficiently advanced technology is indistinguishable from magic.

The last is the most well known. I love the change that the comic strip freefall made to it and felt that was most appropriate to my day to day dealings with people and technology in general.

On a regular basis, I hear people describe SQL Server as a black box or magic box and working with it in any real depth is an art or wizardry. That simply isn’t so. It is science 100%. Not to take away from the designers and developers of SQL Server and the people that push the boundaries on what it is capable of, but it is all based fundamentally on engineering principles,math (relational algebra in particular) and the underlying technology of computers.

Once you demystify it, break it town into small enough parts you can quickly master the parts of it that effect your life in a reasonable amount of time.

One of the areas I focus on is I/O performance and SQL Server. So, I’ll be doing a multi-part post covering the entire I/O stack from how a hard disk works through SAN’s and eventually how this all effects SQL Server.

Hopefully you will find it useful.

Here is to my blogging endeavor!