AllUserData Table Changes in SharePoint 2013 Content Databases

Has anybody else noticed something’s changed with the schema of the Content Database in SharePoint 2013? The AllUserData table has changed.

Look, but don’t touch!

Try a SELECT * FROM AllUserData and see what you get:

First of all, what’s happened to columns [nvarchar1], [nvarchar2] (skip a few) [nvarchar64]? How about [int1] to [int16]?

They’ve all been combined into a fragment of Xml, and stored in a single column [tp_ColumnSet].

Let’s zoom in and have a look at one of those Column Sets:

That’s interesting.

Wide List Issues

Firstly, this means “wide lists” are going to be potentially faster than under 2010. A “wide list” in SharePoint 2010 was one that had so many columns that it would cause an INSERT or UPDATE statement to affect more than 8000 bytes of row data.

To work around the problem, when SP2010 works out the column you’re about to add to a list could cause more than 8000 bytes to be written, it would instead create an additional row to hold the new columns. SharePoint used the [tp_RowOrdinal] column to number each of the rows accordingly for a wide list.

We expect SQL Server to start table locking the AllUserData table as the number of row locks approaches 5000. Wide list behaviour would potentially cause SQL Server to have locky behaviour at one-half as many rows (or one-third, or one-quarter – depending upon how many list columns and therefore SQL rows).

Now it’s all stored in one big column, that particular problem is going to go away.

I was bored, so I added 66 text columns to a list. It was all stored in one row, in that one column.

However, the [tp_RowOrdinal] column still exists in the SharePoint 2013 content database, and I’m not sure why.

SharePoint 2013 Content Databases and Sparse Columns

SQL 2008, 2008R2 and SQL 2012 all support “sparse columns” and “column sets“. These are SQL technologies that allows a row to grow beyond the usual 8K-per-row limit (up to 2GB of data when expressed as Xml). Under the hood, the AllUserData table still has [nvarchar1] through to [nvarchar64] and beyond! In fact there are 262 nvarchars, 1000 bits, 550 datetimes, 550 floats, 750 ints, 262 ntexts, 262 sql_variants, 350 uniqueidentifiers and 2 geographies (in a pear tree). (Thank you to Lior Gal for pointing me in the right direction!)

See the MSDN reference article on SharePoint 2013 AllUserData table design here: http://msdn.microsoft.com/en-us/library/hh625524.aspx.

But why didn’t these show up in SQL Management Studio? This is behaviour by designer to prevent poor performance when selecting form wide tables.

AllUserData Performance Issues in SharePoint 2013

Three general observations:

1. For list sizes below 5000 SQL rows, lists that don’t use SharePoint indexed columns will probably be slower.
2. For lists greater than 5000 SQL rows, unless we’re using SharePoint column indices in our query, SharePoint list throttling should kick in, and terminate the query before it gets started.
3. For most list use cases, it is still massively important to use SharePoint column indices. But if you do decide to switch off throttling for a list, performance should be faster than under SharePoint 2007.

So, in summary: even in SharePoint 2013, make sure you have suitable indexed columns.

Technorati Tags: SharePoint 2013, SharePoint Architecture