sql server - SQLServer 2014 unable to achieve parallelism for query

Question

I use SQLServer 2014 (120 compatibility mode) and I want a parallel execution plan for my query, but because of a few fields, it isn't. As long as field pi.Name AS ProcessName is commented I have parallelism but when it is active it isn't. The same problem is with other commented fields. Is the reason connected with an index which covers pi.ProcessGuid but not pi.Name or something more?

ExecutionPlan_Parallel

ExecutionPlan_Single

ExecutionPlan_Original

When I commented on those few fields ExecutionPlan starts to be parallel and from 30 minutes query runs only 11 seconds.

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')

question from:https://stackoverflow.com/questions/65938166/sqlserver-2014-unable-to-achieve-parallelism-for-query

Answer 1

Waitting for answers