Report running slow

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

avatar image By SQLDB 60 asked Mar 31, 2015 at 10:31 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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.

sp.png (30.4 kB)
avatar image By SQLkiwi ♦ 6.6k answered Mar 31, 2015 at 12:15 PM
more ▼
(comments are locked)
avatar image SQLDB Mar 31, 2015 at 05:01 PM

Thanks SQLkiwi, will check on the indexes for sure!

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x109
x46

asked: Mar 31, 2015 at 10:31 AM

Seen: 61 times

Last Updated: Mar 31, 2015 at 05:01 PM