Any tips on how to eliminate key lookup

Rade 2014-11-11 09:47:38

Hi,

SQL Server 2008 R2 Express
Database for website.

This SP is executing very frequently and as you can see in the plan there is a key lookup on the tblProperty table causing cpu pressure.
Usually I take care of the key lookup with indexing.

But in this case the columns causing the lookups are (ntext, nvarchar (2000)) that are used as free text search fields.
Not much to index here i guess?

Have sent this to the developers and asked why this proc is executing so frequently (site does not have that many users).

The main goal is to reduce cpu and execution rate.
Anybody see anything I missed?

Mart 2014-11-13 11:20:50
As mentioned by @SQLTrooper, that would be the standard way to remove a key lookup. It should be mentioned that key lookups aren't always bad, but in your case clearly one of them seem that way. What you may find is that when (assuming it was possible) you added this additional column to the index you'd find that index would appear the performance bottle neck, after all it all has to add up to 100% somehow.

There are always many ways to skin a SQL CAT 🙂 for key lookups you could take the standard approach or consider an index on that column alone, be that XML/Full Text/Non Clustered. The data could then be read from that index and joined back, potentially better than the keylookup.

It may well be worth considering the data stored in the column and trying a Full Text index – which will work with the current datatype. That said you should obviously, due to the fact it's deprecated, change the data type at some point.

My tests show that you can create a clustered index on the column, but I don't think that would be much use in this case, interesting though. What's more interesting is that after creating a schema bound view, a clustered index can't be added to that view specifically on the ntext column.

Further reading and info…

This link shows info about include columns:

http://technet.microsoft.com/en-us/library/ms190806(v=sql.105).aspx

Specifically it states that the data type you mention can't be included in an index include column, my tests show it can't be used as a key column or and include column. Paul Randall (http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/03/14/can-you-include-columns-of-type-text-ntext-or-image.aspx) states the old data types are stored in a similar way to the new data types but "the internals of how they are processed by the server are different so they [the new types] behave like regular data types".

Hope that helps!

Rade 2014-11-19 14:36:38
Thanks Mart!

This was resolved by the web developers, somebody screwed up and made this SP execute constantly.

Now it executes "now and then" and it is not an issue anymore.
Only need to get them to convert the legacy data types.

Mart 2014-11-19 14:44:58
Hey Rade, no problem. Glad you got it sorted!
@SQLTrooper 2014-11-11 16:06:29
You eliminate Key Lookups by adding them as included columns to existing indexes – in this case IDX_tblPageDefinition_Name (INCLUDE Property and LanguageSpecific) and IDX_tblProperty_fkPageDefinitionID (INCLUDE String and LongString).
Rade 2014-11-12 07:43:58
It is not possible to add Longstring as an included column since it's ntext.
Legacy stuff, guess it has to be converted.

Thanks