SharePoint 2010/2013 and capacity planning for TempDB & TempDB log files.


I’m not sure how many of you know this, and it’s not something I’ve read much in the literature available from Microsoft on SharePoint scalability and sizing.

So I thought I would share my experiance, that is you can have real issues with large Scale SharePoint 2010/2013 platforms if you haven’t sufficiently sized your tempDB, and the tlog that goes with it.

I know microsoft recommend setting all DB’s to AutoGrow, however none of us want to over allocate the amount of storage required for our SharePoint databases, even with AutoGrow you can have the same problem once the LUN’s holding your TempDB + TempDB log fill up.

It’s generally best practice with SharePoint on a medium to large scale to ensure your TempDB & Tlog run on there own lun’s, with reasonable I/O (FC SAN) or even DAS SSD’s etc.., this prevent’s I/O contention between the content databases, and SharePoint stored procedures that manipulate data via temp tables.

I don’t know if you’ve ever looked into the stored procedures that SharePoint uses to retrieve data or carry out operations within timer jobs or for search, or just general DB read operations, but many of these procedures make extensive use of temporary tables, and cursors to work with data.

Thus once your environment starts to get larger (millions+ items) you can start to run into significant demands in terms of TempDB Log & TempDB space, including Random I/O requirements to complete maintaince and crawl update operations, this is in addition to maintaining normal SharePoint operation.

Even standard SPQuery’s onto the content DB’s require TempDB space, if your TempDB or TempDB log fill up, or the LUN’s they are on become full, then until you resolve the issue many parts of SharePoint just stop working.

One particular problem I have encountered on SharePoint 2010 can occur during a full crawl, a table in the CrawlStoreDB (dbo.MSSCrawlUrlChanges) appears to be tracking items crawled for reporting puroposes and stores a TimeStatme (DateTime) and Status (int) with associated item DocID (int), this table has two indexes one non-clustered on DocID (int) the other clustered on TimeStamp (datetime).

Now in a system with many millions of items this table can get rather large when you run a full crawl, I have seen it get as big as millions of rows & 10’s of Gbytes of space, the particular stored procedure that updates this table proc_MSS_CrawlReportPreprocessChanges attempts to load all records less than a particular time into a cursor (which means tempDB action), then it processes this cursor within a transaction, and inserts results as appropriate into a further temp table (more tempDB action).

Finally it clears down the dbo.MSSCrawlUrlChanges and inserts all the changes from the temp table back into the CrawlStoreDB table dbo.MSSCrawlUrlChanges.

This is all carried out under a single transaction so will hold space in TempDB log files for the duration of it’s execution.

These operations of temporary table plus cursor usage can have a real impact on the amount of Space you need in both tempDB and it’s associated transaction log.

This problem can be exacerbated, if you also run dbcc check’s, and index rebuilds as maintaince jobs on your SQL servers that happen to coinside with these TempDB application operations, as SQL DBCC + Index Rebuilds also make significantly demands of tempDB.

I’ve seen failures of these operations on systems with 100-150GB TempDB, with 20GB+ Tlogs.

I’m trying to work out some metric’s that cover sizing of TempDB & Tlog Size vs SharePoint content size and item numbers to provide a bit of guidance, I’ve only found this blog article so far that suggests tempDB should be 25% of your largest content DB, but it doesn’t cover the relationship between TempDB size, content DB size, and Log file size for temp DB, which is crucial if SharePoint is to stay online, I’ll update this article once I have something more specific.

Thanks for reading.

SharePoint 2013 – Deferred Upgrade Option


I’ve been doing a bit of work on this, and I wanted to dispel a couple of thoughts I came across regarding this feature that is now available with SharePoint 2013.

I’ve been looking at ways to optimise the Infrastructure required when attempting to upgrade from SharePoint 2010 -> SharePoint 2013.

The wise amongst us would say it’s just a simple case of building out your new SP2013 infra, moving your content databases across and upgrading them.

Two thoughts here

Capex Costs, if you have a SharePoint 2010 estate, multiple farms across the globe the costs of building duplicate infra can be significant.
Data volumes, if you have 10’s of TBytes of content databases, moving them from one set of servers to another takes time and the system needs to be offline.

If you Google around the internet, I’m not going to name any names or provide links etc.. you will find quite a lot of posts on what deferred upgrade is all about.

Some blogs / posts you might find may tell you that deferred upgrade does not modify the SharePoint 2010 schema, hmm I thought that might help me out in terms of not need to completely duplicate the SharePoint infra, I could duplicate the WFE of the SharePoint farm, and then just drop the content DB’s from one farm, add them to the other, and do a deferred upgrade on SP2013, and if something were to go wrong it would be possible to re-attach the DB’s to the old SP2010 farm as a rollback strategy.

After spending a few hours investigating this idea, I have concluded that some of the posts / blogs on the internet are misleading.

If you examine the contents of the dbo.Versions table in a upgraded SP2010 content databases even if you do not carry out the visual upgrade, and choose to leave everything looking like SP2010 (deferred upgrade) the DB schema is definitely changed.

The dbo.Versions table get’s an extra 120+ row’s added to it all with v15+ numbers.

If you then attempt to reconnect this database to an SP2010 farm, it no longer functions, due to schema incompatibly errors.

So just for the record the deferred upgrade definitely modifies the content database schema making it incompatible with SP2010.