Secrets of the Metaverse Part 3

Parts 1-5:

1. What is the Metaverse?
2. How is the Metaverse data stored?
3. Is there a limit to how many Metaverse attributes I can have?
4. Has access to the metaverse gotten faster with recent releases?
5. How do I safely query the metaverse?
6. Added (Aug 5 2015): How Many Metaverse Attributes can I have?

Many times people wonder how many attributes they can create in the Metaverse Designer tool.

The answer is confusing because … it depends.

Per my calculations there is a hard limit for single-valued, non-reference attributes of 502. Now to show you my work (in school my math teachers always insisted that I show my work).

Remember from Part 2 that the Metaverse consists of 5 tables:

When you create a new single-valued, non-reference attribute using the Metaverse Designer tool FIM modifies not just data but the table structure of three of these tables: mms_metaverse, mms_metaverse_lineagedate, and mms_metaverse_lineageguid. FIM will add a column to each of these tables. The mms_metaverse table will get a column of a datatype  based on what has been selected as the attribute data type:

Metaverse DataType

SQL Data Type

Min Size

Max Size

Comments

String (Indexable)

nvarchar(448)

2

898

String (non-Indexable)

nvarchar(max)

2 GB

If the data is small (less than 4000 bytes or whatever limit is set) and doesn’t cause the row to exceed the 8060 byte limit it will stored in the row, otherwise only pointers will be stored in the row and the data will be stored in its own set of pages (off-row).

Binary(indexable)

varbinary(900)

2

902

Binary(non-indexable)

varbinary(max)

2 GB

see the note on string non-indexable

Boolean

bit

1/8

1

If there are between 1-8 bit columns in the table 1 byte of storage is consumed, if 9-16 bits then 2 bytes, if 17-24 then 3 bytes and so on

Number

BigInt

8

8

In the mms_metaverse_lineagedate table FIM adds a new column of type DateTime which takes 8 bytes. In the mms_metaverse_lineageguid table FIM adds a column of type UniqueIdentifier which takes 16 bytes.

A row in SQL Server 2000 and beyond can only hold 8060 bytes. With SQL 2005 and beyond there is a feature called row overflow that allows for the variable length columns (like varchar) to overflow to other pages. However that doesn’t apply to fixed length data types like BigInt, DateTime and UniqueIdentifier.

So the mms_metaverse_lineageguid has two columns (row_key a bigInt and object_id a unique identifier so 24 bytes) and then one column of type UniqueIdentifier for every single-valued non-reference attribute. 8060-24 = 8036 and 8036/16 = 502.25, which rounds down to 502.

So while there may be other limits that may depend on the datatype selected, this is one limit that cannot be escaped.

http://feeds.feedburner.com/IdentityLifecycleManagerilmBestPractices