sql-server - SQLServer 2014 无法实现查询的并行性

Question

我使用 SQLServer 2014（120 兼容模式）并且我想要为我的查询提供一个并行执行计划，但由于一些字段，它不是。只要字段pi.Name AS ProcessName被评论，我就有并行性，但当它处于活动状态时它不是。其他注释字段也存在同样的问题。原因是否与涵盖pi.ProcessGuid但不涵盖的索引pi.Name或更多内容有关？

ExecutionPlan_Parallel

ExecutionPlan_Single

执行计划_原创

当我评论这几个字段时，ExecutionPlan 开始并行，从 30 分钟开始查询只运行 11 秒。

SELECT kg.Id,
    ui.UserInfoGuid     AS UserGuid,
    ui.FullName         AS UserName,
    di.DeviceInfoGuid   AS DeviceGuid,
    di.ComputerFullName,
    pi.ProcessGuid,
    --pi.Name AS ProcessName,
    kg.ProcessActivationGuid,
    t.Caption,
    whi.WebsiteHostGuid,
    --whi.HostName,
    wv.WebsiteGuid,
    wv.[Url],
    --cc.CategoryGuid,
    --cc.[Name]           AS CategoryName,
    ca.ControlSequenceGuid,
    ca.ControlActivationGuid/*
    (SELECT ce.[Name] + '/' AS [text()]
    FROM   [raw].ControlElement AS ce
    WHERE  ce.ControlSequenceGuid = ca.ControlSequenceGuid
    ORDER  BY ce.SequenceNumber DESC
    FOR xml path ('')) AS ControlTree*/
    --,ac.ApplicationContextName
    ,kg.BeginDate        AS KeystrokeBeginDate
    ,kg.EndDate          AS KeystrokeEndDate
    ,kg.TotalKeyCount
    ,kg.KeyCount
    ,kg.FunctionalKeyCount
    ,kg.ClickCount
    ,kg.WheelCount
    ,kg.OtherKeyCount
    ,kg.TotalMilliseconds
    ,kg.ActiveMilliseconds
FROM raw.[Session] AS s
    INNER JOIN raw.Monitoring AS m
            ON ( m.SessionGuid = s.SessionGuid )
    INNER JOIN stats.[KeystrokeGroupRcpView] AS kg
            ON ( kg.MonitoringGuid = m.MonitoringGuid )
    INNER JOIN dbo.UserInfo AS ui
            ON ( ui.UserInfoGuid = kg.UserGuid )
    INNER JOIN dbo.DeviceInfo AS di
            ON ( di.DeviceInfoGuid = kg.DeviceGuid )
    INNER JOIN dbo.ProcessInfo AS pi
            ON ( pi.OrganizationGuid = di.OrganizationGuid
                AND pi.ProcessGuid = kg.ProcessGuid )
    INNER JOIN raw.Title AS t
            ON ( t.TitleGuid = kg.TitleGuid )
    LEFT OUTER JOIN [raw].ControlActivation AS ca
                ON ca.ProcessActivationGuid = kg.ProcessActivationGuid
                    AND kg.BeginDate >= ca.BeginDate
                    AND kg.EndDate <= ca.EndDate
    LEFT OUTER JOIN controls.ControlFocus AS cf
                ON cf.ControlActivationGuid = ca.ControlActivationGuid
    LEFT OUTER JOIN controls.ControlSequenceContext AS csc
                ON csc.ControlSequenceGuid = ca.ControlSequenceGuid
    LEFT OUTER JOIN controls.ApplicationContext AS ac
                ON ac.ApplicationContextGuid = csc.ApplicationContextGuid
    LEFT OUTER JOIN controls.ProcessControlCategory AS pcc
                ON pcc.ProcessGuid = pi.ProcessGuid
                    AND pcc.ControlCategoryKeyGuid =
                        cf.ControlCategoryKeyGuid
    LEFT OUTER JOIN controls.ControlCategory AS cc
                ON cc.CategoryGuid = pcc.CategoryGuid
    LEFT OUTER JOIN raw.WebsiteVisit AS wv
                ON ( pi.IsWebBrowser = 1
                    AND wv.ProcessActivationGuid =
                        kg.ProcessActivationGuid
                    AND ( wv.BeginDate < kg.BeginDate
                                AND wv.EndDate >= kg.EndDate ) )
    LEFT OUTER JOIN dbo.WebsiteHostInfo AS whi
                ON ( whi.WebsiteHostGuid = wv.WebsiteHostGuid
                    AND whi.OrganizationGuid = di.OrganizationGuid )
WHERE kg.BeginDate > '2020-07-01' AND kg.BeginDate < '2020-07-02' and kg.UserGuid in ('A170565A-2D30-4911-5B9C-8525E2A2772B','4BE982BD-ADFC-6201-31C2-60A0BEF0D7F6','AE296576-87C6-EC2F-5A6F-E24ACE14456E')

标签： sql-servertsqlrelational-databasesql-server-2014sql-execution-plan