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.