SMS, DMS and Automatic Storage
22 October 2008
I often get the question about when SMS tablespaces should be used and when DMS tablespaces should be used. We also now have automatic storage that will pick SMS or DMS for you. There has been much written about what these tablespaces are, but in this article I will share my opinions of when to use each. First let me give very brief definitions of these terms:
|
SMS |
System Managed Storage tablespace.
|
|
DMS |
Database Managed Storage tablespace
|
|
Automatic Storage |
With Automatic Storage all tablespaces are still either SMS or DMS, but you let DB2 choose which type. When you create the database, you tell DB2 which file system or set of file systems into which you want all tablespace data placed. |
Automatic Storage
For many new databases I would now recommend automatic storage. With it you get both the ease of administration of SMS and the performance of DMS. Starting in DB2 v9.1, Automatic Storage is the default when you create a new database. As I said in the table above when you create a database with automatic storage you specify a storage path or set of storage paths. As you create tablespaces, you do not specify containers. DB2 will place the tablespace in the storage path(s) that you specified at database creation time. If you specified two or more storage paths, then DB2 will stripe your tablespaces across all of them. In general DB2 will choose SMS tablespaces for temporary tablespaces and DMS for all others and will use LARGE tablespaces by default for all DMS tablespaces. Another nice thing about automatic storage is that you don't need to do redirected restores if you want to make copies of your databases in different places. Automatic Storage is free with all editions of DB2 9.
Whether or not you choose to use automatic storage, it is good to know some circumstances when you may want to choose SMS or DMS. As I said, automatic storage is often an excellent choice, but there are circumstances where other options are better. Even when Automatic Storage is the best, it is still good to understand the underling SMS and DMS models that it uses. You should also note that you can create tablespaces that are not under Automatic Storage control in Automatic Storage databases.
General Considerations
For many applications, DMS tablespaces do provide somewhat better performance for data tablespaces. However, SMS, almost universally provides the same or better performance for temporary tablespaces. This general statement applies to all versions of DB2/LUW including v9.5. That being said, many shops use SMS for everything because they make the trade-off in favor of ease of administration. The less I/O you do against the tablespace the less pronounced the performance differences are between SMS and DMS. DMS has the advantage of allowing you to place your indexes and LOB data in a different tablespace than your data. With DMS you can also add containers on the fly, where once an SMS tablespace is define no more containers can be added.
Prevent File System Filling
In many circumstances I prefer DMS just in case there is a run-away application that starts filling one table. In SMS the table will be allowed to consume all of the space in the file system(s) where the directory(s) are located before an out of space error is returned to the application. In this scenario in DMS the application just fills one tablespace file(s) and quits, leaving free space in my directory and all other tablespaces. When you allow DMS tablespaces to auto-extend, you need to specify an appropriate MAXSIZE value on the tablespace to prevent this problem. Specifying MAXSIZE should be done whether you are using Automatic Storage or not. I always do everything that I can to thwart Murphy's Law.
Index and LOB placement
Due to various considerations such as performance or utility scheduling, you may want to put your indexes and large objects into a different tablespace than your normal data. For nearly all types of tables, you must use a DMS tablespace to do this. When a table is in SMS the index and large object data must be placed in the same tablespace as the data. The only exception is when you create a range partitioned table in an SMS tablespace. With range partitioned tables in SMS tablespaces, you can put your indexes into another tablespace.
Test Databases
SMS is a great choice for small test databases. This is especially true when you have several small test databases that you wish to put in one file system. When this is the case you frequently do not know which one will get the most data. SMS tablespaces are only allocated a few pages when they are created. As data is added only the tablespaces that have tables that have data added grow and then only grow in accordance with the amount of data added. So this way you don’t have to know which database will grow – each database and tablespace within that database will just consume space from the file system as it is needed. You can also do this when using Automatic Storage and specifying small initial sizes for tablespaces, but SMS will still generally make more efficient use of shared space.
Unknown Table Growth Rates
SMS may also be a good choice when you have several tables where you expect significant growth, but the amount of growth is unknown among them. The first option that you should consider would be to put all of these tables in one DMS tablespace so that each table can claim the space it needs, while not “trapping” unused space in a tablespace for a table that does not grow as expected. However, there may be times when it is not feasible to put them all into one tablespace. In this case SMS is a good choice if you put all of the tablespaces in directories in the same file system or file systems. Again, the tables can consume the space as needed without allocating space that will not be used.
Large Tablespaces
DB2 v9.1 introduced a new type of tablespace called the “Large” tablespace that allows for much larger tablespaces. Indexes for tables in “Large” tablespaces have a larger address or (Row ID – RID) that points to the rows in the table. Only DMS tablespaces can be declared “Large”. This means that if you want larger temporary tablespaces, they must be DMS. Starting in V9.1 the default of “Large” is used when creating a DMS tablespace. Maximum tablespace size is still determined by page size and I show those limits below. For partitioned databases, this maximum size is the maximum size in each partition. Also, the new large tablespace lets you have more than the maximum of 255 rows per page that regular tablespaces allow. These and other limits are defined on the SQL LIMITS page.
Maximum Tablespace Sizes (Per Database Partition)
|
Page Size |
Large Tablespace |
Regular Tablespace |
|
4K |
2 TB |
64 GB |
|
8K |
4 TB |
128 GB |
|
16K |
8 TB |
256 GB |
|
32K |
16 TB |
512 GB |
Further Reading
To read more about tablespaces in general and the meaning of “DMS”, “SMS”, and “Large” tablespaces please see chapter 9 of the Data Servers, Databases and Database Object Guide. You can also read more about Automatic Storage in chapter 3 of this manual.