Report running slow
SQL server 2005, 32 bit, SP3 12 CPU and 16 GB RAM.
MAXDOP setting 1.
As per query plan i can assume for thr key look up that i need to create the index to include those missing columns, but the problem is it says to include a column which is ntext.
i tried removing that column from the script but seems no luck.
I.e index 21 which shows a key look up , missing index script shows to include the columns listed there, but column 4 being ntext, I am unable to do so.
This missing index is the one with most beneficial improving query cost per sp_blitzindex
Please help on how i can tune this, thanks
There are many things that could (and probably should) be improved first. The one that stands out is this:
The 580 rows from the Index Seek on Table5.Index14 are stored in a temporary spool worktable then replayed almost 23,000 times before a residual predicate at the Nested Loops Outer Join eliminates most of them. You should look carefully at the predicate on the join, and look to improve the indexing on Table5 so the spool is eliminated. The plan is anonymized and current table/index definitions weren't supplied so I can't be more specific I'm afraid.