SqlServer Begin's

So the usual story, the query works well for small data sets but does not work optimally for large data. What are my options ?      Recompile the view/function/procedure Update states Add a optimize @Variable for unknown Look for missing indexes Rewrite the query The low hanging fruits Generally, the top two items would allow you to get over the hump but without a optimize for hint you can’t get over the parameter sniffing problem and without the index you would keep running into the same problem over and over again. Long term Rewrite the query to avoid the the index and if you get really lucky it may be possible to avoid the optimize for hint. How i went about solving the problem The fat line indicated where the problem existed, but what would it take to resolve the  issue ? The table (let’s call it DetailTransactionLineItem just because it held detail to detail table  ) had only fraction of the data for the @Pratmerter Used. Currently it is pu