ntext column type

There are a large amount of ntext columns (~140) in the OMS database. ntext, text and image columns can't be indexed and key lookups to the clustered index are performed whenever these columns need to be retrieved. Understandably, this comes at a performance cost.
We are most concerned about the different notes columns on the dbfile and dbtasks tables as they often cause a large number of lookups. Are there any plans to alter these columns to nvarchar(max) in the near future?

these columns are pre varchar(max) was availble and are legacy. ntext has been marked for being removed in the next release of observer and we are looking at how we would upgrade oursites to this in replicated scenarios. technically there are no real penalties in using these at present but we are wanting to.get the benefits of.changing these data types in the future.if there are ny specific traces you are unsure of for lookup please let us know so we can investigate what maybe happening on the business layer.

That is great news and we will benefit from the change to varchar(max). Key lookups are something we definitely want to avoid: they are expensive, slow (very slow if the data is not cached), cause unnecessary locking of the clustered index and are a common cause of deadlocks.
Not sure what you mean by any specific traces for lookup??

Have you got any SQL Trace logs of any specific lookups that are occuring regularly? There is a setting from memory that allows you to set the amount of bytes of data to keep in the data page of the table for NText column's that reduce the need for a lookup, this can help if the data is often less then so many bytes of data. I will see what and how impact testing to change the column types went.

That sounds great and I'd be keen to know how the impact testing went.
I think you are talking about how ntext data is stored as a LOB and separately from the rest of the data in that row. The LOB data is located via a pointer in the row. The sp_tableoption stored proc can be used to instruct SQL Server to store ntext data on the same page as the rest of the row if the ntext data is small enough to fit. The lookups I'm talking about are a little different. For example one of our stored procedures that looks for tasks returns the tsknotes column, which is ntext. We seek into a nonclustered index on the dbtasks table to find all the tasks for a specific feeusrid however tskNotes does not and can not exist in that nonclustered index as ntext columns can't be defined as either a key column or included column in NCIX's. Say we find 50,000 tasks for that feeusrid - that means 50,000 key lookups to the clustered index to retrieve the tskNotes column values. That is in effect 50,000 separate trips to the clustered index and back plus the overhead of finding the LOB data.
I don't believe there is any way around that (sp_tableoption or any other method) via as long as the columns are defined as ntext.

You are correct in what you say, such is the nature of ntext columns.
Until we can be confident that the change from ntext to nvarchar(MAX) does not break our existing code base we must continue using legacy ntext columns such as tskNotes.
I'm not familiar with your database topology, whether you have replication or a single instance so please consider the following proposal with that in mind.
If you create a computed column of Convert(nvarchar(MAX), tskNotes) on the task table you could then include the computed column as an included column in an appropriate index and point your stored procedure to the new column. The down side would of course be storage, in effect the notes are stored three times and any performance in maintaining the computed column. Additonally if you have a replicated database the network traffic for dbtasks will also increase. You will of course need to decide whether this is a suitable trade off to the key lookups and whether it is practical for your environment.

I think the overhead of the computed columns outweighs the benefit of stopping the key lookups in our case and we'd rather wait for the upgrade. W as just hoping to get an rough idea of how far off this may be?