Partager via

SharePoint, that was a poor choice, you need a time out.

  • Article

As the mother of a 3 year old, I find myself saying talking about poor choices and sitting in time out quite a bit.  Perhaps that is due to the fact that my little one has her mommy’s spirited nature, so I can’t always fully blame her.

I also notice that with my customers that use SharePoint, I see SharePoint making poor query choices!  This often times leads to a different type of time out. And just like my 3 year old, it is sometimes in part due to SharePoint’s nature.  Just like your kids, you need to give SharePoint limits.

Now, I am a SQL PFE, so I am not going to go into the details on limits within SharePoint, but I want to illustrate how they can have an affect on the underlying SQL databases. Specifically large lists.  List data within SharePoint is stored in the AllUserData table.  It is a generic table used to hold data for all lists.  Therefore it must be all things to all people.  Part of this is due to the nature of the flexibility within SharePoint.  If you create a very wide list, it will take 2+ rows in the AllUserData table to make 1 “row" of data in the list.  Another feature in lists is that you can have lookup columns that reference data in other lists (think a foreign key).  As you can probably imagine, all these lead to joins back to the AllUserData table.  This is why there are recommended limits to how lists should be designed within SharePoint.  As a DBA, you may need to help identify these when you are noticing poor performance in your SharePoint environment.

Being too lazy to sit down and create a list manually with 100+ columns and 20 lookup columns, I enlisted the help of a SharePoint PFE, Brian Jackett,  to write a PowerShell script that will do it for me.  Which he did and shared in his blog.  One of the things I love about working in PFE is the amazing network of engineers that are always willing to help one another out.  Thanks again Brian!

Anyway, I now have my large list in my lab SharePoint environment, and I simply ran a trace while looking at the list in the SharePoint  UI to capture the query.  Leaving out most of the columns in the select list, the below query is not one that will be very efficient when there is a lot of data. I put in green text the joins back to the AllUserData table required to get multiple rows in this table to form a single “row” in the list.  The blue joins are for the lookup columns. This is exactly why there are recommended limits to sizes in lists. You may have many of these queries running concurrently.  If you see queries like these running against your SharePoint content databases, ask your SharePoint administrators about large lists that may be exceeding the documented capacity limitations within SharePoint.

**Note** It is not supported to query SharePoint databases directly. I am a trained professional.

SELECT ...
  FROM AllUserData AS UserData WITH(INDEX=AllUserData_PK)
    LEFT OUTER LOOP JOIN Docs AS t1 WITH(NOLOCK)
        ON (UserData.[tp_CalculatedVersion] = 0 )
        AND    (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) )
        AND (UserData.[tp_DeleteTransactionId] = 0x )
        AND (UserData.[tp_RowOrdinal] = 0)
        AND (t1.SiteId=UserData.tp_SiteId)
        AND (t1.SiteId = @SITEID)
        AND (t1.ParentId = UserData.tp_ParentId)
        AND (t1.Id = UserData.tp_DocId)
        AND ( (UserData.tp_Level = 1) )
        AND (t1.Level = UserData.tp_Level)
        AND (t1.IsCurrentVersion = 1)
        AND (t1.Level = 1 OR t1.Level =  2)
    LEFT OUTER LOOP JOIN AllUserData AS t2 WITH(NOLOCK,INDEX=AllUserData_PK)
ON (UserData.[tp_ID]=t2.[tp_ID])
AND (UserData.[tp_RowOrdinal] = 0)
AND (t2.[tp_RowOrdinal] = 1)
AND (UserData.[tp_Level] = t2.[tp_Level])
AND (t2.[tp_IsCurrentVersion] = CONVERT(bit,1) )
AND (t2.[tp_CalculatedVersion] = 0 )
AND (t2.[tp_DeleteTransactionId] = 0x )
AND (t2.tp_ListId = @L2)
AND (UserData.tp_ListId = @L2)
LEFT OUTER LOOP JOIN AllUserData AS t3 WITH(NOLOCK,INDEX=AllUserData_PK)
ON (UserData.[tp_ID]=t3.[tp_ID])
AND (UserData.[tp_RowOrdinal] = 0)
AND (t3.[tp_RowOrdinal] = 2)
AND (UserData.[tp_Level] = t3.[tp_Level])
AND (t3.[tp_IsCurrentVersion] = CONVERT(bit,1) )
AND (t3.[tp_CalculatedVersion] = 0 )
AND (t3.[tp_DeleteTransactionId] = 0x )
AND (t3.tp_ListId = @L2)
AND (UserData.tp_ListId = @L2)
LEFT OUTER LOOP JOIN AllUserData AS t4 WITH(NOLOCK,INDEX=AllUserData_PK)
ON (UserData.[tp_ID]=t4.[tp_ID])
AND (UserData.[tp_RowOrdinal] = 0)
AND (t4.[tp_RowOrdinal] = 3)
AND (UserData.[tp_Level] = t4.[tp_Level])
AND (t4.[tp_IsCurrentVersion] = CONVERT(bit,1) )
AND (t4.[tp_CalculatedVersion] = 0 )
AND (t4.[tp_DeleteTransactionId] = 0x )
AND (t4.tp_ListId = @L2)
AND (UserData.tp_ListId = @L2)
    LEFT OUTER LOOP JOIN AllUserData AS t5 WITH(NOLOCK,INDEX=AllUserData_PK)
