Recently we had a case wherin the T-SQL Query was taking around 11+ hours to execute. After studying the Query Execution Plan in depth we have re-written around 60% of the Query again and now its execution time is around 11 minutes and 35 seconds. At this stage, one particular stuff which I am worried about a lot is that I could see one such table whose Scan Count value is pretty high, although I am just fetching around 23000 records from it with only 3 columns but still the value of Scan Count, Logical Reads value is very High and I doubt that at this stage it is the Main Culprit.
I have checked Indexes, Statistics they r properly updated and refreshed as well, I believe that there isn't any issue with respect to the Missing Indexes as well.
Can someone throw some light on this, if they have encountered any similar issues in the past. Another thought that goes in my mind is seperating out this table and putting it into a seperate Filegroup of RAID 10 but that I would like to keep it as an option for later Stage.
Any help would be Highly Appreciated.