No, I'm not "The SharePoint Mike Walsh!"... I'm a SQL Server DBA/etc... Anyway, having some issues with SharePoint and I don't know much about the application end of SharePoint but trying to help the team figure out what process in SharePoint is calling a query...Little Background... There are some large document libraries with no folders that this is happening on (10,000 and 16,000 documents respectively). They are working on a plan to get into compliance with the best practice of no more than 2,000 documents. These documents are stored as part of a document management system that only writes to the document library. The users access their PDFs from the SharePoint site and their browser...We have ruled out our DocAve backup tool and believe we have ruled out a user simply browsing the library but otherwise don't have any good leads on a pattern of when the query is called. Bad news is one service account sadly runs much of their components so I can't look at the host name or Service Account name to help identify it. This is an ugly query (in more ways than one) and hopefully reducing the library size will improve it (Looking at the query and some of the join and index hints, I can see why they say no more than 2K documents per library!)Any help in identifying when and why this query is being called would be helpful! (In case this is more than just document library size).exec sp_executesql N' set nocount on;set ansi_warnings off;declare @aggrTable1 table(AggrNewID# uniqueidentifier, Count# int, GroupCol#0 sql_variant); insert into @aggrTable1 select NEWID() as AggrNewID#,COUNT(*) as Count#, UserData.[sql_variant3] FROM UserData INNER MERGE JOIN Docs AS t1 WITH(NOLOCK) ON ( 1 = 1 AND UserData.[tp_RowOrdinal] = 0 AND t1.SiteId = UserData.tp_SiteId AND t1.SiteId = @L2 AND t1.DirName = UserData.tp_DirName AND t1.LeafName = UserData.tp_LeafName AND t1.Level = UserData.tp_Level AND (UserData.tp_Level = 255 AND t1.LTCheckoutUserId =@IU OR (UserData.tp_Level = 1 AND (UserData.tp_DraftOwnerId IS NULL OR (UserData.tp_DraftOwnerId <>@IU AND 1=0 )) OR UserData.tp_Level = 2 AND (UserData.tp_DraftOwnerId = @IU OR 1=1 )) AND (t1.LTCheckoutUserId IS NULL OR t1.LTCheckoutUserId <> @IU )) AND (1 = 1)) LEFT OUTER JOIN AllUserData AS t2 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[tp_CheckoutUserId]=t2.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t2.[tp_RowOrdinal] = 0 AND ( (t2.tp_IsCurrent = 1) ) AND t2.[tp_CalculatedVersion] = 0 AND t2.[tp_DeleteTransactionId] = 0x AND t2.tp_ListId = @L3 AND UserData.tp_ListId = @L4) WHERE (UserData.tp_Level= 255 AND UserData.tp_CheckoutUserId = @IU OR ( UserData.tp_Level = 2 AND UserData.tp_DraftOwnerId IS NOT NULL OR UserData.tp_Level = 1 AND UserData.tp_DraftOwnerId IS NULL ) AND ( UserData.tp_CheckoutUserId IS NULL OR UserData.tp_CheckoutUserId <> @IU)) AND UserData.tp_SiteId=@L2 AND (UserData.tp_DirName=@DN) AND UserData.tp_RowOrdinal=0 AND (t1.SiteId=@L2 AND (t1.DirName=@DN)) GROUP BY UserData.[sql_variant3];declare @aggrTable2 table(AggrNewID# uniqueidentifier, Count# int, GroupCol#0 sql_variant, GroupCol#1 nvarchar(255) COLLATE Latin1_General_CI_AS_KS_WS); insert into @aggrTable2 select NEWID() as AggrNewID#,COUNT(*) as Count#, UserData.[sql_variant3], UserData.[nvarchar11] FROM UserData INNER MERGE JOIN Docs AS t1 WITH(NOLOCK) ON ( 1 = 1 AND UserData.[tp_RowOrdinal] = 0 AND t1.SiteId = UserData.tp_SiteId AND t1.SiteId = @L2 AND t1.DirName = UserData.tp_DirName AND t1.LeafName = UserData.tp_LeafName AND t1.Level = UserData.tp_Level AND (UserData.tp_Level = 255 AND t1.LTCheckoutUserId =@IU OR (UserData.tp_Level = 1 AND (UserData.tp_DraftOwnerId IS NULL OR (UserData.tp_DraftOwnerId <>@IU AND 1=0 )) OR UserData.tp_Level = 2 AND (UserData.tp_DraftOwnerId = @IU OR 1=1 )) AND (t1.LTCheckoutUserId IS NULL OR t1.LTCheckoutUserId <> @IU )) AND (1 = 1)) LEFT OUTER JOIN AllUserData AS t2 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[tp_CheckoutUserId]=t2.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t2.[tp_RowOrdinal] = 0 AND ( (t2.tp_IsCurrent = 1) ) AND t2.[tp_CalculatedVersion] = 0 AND t2.[tp_DeleteTransactionId] = 0x AND t2.tp_ListId = @L3 AND UserData.tp_ListId = @L4) WHERE (UserData.tp_Level= 255 AND UserData.tp_CheckoutUserId = @IU OR ( UserData.tp_Level = 2 AND UserData.tp_DraftOwnerId IS NOT NULL OR UserData.tp_Level = 1 AND UserData.tp_DraftOwnerId IS NULL ) AND ( UserData.tp_CheckoutUserId IS NULL OR UserData.tp_CheckoutUserId <> @IU)) AND UserData.tp_SiteId=@L2 AND (UserData.tp_DirName=@DN) AND UserData.tp_RowOrdinal=0 AND (t1.SiteId=@L2 AND (t1.DirName=@DN)) GROUP BY UserData.[sql_variant3],UserData.[nvarchar11];set ansi_warnings on;set nocount off;SELECT t1.[Type] AS c0,t2.[tp_ID] AS c5c7,t1.[IsCheckoutToLocal] AS c11,UserData.[tp_ContentTypeId],UserData.[nvarchar10],UserData.[datetime1],t1.[LeafName] AS c4,UserData.[nvarchar4],UserData.[tp_CheckoutUserId],UserData.[tp_ModerationStatus],UserData.[tp_Level],t2.[nvarchar1] AS c5c6,UserData.[tp_HasCopyDestinations],UserData.[nvarchar17],UserData.[sql_variant3],t1.[ScopeId] AS c3,UserData.[tp_ContentType],UserData.[nvarchar13],UserData.[datetime4],CASE WHEN DATALENGTH(t1.DirName) = 0 THEN t1.LeafName WHEN DATALENGTH(t1.LeafName) = 0 THEN t1.DirName ELSE t1.DirName + N''/'' + t1.LeafName END AS c2,UserData.[nvarchar3],t2.[nvarchar5] AS c5c9,UserData.[tp_UIVersion],UserData.[tp_ID],t2.[tp_Created] AS c5c10,UserData.[nvarchar5],UserData.[tp_CopySource],UserData.[nvarchar11],UserData.[datetime2],t1.[TimeCreated] AS c1,t2.[nvarchar4] AS c5c8,t1.[DirName] AS c12,aggrTable#1.AggrNewID# AS __AggrNewID#1,aggrTable#1.Count# AS __Count#1,aggrTable#1.GroupCol#0 AS __GroupCol#1,aggrTable#2.AggrNewID# AS __AggrNewID#2,aggrTable#2.Count# AS __Count#2,aggrTable#2.GroupCol#1 AS __GroupCol#2 FROM UserData INNER MERGE JOIN Docs AS t1 WITH(NOLOCK) ON ( 1 = 1 AND UserData.[tp_RowOrdinal] = 0 AND t1.SiteId = UserData.tp_SiteId AND t1.SiteId = @L2 AND t1.DirName = UserData.tp_DirName AND t1.LeafName = UserData.tp_LeafName AND t1.Level = UserData.tp_Level AND (UserData.tp_Level = 255 AND t1.LTCheckoutUserId =@IU OR (UserData.tp_Level = 1 AND (UserData.tp_DraftOwnerId IS NULL OR (UserData.tp_DraftOwnerId <>@IU AND 1=0 )) OR UserData.tp_Level = 2 AND (UserData.tp_DraftOwnerId = @IU OR 1=1 )) AND (t1.LTCheckoutUserId IS NULL OR t1.LTCheckoutUserId <> @IU )) AND (1 = 1)) LEFT OUTER JOIN AllUserData AS t2 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[tp_CheckoutUserId]=t2.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t2.[tp_RowOrdinal] = 0 AND ( (t2.tp_IsCurrent = 1) ) AND t2.[tp_CalculatedVersion] = 0 AND t2.[tp_DeleteTransactionId] = 0x AND t2.tp_ListId = @L3 AND UserData.tp_ListId = @L4) INNER JOIN @aggrTable1 as aggrTable#1 ON ( ( (UserData.[sql_variant3] IS NULL AND aggrTable#1.GroupCol#0 IS NULL) OR UserData.[sql_variant3]=aggrTable#1.GroupCol#0) ) INNER JOIN @aggrTable2 as aggrTable#2 ON ( ( (UserData.[sql_variant3] IS NULL AND aggrTable#2.GroupCol#0 IS NULL) OR UserData.[sql_variant3]=aggrTable#2.GroupCol#0) AND ( (UserData.[nvarchar11] IS NULL AND aggrTable#2.GroupCol#1 IS NULL) OR UserData.[nvarchar11]=aggrTable#2.GroupCol#1) ) WHERE (UserData.tp_Level= 255 AND UserData.tp_CheckoutUserId = @IU OR ( UserData.tp_Level = 2 AND UserData.tp_DraftOwnerId IS NOT NULL OR UserData.tp_Level = 1 AND UserData.tp_DraftOwnerId IS NULL ) AND ( UserData.tp_CheckoutUserId IS NULL OR UserData.tp_CheckoutUserId <> @IU)) AND UserData.tp_SiteId=@L2 AND (UserData.tp_DirName=@DN) AND UserData.tp_RowOrdinal=0 AND (t1.SiteId=@L2 AND (t1.DirName=@DN)) ORDER BY UserData.[sql_variant3] Asc,UserData.[nvarchar11] Asc,t1.[Type] Desc,UserData.[tp_ID] Asc OPTION (FORCE ORDER) ',N'@L0 uniqueidentifier,@L2 uniqueidentifier,@IU int,@L3 uniqueidentifier,@L4 uniqueidentifier,@DN nvarchar(260)',@L0='00000000-0000-0000-0000-000000000000',@L2='4C14AF67-F1FE-480B-AC89-5D1E12C46464',@IU=1073741823,@L3='E11D8AEA-203C-494A-9B5F-CC9250722035',@L4='49F943D7-B321-4896-B700-D520350479BB',@DN=N'site/library'I left the @IU parameter unchanged because I think that may be significant? It appears to be the Sharepoint System user? When this query runs it is crushing our database server and crushing the app server apparently.Mike Walsh - www.straightpathsql.com/blog

I'd try time-synching with verbose logging, look at the log files. if it's the system account probably a workflow. You might have to cross reference with the iis logs to get a user id/url if there's a human behind it...most workflows are triggered with human intervention. The sql profiler doesn't give a hint?

10 - 16k items shouldn't be killing you from a SQL perspective. You are definintly way over on what you should present in the WFE. I would encourage you to create a couple of views to bring back only the data you need. There is no need to reduce the number of items in the list itself. SharePoint 2007 can acutally work with 5 million items in a list. Your going to find that you will need to either use folders or views to show the data. The latency as you approach the 2000 item limit is due to the interation of data to present it. Even if you know exactly where and when this query is being ran, you can't do much with it. The database is off limits if you want to keep MSFT support on your implementation. If these are static documents, I would investigate BLOB caching. It will store these documents on the WFE and remove the burden from SQL. Shannon Bray - MCT, MCPD, MCTS, MCITP Blog http://shannonbray.wordpress.com User Group Site: http://www.cospug.com