ON (t2.[int15]=t5.[tp_ID])
AND (t2.[tp_RowOrdinal] = 1)
AND (t5.[tp_RowOrdinal] = 0)
AND ( (t5.tp_Level = 1) )
AND (t5.[tp_IsCurrentVersion] = CONVERT(bit,1) )
AND (t5.[tp_CalculatedVersion] = 0 )
AND (t5.[tp_DeleteTransactionId] = 0x )
AND (t5.tp_ListId = @L3)
AND (t2.tp_ListId = @L2)
LEFT OUTER LOOP JOIN AllUserData AS t6 WITH(NOLOCK,INDEX=AllUserData_PK)
ON (t3.[int8]=t6.[tp_ID])
AND (t3.[tp_RowOrdinal] = 2)
AND (t6.[tp_RowOrdinal] = 0)
AND ( (t6.tp_Level = 1) )
AND (t6.[tp_IsCurrentVersion] = CONVERT(bit,1) )
AND (t6.[tp_CalculatedVersion] = 0 )
AND (t6.[tp_DeleteTransactionId] = 0x )
AND (t6.tp_ListId = @L3)
AND (t3.tp_ListId = @L2)
LEFT OUTER LOOP JOIN AllUserData AS t7 WITH(NOLOCK,INDEX=AllUserData_PK)
ON (t3.[int9]=t7.[tp_ID])
AND (t3.[tp_RowOrdinal] = 2)
AND (t7.[tp_RowOrdinal] = 0)
AND ( (t7.tp_Level = 1) )
AND (t7.[tp_IsCurrentVersion] = CONVERT(bit,1) )
AND (t7.[tp_CalculatedVersion] = 0 )
AND (t7.[tp_DeleteTransactionId] = 0x )
AND (t7.tp_ListId = @L3)
AND (t3.tp_ListId = @L2)
LEFT OUTER LOOP JOIN AllUserData AS t8 WITH(NOLOCK,INDEX=AllUserData_PK)
ON (t3.[int10]=t8.[tp_ID])
AND (t3.[tp_RowOrdinal] = 2)
AND (t8.[tp_RowOrdinal] = 0)
AND ( (t8.tp_Level = 1) )
AND (t8.[tp_IsCurrentVersion] = CONVERT(bit,1) )
AND (t8.[tp_CalculatedVersion] = 0 )
AND (t8.[tp_DeleteTransactionId] = 0x )
AND (t8.tp_ListId = @L3)
AND (t3.tp_ListId = @L2)
LEFT OUTER LOOP JOIN AllUserData AS t9 WITH(NOLOCK,INDEX=AllUserData_PK)
ON (t3.[int11]=t9.[tp_ID])
AND (t3.[tp_RowOrdinal] = 2)
AND (t9.[tp_RowOrdinal] = 0)
AND ( (t9.tp_Level = 1) )
AND (t9.[tp_IsCurrentVersion] = CONVERT(bit,1) )
AND (t9.[tp_CalculatedVersion] = 0 )
AND (t9.[tp_DeleteTransactionId] = 0x )
AND (t9.tp_ListId = @L3)
AND (t3.tp_ListId = @L2)
LEFT OUTER LOOP JOIN AllUserData AS t10 WITH(NOLOCK,INDEX=AllUserData_PK)
ON (t3.[int12]=t10.[tp_ID])
AND (t3.[tp_RowOrdinal] = 2)
AND (t10.[tp_RowOrdinal] = 0)
AND ( (t10.tp_Level = 1) )
AND (t10.[tp_IsCurrentVersion] = CONVERT(bit,1) )
AND (t10.[tp_CalculatedVersion] = 0 )
AND (t10.[tp_DeleteTransactionId] = 0x )
AND (t10.tp_ListId = @L3)
AND (t3.tp_ListId = @L2)
LEFT OUTER LOOP JOIN AllUserData AS t11 WITH(NOLOCK,INDEX=AllUserData_PK)
ON (t3.[int13]=t11.[tp_ID])
AND (t3.[tp_RowOrdinal] = 2)
AND (t11.[tp_RowOrdinal] = 0)
AND ( (t11.tp_Level = 1) )
AND (t11.[tp_IsCurrentVersion] = CONVERT(bit,1) )
AND (t11.[tp_CalculatedVersion] = 0 )
AND (t11.[tp_DeleteTransactionId] = 0x )
AND (t11.tp_ListId = @L3)
AND (t3.tp_ListId = @L2)
LEFT OUTER LOOP JOIN AllUserData AS t12 WITH(NOLOCK,INDEX=AllUserData_PK)
ON (t3.[int14]=t12.[tp_ID])
AND (t3.[tp_RowOrdinal] = 2)
AND (t12.[tp_RowOrdinal] = 0)
AND ( (t12.tp_Level = 1) )
AND (t12.[tp_IsCurrentVersion] = CONVERT(bit,1) )
AND (t12.[tp_CalculatedVersion] = 0 )
AND (t12.[tp_DeleteTransactionId] = 0x )
AND (t12.tp_ListId = @L3)
AND (t3.tp_ListId = @L2)
LEFT OUTER LOOP JOIN AllUserData AS t13 WITH(NOLOCK,INDEX=AllUserData_PK)
ON (t3.[int15]=t13.[tp_ID])
AND (t3.[tp_RowOrdinal] = 2)
AND (t13.[tp_RowOrdinal] = 0)
AND ( (t13.tp_Level = 1) )
AND (t13.[tp_IsCurrentVersion] = CONVERT(bit,1) )
AND (t13.[tp_CalculatedVersion] = 0 )
AND (t13.[tp_DeleteTransactionId] = 0x )
AND (t13.tp_ListId = @L3)
AND (t3.tp_ListId = @L2)
LEFT OUTER LOOP JOIN AllUserData AS t14 WITH(NOLOCK,INDEX=AllUserData_PK)
ON (t3.[int16]=t14.[tp_ID])
AND (t3.[tp_RowOrdinal] = 2)
AND (t14.[tp_RowOrdinal] = 0)
AND ( (t14.tp_Level = 1) )
AND (t14.[tp_IsCurrentVersion] = CONVERT(bit,1) )
AND (t14.[tp_CalculatedVersion] = 0 )
AND (t14.[tp_DeleteTransactionId] = 0x )
AND (t14.tp_ListId = @L3)
AND (t3.tp_ListId = @L2)
LEFT OUTER LOOP JOIN AllUserData AS t15 WITH(NOLOCK,INDEX=AllUserData_PK)
ON (t4.[int2]=t15.[tp_ID])
AND (t4.[tp_RowOrdinal] = 3)
AND (t15.[tp_RowOrdinal] = 0)
AND ( (t15.tp_Level = 1) )
AND (t15.[tp_IsCurrentVersion] = CONVERT(bit,1) )
AND (t15.[tp_CalculatedVersion] = 0 )
AND (t15.[tp_DeleteTransactionId] = 0x )
AND (t15.tp_ListId = @L3)
AND (t4.tp_ListId = @L2)
LEFT OUTER LOOP JOIN AllUserData AS t16 WITH(NOLOCK,INDEX=AllUserData_PK)
ON (t3.[int2]=t16.[tp_ID])
AND (t3.[tp_RowOrdinal] = 2)
AND (t16.[tp_RowOrdinal] = 0)
AND ( (t16.tp_Level = 1) )
AND (t16.[tp_IsCurrentVersion] = CONVERT(bit,1) )
AND (t16.[tp_CalculatedVersion] = 0 )
AND (t16.[tp_DeleteTransactionId] = 0x )
AND (t16.tp_ListId = @L3)
        AND (t3.tp_ListId = @L2)
