Day: January 7, 2008

Database Flash Memory Storage

Hard disks have become so huge that less than $200 will now buy you a 500GB external drive in a store. Still, flash drives are catching up in size whilst providing far better access speeds and power usage. (Dell are charging $800 for a 32GB Flash Drive for my laptop – enough to be usable)

Today, major SQL Server installations are unlikely to use flash drives, but the common belief is that this will change in the near future.

But before you start planning for these great 10x performance gains, you should spend some time to understand the underlying differences, and see that it is in fact the nature of your data reads and writes that will affect performance. It really isn’t as uniform as you might think, and it is worth to read the following paper on the subject:

http://www.cs.cmu.edu/~damon2007/pdf/graefe07fiveminrule.pdf

________________________________________

Update: Spoke too soon.. the flash drive for database systems is just around the corner

SQL Server 2005 Table Partitioning – Links

Table partitions have new and improved functionality in SQL Server 2005, and it is now actually very easy to define them.

A simple take is that partitions enable you spread table data over multiple locations.

In one scenario, your application may have 10 years worth of data in a ‘Sales’ table. If you create a partition for each year and place them each on a separate disk, you will in theory have fewer reads/writes on each disk resulting in more efficient queries.

In addition, many bulk operations can be further optimised by dropping indexes and other constraints on individual table partitions.

It should be noted that your database will have to be very large to make this a worthwhile exercise. Even then, other factors might produce little or even worse performance benefit. 

If you are interested, take a look at the following links to get started:

Partitioned Tables and Indexes in SQL Server 2005
How to Implement an Automatic Sliding Window in a Partitioned Table on SQL Server 2005
Top 10 SQL Server 2005 Performance Issues for Data Warehouse and Reporting Applications
Partition Elimination in SQL Server 2005

And for some more detail:

Loading Bulk Data into a Partitioned Table

Top 10 SQL Server 2005 Performance Issues for Data Warehouse and Reporting Applications