JoelBlogs - Joel Jeffery's Microsoft 365 Blog

Microsoft 365, SharePoint, Teams and Office 365 Architecture, Development, Administration and Training

  • Home
    • Sitemap
  • Articles
    • #SPThingADay
    • SharePoint Online
      • SharePoint Online – Drag and Drop and Large File Uploads
    • SharePoint 2016
    • SharePoint 2013
      • Content Database Changes to the AllUserData Table
    • SharePoint 2010
      • Administration
        • Disable CRL Checking
        • Excel 2010 & PowerPivot
        • Limits & Thresholds
        • PeoplePicker AD Errors
        • Recycle Bin Behaviour
        • Renaming a Server
        • Service Pack 1
        • Unattended Installs
        • Uninstall All SharePoint 2010 Solutions via PowerShell
        • User Alert Management
        • Virtualised SharePoint
        • Visio Stencils for Administrators
      • Development
        • Audience Membership Workflow Activity
        • Base Types, Lists & Content Types
        • BCS & Offline Sync
        • Debugger Skipping Lines
        • Development Laptop Spec
        • Enabling JavaScript IntelliSense
        • Event Receivers & Deployment Jobs
        • FavIcons & SPUrl
        • Google Maps Sandbox Web Part
        • Group By Content Type for List Views
        • Locale Stapler / Master or Default Locale
        • Removing Default Editor Parts
        • Sandbox Embedding Resources
        • Solution Sandbox Introduction
        • SPPersistedObject
        • Restoring Deleted SPSites in SP1
        • SPWebConfigModification 1
        • SPWebConfigModification 2
        • STSADM copyappbincontent vs. Install-SPApplicationContent
        • Workflows for Beginners
        • Workflow InitiationData Seralizer
    • SharePoint 2007
      • Alternate Access Mappings
      • Excel Services
      • Excel Services UDFs & Excel Client 2007
      • Experiences from the Field
      • InfoPath & Forms Server
      • Kerberos & SSRS
      • Records Management
      • Web Application Service
      • WSS vs MOSS
  • Training
    • SharePoint Admin Links
  • Downloads
    • Summary Slides for PowerPoint
    • CodePlex Projects
      • Audience Membership Workflow Activity
      • Google Maps Sandbox Web Part
      • Group By Content Type in List Views
      • Locale Stapler / Master or Default Locale
      • SharePoint Outlook Connector
  • Hire Me!
    • MCP Transcript
    • Résumé/CV

SharePoint 2013 Content Databases and the AllUserData Table

February 15, 2013 by Joel Jeffery

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:

SharePoint 2013 AllUserData Table

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:

tp_ColumnSet from AllUserData Table

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.

Wide List

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.

Filed Under: SharePoint 2013 Tagged With: SharePoint 2013, SharePoint Architecture

SharePoint Documentation Kit (SPDocKit)

September 4, 2012 by Joel Jeffery

I need to tell you about something fantastic.

SharePoint Documentation Kit – SPDocKit Review

I’ve been using SPDocKit from Acceleratio for about 6 months now, on and off. One of the services we offer at JFDI Phoenix is our SharePoint 2010 Farm Health Check, and this involves a lot of boring navigation through Central Admin and some gnarly PowerShell to gather farm insight. Collecting settings information by hand is tough and time consuming.

The value-add for customers comes from what I can deduce from their configuration – what problems they’ll see; whether their platform will scale; whether it will be secure enough.

SPDocKit helps me gather the vast majority of the information I need, and formats it into a very detailed, very useful report in Word format.

Here’s just a snippet from the table of contents:

SPDocKit Table of Contents

It goes on to produce a detailed inventory of each server in the farm:

Farm Overview

It even builds a Visio-style diagram showing the farm topology. OK, I know it looks daft when I run it on a one-server farm, but it is vastly more impressive on a multi-server farm!

Farm Topology

It also identifies and lists:

  • Services on server, per server
  • Site topology (site collections per web application)
  • Web applications
  • Self-service site creation settings
  • Alternate access mappings
  • IIS settings
  • Site collection usage
  • Quota templates
  • Service applications
  • Detailed settings for each application
  • Databases used
  • Permissions
  • Associations to web applications and proxy groups
  • Content databases, usage and quotas
  • System settings, including email and SMS settings
  • Features and solutions
  • Sandbox configuration
  • Monitoring and reporting
  • Security
  • Administrators
  • Managed accounts
  • Service accounts
  • Products and versions
  • Scheduled tasks
  • Server information
  • Hardware / cores
  • Installed programs and hotfixes
  • Drives and free space

If this wasn’t enough, it also lets you record all this information and monitor changes over time.

More Features!

And there’s more! The SPDocKit application itself has a pretty useful UI. You can browse the web application and site structure without needing to generate documentation:

Site Explorer

It also lets you walk the permissions of your sites. Very useful if you’ve been breaking inheritance and you want to know who has access to what. Having said that, SharePoint’s built-in “Check Effective Permissions” button is still invaluable to debugging permissions problems.

Permssions Explorer

There’s also a Detailed Permissions report, but this is still a very interactive tool:

Detailed Permissions

There’s even best practices analysis built in:

Best Practices

Although obviously not exhaustive, it does give a very good start for a more detailed analysis of a farm.

There’s also a Scheduled Tasks viewer:

Scheduled Tasks

Conclusion

Whilst SPDocKit is not magic, it certainly saves me a great deal of time on SharePoint farm health checks. The licensing is pretty cool also. You can either buy it for an individual farm, of have a yearly subscription as a consultant. If you’re using the latter, the setup exe lets you run it on a server without leaving it installed afterwards.

Filed Under: SharePoint 2010 Tagged With: SharePoint 2010, SharePoint Administration, SharePoint Architecture

« Previous Page
Next Page »

Joel is a full-stack cloud architect who codes. He is a Microsoft Certified SharePoint Online, SharePoint Server and Azure specialist and Microsoft Certified Trainer.
He has over 20 years' experience with SharePoint and the Microsoft .NET Framework.
He's also co-founder of Microsoft Gold Partner JFDI Consulting Ltd. Read More…

Recent Posts

  • Microsoft Flow Tip #1 – Word Templates and Hiding Empty Repeating Sections
  • SharePoint PowerShell Tip #1 – Select-Object and FieldValues
  • Popular Misconceptions – Microsoft Teams relationship with SharePoint
  • Course: Microsoft 365 Certified Teamwork Administrator
  • Audience Targeted Searches in Modern SharePoint Online
MCT 2020-2021
Microsoft Teamwork Administrator Associate
Joel's Acclaim Profile
Joel's Microsoft Profile

Tags

Administration Architecture Certification Cloud Development freetraining Information Architecture intranets MCP Microsoft Microsoft Architecture Microsoft Azure microsoftsharepoint migration Mobile Development MOSS Office 365 office365 Office 365 Permissions PowerShell SaaS SharePoint SharePoint 2010 SharePoint 2010 Training SharePoint 2013 SharePoint Administration SharePoint Administrator SharePoint Architecture SharePoint Developer SharePoint Development sharepointia SharePoint Online sharepointonline SharePoint Search SharePoint Training SharePoint Videos Silverlight SOA SPThingADay TechEd 2007 Training Videos Windows Phone 7 WSS

Copyright © 2022 Joel Jeffery, SharePoint Architect