Main Performance issue witht this plan and how to improve

Pearljammer1 2017-01-17 17:11:23

I have updated stats on AI_Core (with fullscan), run dbcc freeproccache, yet the actual and estimated rows in the highlighted KeyLookup are still way out. Also when there is no output list in a keylookup (and it just says PREDICATE), is there a way to remove the keylook as is the case when there is an output list and you add the listed columns to the non clustered Index ?
I can see there is also a Table Valued function causing a cardinality estimate but need advice on fixing if possible.

Rob Farley 2017-01-17 21:28:29
(Until I can get to a machine with stuff installed and answer the other parts of this question…)

When a KeyLookup has a Predicate, there is a column used in the Predicate that wasn't available before. It might not need to return that column via the Output property, but simply run a test on it and return a row (potentially without any columns) or not. So figure out what that column is and you can get rid of your KeyLookup.

Ok – now that I've seen it.

There's a bunch of TVFs that could be doing anything and causing you a ton of grief.

But also, have you looked at the plan and seen the massive black lines coming out of SEC_Hierachy_MappingsForUsers? You should really index MappingID. If MappingID is indeed unique, you should see those joins disappear completely. You don't seem to even have any statistics on that table – there are Scans on it that estimate 1 row but are actually spitting out 1002 rows. That's going to affect a lot of things. Put an index on MappingID, and you will probably see a radically different query plan.

I would actually recommend you look through a lot of your system to see where uniqueness should apply, because I suspect a number of these joins would disappear.

You seem to hook into the same tables over and over again. I can't see the full query because it's been truncated, but I'm sure you could query those tables fewer times and achieve the same.

That whole row of Table Scans is interesting, and worth a comment. I'm actually tempted to write A blog post about it even… You see, those tables tiny. 11 rows here, 35 rows there – they probably use a single page each. Having an index on that wouldn't help the number of pages being read at all. But where there are 35 rows, a lack of index means it would have to hunt through all the rows to find any that match. With a unique index, it would be able to do a binary search across those sorted 35 rows, and find what it's after in between 1 and 6 attempts.

But definitely start by indexing SEC_Hiearchy_MappingsForUser, to get both Seeks and Statistics on it.

Pearljammer1 2017-01-18 13:15:34
Thank you Rob – I have added the relevant columns to the non clustered index and it has removed the key lookups – brilliant ! thank you very much

Any other advice on the other issues regarding the mismatch in actual versus estimated would be much appreciated.

Thanks again

Rob Farley 2017-01-21 01:47:24
Updated my answer… You should index SEC_Hierachy_MappingsForUser.