Should I be using table partitioning or auto tiering features of my storage array?

Question: We have an EMC array with FAST technology. Should we be using this or table partitioning to move old data to slower storage?

Answer: All together now "it depends."

Okay, so that answer probably isn't exactly what you were expecting, so let's go for a bit of a better answer. The nice thing about technologies like EMC's FAST, Dell's Compellent storage array's, etc. that do auto-tiering of data is that it works for everything no matter the application and no matter the version and edition of SQL Server. While table partitioning is great, it does require a good deal of setup and maintenance to keep it running and you have to have a good understanding of the data which will be going into the tables. Not to mention that you have to have SQL Server 2005 or higher as well as Enterprise Edition or higher.  Another plus of the auto-tiering is that it can be used with vendor build application where table partitioning may not be an option as making schema changes to a vendor database will typically result in a loss of technical support from the vender.

Related: Using Table Partitions to Archive Old Data in OLTP Environments

When using these auto-tiering technologies you simply create a LUN on the pool of disks (or move an existing LUN into the pool) which the tiering technology will be using to auto-tier the data. You then let the array do it's magic. As data is accessed more (the data becomes hot) those blocks will move up to the faster storage. As data is no longer being accessed (the data goes cold) the blocks move down to slower less expensive storage. This all assumes that the colder data isn't ever touched once it comes cold. If you need it for 8 queries a day, but you don't care how long those queries take then suddenly the auto-tiering technologies aren't going to work so well, simply because the 8 accesses of those blocks a day may be enough to trigger them being moved to the faster more expensive hardware.

At this point we probably want to look back at table partitioning. Even though this is going to be more complex to setup and we need to schedule data moving jobs to move data from one partition to another we control what disks the data is sitting on, and we can decide that older data will always be sitting on the slower less expensive disks.

Table (and index) partitioning gives us another big advantage when working with VERY large data sets that auto-tiering doesn't give us. That is partition elimination. With table and index partitioning if the query specifies the partitioned column in the WHERE clause of the query, the SQL Server "should" be able to figure out which partition needs to be looked at and only load that single partition off of the disk to find the data that it needs. When we using auto-tiering storage array technology we don't have this functionality as the SQL Server has no idea what is going on at the storage level. As far as SQL Server knows it is just one table on a single traditional disk.

This leads me to another potential problem with auto-tiering technologies. Because we are now putting data on slow, inexpensive disks we may see random IO waits in SQL Server that we can't explain. Assume we have a table with 10 years worth of data in it, and we run a single nightly query that looks at the last 3 months worth of data. If only the current few days worth of data is on the fastest storage the next couple of weeks worth of data is in the second tier and the rest is on the slowest storage when this nightly query runs we could see the much hated IO taking longer than 15 seconds to respond message in the SQL ERRORLOG file. But there wouldn't be any real way to tell why this was happening without actually looking at the page contents using DBCC PAGE to see that the data was old and probably sitting on the slowest storage.

With table partitioning we know exactly which LUN the database is on, and we can see where that LUN is sitting within the array and our storage admin can (hopefully) help us see that it's on slower storage (which is hopefully were it was requested to be put) and that is why we are seeing the slow performance errors and we can then decide if we want to ignore those errors or do something about them.

I hope that this helps you make some decisions about the configuration of your storage platform and/or your table partitions,
Denny