13

By reading this LIKE character length limitation here, it looks like I can't send a text longer than ~4000 characters in a LIKE clause.

I'm trying to fetch the query plan from query plan cache for a particular query.

SELECT *
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp 
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
where st.text like '%MY_QUERY_LONGER_THAN_4000_CHARS%' ESCAPE '?'

if the query inside the the LIKE is longer than 4000 chars then I get 0 results even if my query is in the cache plan. (I was expecting at least an erorr).

Is there a way to workaround this issue or to do it differently? I have queries which can be > 10000 chars long and it look like I can't find them with the LIKE.

  • 2
    Break up the text perhaps... since you shouldn't have many queries exactly like each other: where st.text like '%MY_QUERY%CHARS%' ESCAPE '?' – scsimon May 31 '17 at 18:28
  • 4
    Do you actually have query texts that are identical for 4,000 characters and then differ? – Martin Smith May 31 '17 at 18:56
  • @MartinSmith yes, I do have queries like that. – Dan Dinu Jun 1 '17 at 9:17
9

It does not appear that this can be solved in pure T-SQL since neither CHARINDEX nor PATINDEX allow for using more than 8000 bytes in the "to search for" string (i.e. max of 8000 VARCHAR or 4000 NVARCHAR characters). This can be see in the following tests:

SELECT 1 WHERE CHARINDEX(N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 7000),
                         N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 6000)) > 0

SELECT 1 WHERE PATINDEX(N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 7000),
                        N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 6000)) > 0

Both of those queries return the following error:

Msg 8152, Level 16, State 10, Line xxxxx
String or binary data would be truncated.

And, reducing the 7000 in either of those queries down to 3999 gets rid of the error. A value of 4000 in both cases will also error (due to the extra N'Z' character at the beginning).

HOWEVER, this can be accomplished using SQLCLR. It is fairly simple to create a scalar function that accepts two input parameters of type NVARCHAR(MAX).

The following example illustrates this ability using the Free version of the SQL# SQLCLR library (which I created, but String_Contains is again available in the Free version :-).

The String_Contains scalar UDF currently has the @SearchValue input param as NVARCHAR(4000) instead of NVARCHAR(MAX) (I must not have thought people would be searching for strings of over 4000 characters ;-) but that is very easy to change by making the following one-time change (after SQL# has been installed, of course):

GO
ALTER FUNCTION [SQL#].[String_Contains](@StringValue [NVARCHAR](MAX),
                                        @SearchValue [NVARCHAR](MAX))
RETURNS [BIT]
WITH EXECUTE AS CALLER
AS EXTERNAL NAME [SQL#].[STRING].[Contains];
GO

SETUP

-- DROP TABLE #ContainsData;
CREATE TABLE #ContainsData
(
  ContainsDataID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  Col1 NVARCHAR(MAX) NOT NULL
);

INSERT INTO #ContainsData ([Col1])
VALUES (N'Q' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 15000)),
       (N'W' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 20000)),
       (N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 70000));

-- verify the lengths being over 8000
SELECT tmp.[ContainsDataID], tmp.[Col1], DATALENGTH(tmp.[Col1])
FROM   #ContainsData tmp;

TESTS

SELECT tmp.[ContainsDataID], tmp.[Col1], DATALENGTH(tmp.[Col1])
FROM   #ContainsData tmp
WHERE  SQL#.String_Contains(tmp.[Col1], REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 15100)) = 1;
-- IDs returned: 2 and 3

SELECT tmp.[ContainsDataID], tmp.[Col1], DATALENGTH(tmp.[Col1])
FROM   #ContainsData tmp
WHERE  SQL#.String_Contains(tmp.[Col1], REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 26100)) = 1;
-- IDs returned: 3

Please keep in mind that String_Contains is using an everything-sensitive (case, accent, Kana, and width) comparison.

2

Because you also asked for alternative approaches, another way to find a specific plan is to search for its plan_hash, by altering your query as follows:

SELECT *
FROM sys.dm_exec_cached_plans AS cp 
INNER JOIN sys.dm_exec_query_stats qs
    ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp 
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE qs.query_hash = 0xE4026347B5F49802

The quickest way I've found to get the QueryHash value to search for is to paste the query in question into a Query Window and then Display the Estimated Execution Plan. Read the XML output and look for the QueryHash attribute in the StmtSimple element and this should give you what you need. Plug the QueryHash value into the query above and hopefully you should have what you're looking for.

Here's some screenshots showing how to quickly get the QueryHash value in case I'm explaining it poorly.

Display Estimated Execution Plan

enter image description here

Show Execution Plan XM...

enter image description here

Search for QueryHash Value

enter image description here

Obviously the trick won't work if the query you're looking for differs from the query you're displaying the Estimated Execution Plan for, but this may be quicker than all the nuance that comes with CLR routines and getting those to work properly.

0

If you have access to the query texts (meaning you can modify them), you could add unique comments to those you are interested in:

select /* myUniqueQuery123 */ whatever from somewhere ...

then search for myUniqueQuery123 in the plan cache instead of the entire query text:

... where st.text like '%myUniqueQuery123%'

PS. Not tested

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.