I came accross the existance of Key Lookups in the plan cache of SQL Server. Most of the time it’s better to change existing indexes or add new ones in order to do an index only search.
But I did not only want to do this investigative work manually but automatically.
So I created a SQL Server Agent Job to execute Kendal van Dykes script Find Key Lookups in Cached Plans v1.00 (2010-07-27) to tell me if any Key Lookups exist in the Plan Cache.
It executed with success, but as most of our objects where we have stored the business logic in are Stored Procedures that have very long comments before the real TSQL starts, the CSV-file with the result of my query just contained the first 256 characters of the Stored Procedure text.
So i had a look at the parameters for sp_send_dbmail and found that
[ @query_no_truncate= ] query_no_truncate
Specifies whether to execute the query with the option that avoids truncation of large variable length data types (varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, and user-defined data types). When set, query results do not include column headers. The query_no_truncate value is of type bit. When the value is 0 or not specified, columns in the query truncate to 256 characters. When the value is 1, columns in the query are not truncated. This parameter defaults to 0.
would fit my needs.
But as i added the parameter to my SQL Server Agents Job-step, i got the following error:
Msg 22050, Level 16, State 1, Line 0 Failed to execute the query because the @query_result_no_append and @query_no_truncate options are mutually exclusive.
So i had a closer look at the parameter “@query_result_no_append”.
Problem is, that the parameter “@query_result_no_append” does not exist in SQL Server, (there are only 3 hits if you google it).
You have to omit the parameter “@query_result_no_padding”, that is mutually exclusive with the use of “@query_no_truncate”.