What is "proc_StartUndirtyList" and who calls it?

Question

Good day.

We have a multi-server sharepoint 2010 farm (2 front-end, 1 application, 1 SQL). In this farm we deployed a web application with a content database size about 1.7 terabyte. We use RBS for SQL 2012, so big part of the database (1.5Tb) is in a filestream storage.

Recently we noticed that during peak times (at 1-2 p.m.) sharepoint database executes this code:

SET NOCOUNT ON;EXEC proc_StartUndirtyList 'BB165D99-E9B2-4CC6-8169-38BF72D0A4FF','526432E7-C283-421A-8B09-9A4338C177B6','BC1EF5ED-67C6-42D1-B412-8A85C46BD972';SELECT TOP 1000 U.nvarchar10,D.MetaInfo,D.DirName,D.LeafName,U.tp_Level FROM dbo.TVF_Docs_DirNameEqLike_Value('C38A7EAC-FA02-43F6-98A5-177A2B6D12A4',N'account', N'account/%') AS D CROSS APPLY dbo.TVF_UserData_PId_DId_Level(D.SiteId, D.ParentId, D.Id, D.Level) AS U WHERE D.ListDataDirty = 1 AND U.tp_RowOrdinal=0 ORDER BY D.DirName Asc, D.LeafName Asc, D.Level OPTION (FORCE ORDER, MAXDOP 1)

It's execution practically freeses the whole database and sharepoint receives SQL timeout errors because of the database locks.

I want to understand what this query is, who starts it, why this query affect the database so much, and how can I avoid it in the future.

Answer 1

Probably you got more information than you posted, but just in case you didn't know yet - the proc_StartUndirtyList stored procedure is used to start a new link fixup operation.

A back-end database server and front-end Web server work together to implement link fixup. The back-end database server tracks forward links from list items to documents. When the back-end database server performs an operation that requires link fixup for list items, it defers potentially complex work by marking the list items needing link fixup as dirty. Later, before a front-end Web server retrieves list data, it checks the "list is dirty" status and, if the list is dirty, performs a link fixup operation before querying the data in the list.

When a protocol client starts a link fixup operation, it starts the operation with a call to proc_StartUndirtyList call and retrieves the data columns from the AllUserData table that correspond to fields whose type allows forward links to be discovered for rows whose corresponding entry in the AllDocs table. Given the resulting range, the protocol client calls proc_GetListDataLinks to obtain the correct forward links for the data. The protocol client then computes the correct values for the field data and commits the data back to the AllUserData table, also calling proc_UndirtyListItem for each item. After repeating this procedure for every dirty list item, the protocol client includes a call to proc_FinishUndirtyList to complete the operation.

The SQL syntax for the procedure is as follows:

PROCEDURE proc_StartUndirtyList(
   @WebId uniqueidentifier,
   @ListId uniqueidentifier,
   @CacheParseId uniqueidentifier,
   @RequestGuid uniqueidentifier = NULL OUTPUT
);

Try using PowerShell to open web with ID BB165D99-E9B2-4CC6-8169-38BF72D0A4FF and get the list with ID 526432E7-C283-421A-8B09-9A4338C177B6. See if you can get any info out of it. Reasons for the procedure being slow might be different - big lists, permissions (maybe?) etc.

Source: Microsoft documentation.

Hope this helps!