Any tips on how to eliminate key lookup
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?
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!
Legacy stuff, guess it has to be converted.
Thanks
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.