Report running slow

SQLDB 2015-03-31 10:31:02

We are running attached report daily on a server:

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

SQLkiwi 2015-03-31 12:15:29
To add that column as an include, you would need to change the underlying column data type from the (deprecated) ntext to nvarchar(max). Moving away from deprecated types is something you should be planning to do anyway (as well as moving to a more recent version of SQL Server as time and money allows). That said, I doubt including this column in the index will improve this query's runtime performance much. That's not to say it isn't generally beneficial to eliminate RID and Key Lookups in most cases, but it's not the biggest performance problem in the plan supplied.

There are many things that could (and probably should) be improved first. The one that stands out is this:

Spool

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.

SQLDB 2015-03-31 17:01:42
Thanks SQLkiwi, will check on the indexes for sure!