Main Performance issue witht this plan and how to improve
I can see there is also a Table Valued function causing a cardinality estimate but need advice on fixing if possible.
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.
Any other advice on the other issues regarding the mismatch in actual versus estimated would be much appreciated.