Cant elimintated these Index Scan

Marekx371 2017-10-16 21:02:58

I have this Proc that pulls that from a view which is made out of other view, and I cant eliminate these index scans that I have 89 of and 36 clustered scans, anyone can tell me what i missing, I different types of Indexes but it still usuing a scan

SDyckes2 2017-10-17 01:58:54
Not all index scans are a bad thing. There are times an index or table scan will do less work than an index seek.

When tuning your query look at the Top Operations tab to find the highest cost operator, in this case, it is an Index Seek on the [saAdmin].[dbo].[Products.By.Site].[idx_pbs_SiteID].(NonClustered). The next highest costs are for the Hash Match (Inner Joins). Then look for the largest pipe, both the number of records and the size of the data. Look for ways to decrease the size of the perspective pipe.

Since you have provided an estimated plan, it is hard to provide direction on the further tuning of the query.