Select query inside procedure taking long time, clustered indexes doing scans, need suggestion

biandsqluser 2016-12-05 11:07:17

I have a procedure, which is running very slow, and is being timed out without returning any records. [Reports] Table is already generated by another SP, which creates indexes on this underlying table ([Reports]) and this SP just reads data from this [Reports] table.

Approx number of records 150K and is taking more than 15 minutes to load this data.

End user needs "Select All" for all filters i.e. they need data for all filters available on report.

I have added current execution plan for slow procedure and underlying table structure:

What could be the reason for slow performance?

Things I have tried:

  • –Local Parameters are already in place (to avoid parameter sniffing)
    –Updated statics
    –Tried to have column store index on all VARCHAR(MAX) columns
    –Tried to create full text search column but couldn't do due to
    underlying table structure (no unique single non-nullable column index is

But there is no improvement in performance.

Please let me know if additional information is required to answer this question and give suggestions


Aaron Bertrand 2017-01-08 17:03:51
I get a 404 error on the link. Also, local parameters might seem like a good idea to avoid parameter sniffing, but it sounds like my solution to "the kitchen sink" – which uses dynamic SQL and OPTION (RECOMPILE) – might be more appropriate.