Introduce Users Into the UserInfo Table

When an authenticated user, whom has never visited a site collection, first visits a site there are a number of tables within the Content database which must be updated. This activity can be expensive and performance can suffer when the site collection is the root of a web application which has just been announced or released for the first time into production. In fact, I have seen first hand this behavior take down a very large SQL server upon initial launch of a large intranet site to the point we had to roll back and I have teammates which have had the same experience (hence this post).

The SharePoint Content database’s UserInfo table stores user specific information about an authenticated user and this information and really acts as a cache which is updated periodically. When a first time user first visits a site this table must be populated. There is a row for each user and for each site collection for which they have visited. For example, if you have 100 site collections and 50 users have visited 50 of the site collections there will be 50*50 or 2,500 approximately rows in the UserInfo table.

Its not just the UserInfo table which gets updated when a new user visits a site collection for the first time but rather a whole host of tables.

  • UserInfo – Insert one row for new user
  • AllListsAux – Update the item count for the user list
  • AllDocs – Update the Last Modified Time and Item Count for the _catalogs/users
  • AllSites – Update the NextUserOrGroupdId, LastContentChanged and DiskUsed (quota management)
  • AllUserData – Insert a new row
  • EventCache – Insert two new rows into this table to track the updates to the site collection and the site collection’s root web
  • StorageMetrics – Insert a new row
  • StorageMetricsChanges – Update one row

So as you can see when a new user visits a SharePoint site collection for the first time we INSERT into 5 tables and UPDATE 4 tables. Arguably this is not a huge problem day to day but for a net new release of a really popular intranet site this can be a performance bottleneck.

The workaround for this problem is to pre-populate the UserInfo tables for site collections which host popular landing pages. Fortunately SharePoint has an API for that and its called SPWeb.EnsureUser(). This handy API takes a user’s logon name in the format of DOMAIN\Username and adds the user into the UserInfo table and the other tables I mentioned earlier. Now using this API does not mean when the user first visits there will not be any database work. While the handy dandy EnsureUser does do most of the heavy lifting, UserInfo table columns such as the tp_externalToken still need to be populated when a user first visits. Specifically here is what occurs when a user visits a site for the first time after being prepopulated:

  • UserInfo – Update one row for user
  • WebMembers – Insert with new UserID for the web in which the user just visited

As you can see when we choose to pre-populate users there is allot less work for SQL and we can push that initial load to pre-go-live activities.

So when do you use this process? I would suggest you use this process anytime both of the following are true:

  • A new SharePoint release with a large user population such as a new company portal or a new HR site.
  • A single or few landing pages within a single or few sites exist which users will either hit directly or will be auto-redirected based upon some criteria.

When planning to go with the pre-population approach its important that you identify each of the site collections which may host landing pages. Even if these pages exist across different site collections in the same content database because as I mentioned earlier, a user entry in the UserInfo table exists for each site collection the user visits. So the SPWeb to target when calling EnsureUser will be those which are hosting these landing pages.

My team and I have gone through this several times with a number of different customers and each time we have pre-populated the UserInfo table after a failed go-live we have been successful with the re-release. Conclusion; pre-populating the UserInfo table ahead of a major release of a intranet SharePoint site is a Best Practice.

Update: I have released a tool for the pre-population and you can get it from here.

Post to Twitter Post to Facebook Post to LinkedIn Post to Delicious Post to Digg

7 thoughts on “Introduce Users Into the UserInfo Table”

  1. To clarify the use of this method, does it have to be called FROM each site collection that you want pre-populated? So, you would have to execute this method for each user on every site collection with a popular landing page, correct?

    It would be even more convenient if you could specify URLs to pre-populate for or to pre-populate for ALL URLs. For example, web.EnsureUser(string logon, string[] URLs) or web.EnsureUser(string logon, bool allURLs)

  2. Yes it must be called once for each site collection you want to pre-populate. The API you mentioned would not work as you are using a SPWeb.EnsureUser and that SPWeb can only exist in one SPSite so passing URLs to other sits would be incorrect. Another method might be:

    foeach(string url in urls)
    {
    using(SPSite site = new SPSite(url))
    {
    using(SPWeb web = site.OpenWeb())
    {
    foreach(string user in users)
    {
    web.EnsureUser(user)
    }
    }
    }
    }

  3. You’re right. I overlooked the fact that the method is on SPWeb. The method could always be implemented into SPWebApplication though 😉

    Thanks for the reply!

  4. You only get one chance for a first impression so it never hurts going the extra mile. Thanks for the good info, never thought of this! I will immediately create a PowerShell script and use it for our next release.

  5. Yuri – you don’t need powershell to do this, I released a tool /post/2011/09/23/SharePoint-UserInfo-Table-Population-Tool

Comments are closed.