WHERE (UserData.tp_ListID=@LISTID) AND ( (UserData.tp_Level = 1) )
    AND (UserData.tp_SiteId=@SITEID AND (UserData.tp_ParentId=@DocParentIdForRF))
    AND (UserData.tp_RowOrdinal=0) AND (t1.SiteId=@SITEID AND (t1.ParentId=@DocParentIdForRF))
ORDER BY UserData.[tp_ID]  ASC 
OPTION (FORCE ORDER, MAXDOP 1)',
N'@LFFP uniqueidentifier,@SITEID uniqueidentifier,@L2 uniqueidentifier,@L3 uniqueidentifier,@FDN nvarchar(4000),@FLN nvarchar(4000),@LISTID uniqueidentifier,@NUMROWS bigint,@RequestGuid uniqueidentifier',@LFFP='00000000-0000-0000-0000-000000000000',@SITEID='F6958F1A-78E9-4A35-8303-B3F81ECE1CE8',@L2='496C3810-29DE-4892-B7C2-E53F2224C77C',@L3='CBF0C028-7759-4428-BC86-E399E29FAD24',@FDN=N'sites/LabSiteCollection1/LabSite1/Lists',@FLN=N'MyBigList',@LISTID='496C3810-29DE-4892-B7C2-E53F2224C77C',@NUMROWS=31,@RequestGuid='DABE137E-35DE-4EE3-A5A3-7A1B4F772057'

 

 

So remember.  SharePoint is not bad, but sometimes it needs some guidance and limits set to help keep it on the straight and narrow.