Optimize database schema / indexes for faster query result when using LIKE and EXISTS clauses
Based on:
SELECT sp.SiteID_i
FROM SitePath_t sp
WHERE EXISTS ( SELECT *
FROM [dbo].[SitePath_T] usp
, [dbo].[UserSiteRight_T] uusr
WHERE uusr.SiteID_i = usp.SiteID_i
AND uusr.UserID_i = 2484
AND usp.Path_v LIKE sp.Path_v+'%' )
(which is just fine based on the fact that you're doing a Semi Join).
It's focussing (rightly) on the uusr table first, to find the records for that user. It's already doing a CIX Seek on that, which is good. From there, it's finding the corresponding records in usp according to the SiteID_i fields.
So next consider the fact that it wants to find the Sites by SiteID_i, and what kind of join you want this to be.
How about a Merge Join? That would be nice, but requires the data to be sorted on both sides. That's fine if the indexes are in the right order...
...and after that, you want to be finding stuff based on the Path. So how about:
CREATE INDEX ix_UUSR on [dbo].[UserSiteRight_T] (UserID_i, SiteID_i);
CREATE INDEX ix_usp on [dbo].[SitePath_T] (SiteID_i) INCLUDE (Path_v);
And then another index on SitePath_T that finds the SiteIDs you want:
CREATE INDEX ix_sp on [dbo].[SitePath_T] (Path_v) INCLUDE (SiteID_i);
There may be a Nested Loop used on this final one, but that's hopefully not too bad. The thing that's going to impact your system will be the first two indexes, which should let you see a Merge Join between the two tables in your EXISTS clause.