Query is very slow with index seeks
(a) the compiled parameter for CustomerNumber is an explicit value and the runtime parameter is a wildcard
(b) some of the clustered index seeks could be slightly more efficient if you had non-clustered indexes that covered only the columns required, though whether it's worth maintaining those depend on workload balance. For example the clustered index seek at Node 45 could be a more efficient nc index seek if the following index were present:
CREATE INDEX [IX_CustomerPOSCompanyXref_CustomerUID_CompanyUID] ON [CustomerSource].[dbo].[CustomerPOSCompanyXref] ([CustomerPOSCompanyUID] DESC, [POSCustomerType] ASC, [POSCustomerNumber] ASC, [CustomerUID] ASC, [CompanyUID] ASC) INCLUDE ([POSCustomerCategory], [POSCustomerParentCategory]) GO
(c) I don't know that parallelism is helping you here, I might shift back to maxdop 1
(d) the Table I/O tab is telling – even though the plan shows there is only a single seek on CustomerPOSCompanyXref, there's clearly more going on here, because that registers 44,611 reads. I think this is a case where the CTE messes things up, so I might try this:
SELECT CustomerPOSCompanyUID AS TempUID INTO #t FROM dbo.CustomerPOSCompanyXref (NOLOCK) WHERE POSCustomerNumber like @CustomerNumber GROUP BY CustomerPOSCompanyUID; CREATE UNIQUE CLUSTERED INDEX x ON #t(TempUID);
Then join to #t instead of the CTE. I might be tempted to try additional unraveling of the next CTE. This may give the optimizer a better opportunity to come up with a good plan, knowing the number of company IDs it will deal with, and hopefully eliminate all of the reads that I believe are caused by the complex cascading of CTEs.