none
Настройка COLLATE сервера SharePoint 2010 RRS feed

  • Общие обсуждения

  • Сейчас на сервере стоит Cyrillic_General_CI_AS, а на базе  Latin1_General_CI_AS_KS_WS. Трассировка показывает, что есть класс запросов использующий хинт COLLATE. Пример фрагмента:
    t1.[DirName] COLLATE Cyrillic_General_CI_AS = @L6DNP
    1) Есть настройка которая может повлять на генерацию SQL запросов в части хинта, должен быть default, настройка берется с сервера?
    2) Как можно выйти на разработчиков? Такое поведение ошибка, если убрать ложное преобразование запросы выполняется в 10-100 раз быстрей.

    Пример медленного запроса:

    exec sp_executesql N'       SELECT TOP(@NUMROWS) t2.[tp_Created] AS c3c8, t1.[SortBehavior] AS c0, UserData.[nvarchar10], UserData.[nvarchar15], t1.[MetaInfo] AS c15, UserData.[tp_ItemOrder], t1.[ParentVersionString] AS c28, t1.[TimeLastModified] AS c10, t3.[nvarchar4] AS c11c6, UserData.[tp_ModerationStatus], UserData.[nvarchar1], UserData.[nvarchar6], UserData.[bit2], UserData.[tp_Created], t1.[Size] AS c23, UserData.[tp_WorkflowInstanceID], t2.[nvarchar4] AS c3c6, UserData.[ntext1], UserData.[nvarchar14], t4.[nvarchar1] AS c18c4, t3.[nvarchar1] AS c11c4, 
    UserData.[tp_ID], t1.[IsCheckoutToLocal] AS c13, UserData.[nvarchar5], UserData.[bit1], t1.[LTCheckoutUserId] AS c21, t1.[ItemChildCount] AS c26, UserData.[tp_GUID], t1.[TimeCreated] AS c1, 
    UserData.[tp_Editor], UserData.[tp_Author], t4.[nvarchar6] AS c18c7, t2.[nvarchar1] AS c3c4, UserData.[nvarchar13], t1.[ParentLeafName] AS c29, t1.[Type] AS c9, t3.[nvarchar6] AS c11c7, t1.[Id] AS 
    c16, UserData.[tp_ContentTypeId], UserData.[tp_WorkflowVersion], UserData.[nvarchar4], UserData.[tp_CheckoutUserId], UserData.[tp_Version], UserData.[nvarchar9], t4.[tp_ID] AS c18c5, t5.[nvarchar1] 
    AS c4, UserData.[tp_IsCurrentVersion], t2.[nvarchar6] AS c3c7, UserData.[tp_HasCopyDestinations], UserData.[tp_Level], UserData.[nvarchar12], UserData.[nvarchar17], t3.[tp_ID] AS c11c5, t1.[ProgId] 
    AS c14, t1.[DirName] AS c19, t1.[ClientId] AS c22, t1.[FolderChildCount] AS c27, t1.[LeafName] AS c2, UserData.[nvarchar3], UserData.[tp_Modified], UserData.[nvarchar8], t4.[tp_Created] AS c18c8, 
    UserData.[tp_UIVersion], t1.[ETagVersion] AS c30, t2.[tp_ID] AS c3c5, UserData.[nvarchar16], UserData.[tp_CopySource], UserData.[nvarchar11], UserData.[tp_InstanceID], t3.[tp_Created] AS c11c8, 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 c12, t1.[ScopeId] AS c17, t1.[CheckinComment] AS c25, 
    UserData.[tp_UIVersionString], UserData.[nvarchar2], UserData.[nvarchar7], t4.[nvarchar4] AS c18c6, t1.[Size] AS c20 FROM AllDocs AS t1 WITH(INDEX=AllDocs_Url,NOLOCK) INNER LOOP JOIN UserData ON 
    (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 OR 
    UserData.tp_Level =  2 OR  UserData.tp_Level =255) ) 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_Level = 2)AND (t1.LTCheckoutUserId IS NULL OR t1.LTCheckoutUserId <> @IU ))) AND (t1.[DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData 
    AS t2 WITH(NOLOCK,INDEX=AllUserData_PK) ON (UserData.[tp_Editor]=t2.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t2.[tp_RowOrdinal] = 0) AND ( (t2.tp_Level = 1) ) AND (t2.[tp_IsCurrentVersion] = 
    CONVERT(bit,1) ) AND (t2.[tp_CalculatedVersion] = 0 ) AND (t2.[tp_DeleteTransactionId] = 0x ) AND (t2.tp_ListId = @L3) AND (UserData.tp_ListId = @L4) LEFT OUTER LOOP JOIN AllUserData AS t3 
    WITH(NOLOCK,INDEX=AllUserData_PK) ON (UserData.[tp_CheckoutUserId]=t3.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t3.[tp_RowOrdinal] = 0) AND ( (t3.tp_Level = 1) ) AND (t3.[tp_IsCurrentVersion] 
    = CONVERT(bit,1) ) AND (t3.[tp_CalculatedVersion] = 0 ) AND (t3.[tp_DeleteTransactionId] = 0x ) AND (t3.tp_ListId = @L3) AND (UserData.tp_ListId = @L4) LEFT OUTER LOOP JOIN AllUserData AS t4 
    WITH(NOLOCK,INDEX=AllUserData_PK) ON (UserData.[tp_Author]=t4.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t4.[tp_RowOrdinal] = 0) AND ( (t4.tp_Level = 1) ) AND (t4.[tp_IsCurrentVersion] = 
    CONVERT(bit,1) ) AND (t4.[tp_CalculatedVersion] = 0 ) AND (t4.[tp_DeleteTransactionId] = 0x ) AND (t4.tp_ListId = @L3) AND (UserData.tp_ListId = @L4) LEFT OUTER LOOP JOIN AllUserData AS t5 WITH(NOLOCK,INDEX=AllUserData_PK) ON (t1.[LTCheckoutUserId]=t5.[tp_ID]) 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) WHERE ( (UserData.tp_Level = 1 OR UserData.tp_Level =  2 OR  UserData.tp_Level =255)  AND ( 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=@SITEID) AND (UserData.tp_RowOrdinal=0) AND (((t1.[LeafName] COLLATE Cyrillic_General_CI_AS = @L5LNP) AND (t1.[DirName] COLLATE Cyrillic_General_CI_AS = @L6DNP)) AND t1.SiteId=@SITEID AND (t1.DirName=@DN OR t1.DirName LIKE @DNEL+N''/%'')) ORDER BY t1.[SortBehavior]  DESC ,UserData.[tp_ID]  ASC  OPTION (FORCE ORDER, MAXDOP 1)'

    Пример быстрого запроса, который должен быть:


    exec sp_executesql N'       SELECT TOP(@NUMROWS) t2.[tp_Created] AS c3c8, t1.[SortBehavior] AS c0, UserData.[nvarchar10], UserData.[nvarchar15], t1.[MetaInfo] AS c15, UserData.[tp_ItemOrder], t1.[ParentVersionString] AS c28, t1.[TimeLastModified] AS c10, t3.[nvarchar4] AS c11c6, UserData.[tp_ModerationStatus], UserData.[nvarchar1], UserData.[nvarchar6], UserData.[bit2], UserData.[tp_Created], t1.[Size] AS c23, UserData.[tp_WorkflowInstanceID], t2.[nvarchar4] AS c3c6, UserData.[ntext1], UserData.[nvarchar14], t4.[nvarchar1] AS c18c4, t3.[nvarchar1] AS c11c4, 
    UserData.[tp_ID], t1.[IsCheckoutToLocal] AS c13, UserData.[nvarchar5], UserData.[bit1], t1.[LTCheckoutUserId] AS c21, t1.[ItemChildCount] AS c26, UserData.[tp_GUID], t1.[TimeCreated] AS c1, 
    UserData.[tp_Editor], UserData.[tp_Author], t4.[nvarchar6] AS c18c7, t2.[nvarchar1] AS c3c4, UserData.[nvarchar13], t1.[ParentLeafName] AS c29, t1.[Type] AS c9, t3.[nvarchar6] AS c11c7, t1.[Id] AS 
    c16, UserData.[tp_ContentTypeId], UserData.[tp_WorkflowVersion], UserData.[nvarchar4], UserData.[tp_CheckoutUserId], UserData.[tp_Version], UserData.[nvarchar9], t4.[tp_ID] AS c18c5, t5.[nvarchar1] 
    AS c4, UserData.[tp_IsCurrentVersion], t2.[nvarchar6] AS c3c7, UserData.[tp_HasCopyDestinations], UserData.[tp_Level], UserData.[nvarchar12], UserData.[nvarchar17], t3.[tp_ID] AS c11c5, t1.[ProgId] 
    AS c14, t1.[DirName] AS c19, t1.[ClientId] AS c22, t1.[FolderChildCount] AS c27, t1.[LeafName] AS c2, UserData.[nvarchar3], UserData.[tp_Modified], UserData.[nvarchar8], t4.[tp_Created] AS c18c8, 
    UserData.[tp_UIVersion], t1.[ETagVersion] AS c30, t2.[tp_ID] AS c3c5, UserData.[nvarchar16], UserData.[tp_CopySource], UserData.[nvarchar11], UserData.[tp_InstanceID], t3.[tp_Created] AS c11c8, 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 c12, t1.[ScopeId] AS c17, t1.[CheckinComment] AS c25, 
    UserData.[tp_UIVersionString], UserData.[nvarchar2], UserData.[nvarchar7], t4.[nvarchar4] AS c18c6, t1.[Size] AS c20 FROM AllDocs AS t1 WITH(INDEX=AllDocs_Url,NOLOCK) INNER LOOP JOIN UserData ON 
    (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 OR 
    UserData.tp_Level =  2 OR  UserData.tp_Level =255) ) 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_Level = 2)AND (t1.LTCheckoutUserId IS NULL OR t1.LTCheckoutUserId <> @IU ))) AND (t1.[DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData 
    AS t2 WITH(NOLOCK,INDEX=AllUserData_PK) ON (UserData.[tp_Editor]=t2.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t2.[tp_RowOrdinal] = 0) AND ( (t2.tp_Level = 1) ) AND (t2.[tp_IsCurrentVersion] = 
    CONVERT(bit,1) ) AND (t2.[tp_CalculatedVersion] = 0 ) AND (t2.[tp_DeleteTransactionId] = 0x ) AND (t2.tp_ListId = @L3) AND (UserData.tp_ListId = @L4) LEFT OUTER LOOP JOIN AllUserData AS t3 
    WITH(NOLOCK,INDEX=AllUserData_PK) ON (UserData.[tp_CheckoutUserId]=t3.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t3.[tp_RowOrdinal] = 0) AND ( (t3.tp_Level = 1) ) AND (t3.[tp_IsCurrentVersion] 
    = CONVERT(bit,1) ) AND (t3.[tp_CalculatedVersion] = 0 ) AND (t3.[tp_DeleteTransactionId] = 0x ) AND (t3.tp_ListId = @L3) AND (UserData.tp_ListId = @L4) LEFT OUTER LOOP JOIN AllUserData AS t4 
    WITH(NOLOCK,INDEX=AllUserData_PK) ON (UserData.[tp_Author]=t4.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t4.[tp_RowOrdinal] = 0) AND ( (t4.tp_Level = 1) ) AND (t4.[tp_IsCurrentVersion] = 
    CONVERT(bit,1) ) AND (t4.[tp_CalculatedVersion] = 0 ) AND (t4.[tp_DeleteTransactionId] = 0x ) AND (t4.tp_ListId = @L3) AND (UserData.tp_ListId = @L4) LEFT OUTER LOOP JOIN AllUserData AS t5 WITH(NOLOCK,INDEX=AllUserData_PK) ON (t1.[LTCheckoutUserId]=t5.[tp_ID]) 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) WHERE ( (UserData.tp_Level = 1 OR UserData.tp_Level =  2 OR  UserData.tp_Level =255)  AND ( 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=@SITEID) AND (UserData.tp_RowOrdinal=0) AND (((t1.[LeafName] COLLATE Latin1_General_CI_AS_KS_WS = @L5LNP) AND (t1.[DirName] COLLATE Latin1_General_CI_AS_KS_WS = @L6DNP)) AND t1.SiteId=@SITEID AND (t1.DirName=@DN OR t1.DirName LIKE @DNEL+N''/%'')) ORDER BY t1.[SortBehavior]  DESC ,UserData.[tp_ID]  ASC  OPTION (FORCE ORDER, MAXDOP 1)'

    Изменение настроек сервера неприемлемо, нет возможности под каждое приложение создавать сервер.


    29 августа 2015 г. 9:57

Все ответы

  • Михаил, 

    У нас аналогичная проблема. Я поднимал вопрос на форуме http://www.sql.ru/forum/1179087-3/degradaciya-proizvoditelnosti-sharepoint-medlennoe-vypolnenie-zaprosov-sql, но пока безрезультатно.

    Вам удалось прояснить сей нюанс? Нашли какое нибуть решение / workaround?

    Хочу предложить объединить усилия. Как с вами можно связаться? мой скайпид: sntr2006

    19 октября 2015 г. 11:25