none
请教下SSISDB库语句慢的问题 RRS feed

  • 问题

  • sqlserver2012-sp4

    SSISDB系统会经常执行这个语句:

    SELECT
    u.name AS [Name],
    u.principal_id AS [ID],
    ISNULL(ak.name,N'') AS [AsymmetricKey],
    ISNULL(cert.name,N'') AS [Certificate],
    ISNULL(suser_sname(u.sid),N'') AS [Login],

            CASE
            WHEN N'C' = u.type THEN 1
            WHEN N'K' = u.type THEN 2        
            WHEN N'S' = u.type AND SUSER_SNAME(u.sid) is null AND u.authentication_type != 2 THEN 3
            ELSE 0 END
           AS [UserType]
    FROM
    sys.database_principals AS u
    LEFT OUTER JOIN sys.asymmetric_keys AS ak ON ak.sid = u.sid
    LEFT OUTER JOIN sys.certificates AS cert ON cert.sid = u.sid
    WHERE
    (u.type in ('U', 'S', 'G', 'C', 'K'))
    ORDER BY
    [Name] ASC

    非常非常慢,大概需要执行5分多钟,可能还会更久,而且会锁表,因为是系统表我无法创建索引等优化操作

    sys.database_principals  记录18条

    sys.asymmetric_keys   记录0条

    sys.certificates    记录52万行

    这种情况怎么处理?

    2020年8月24日 1:53

全部回复