Solving Long Running Query, number of executions problem

I have the following query:

ALTER PROCEDURE [dbo].[spSearchClient]
    @SearchWords NVARCHAR(MAX) = NULL,
    @LowerDate DATE = NULL,
    @UpperDate DATE = NULL,
    @UserCreated nvarchar(450)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @UserAccountID smallint
    DECLARE @SearchWordCount int

    SELECT @UserAccountID = dbo.fnGetUserAccountID(@UserCreated)

    CREATE TABLE #SearchWords
    (
        ID int IDENTITY(1,1),
        Word NVARCHAR(50)
    )

    INSERT INTO #SearchWords
    (
        Word
    )
    SELECT 
        value 
    FROM 
        STRING_SPLIT(@SearchWords, ' ')  
    WHERE 
        TRIM(value) <> ''

    SELECT @SearchWordCount = @@ROWCOUNT;

    SELECT
        C.ClientID, 
        C.FirstName,
        C.LastName,
        C.FullName, 
        C.DateOfBirth,
        G.GenderName, 
        G.GenderIcon, 
        C.VerificationCode,
        V.LastVisitDate
    FROM 
        Client C
    OUTER APPLY (
        SELECT MAX(StartDate) AS LastVisitDate
        FROM Visit AS V
        WHERE C.ClientID = V.ClientID
    ) AS V
    INNER JOIN LookUp.Gender G on
        C.GenderID = G.GenderID
    WHERE
        (
            EXISTS( -- if we have words
                    SELECT *
                    FROM #SearchWords s
                    WHERE (c.FirstName LIKE CONCAT('%',s.Word,'%'))
                       OR (c.LastName LIKE CONCAT('%',s.Word,'%'))
                       OR (c.VerificationCode LIKE CONCAT('%',s.Word,'%'))
                )
            OR @SearchWordCount = 0 --if we don't have words
        )
        AND DateOfBirth BETWEEN ISNULL(@LowerDate,DateOfBirth) AND ISNULL(@UpperDate,DateOfBirth)

    INSERT INTO UserSearchLog
    (
        SearchWords,
        LowerDate,
        UpperDate,
        SearchResultsCount,
        UserCreated
    )
    VALUES
    (
        @SearchWords,
        @LowerDate,
        @UpperDate,
        @@ROWCOUNT,
        @UserAccountID
    )

    DROP TABLE #SearchWords
END

The execution plan is https://www.b

There appears to be a problem with the server that will prevent you from getting good performance with that query, even when @SearchWordCount = 0. Consider the details for node id 15:

This plan executes in row mode and this operator is at the end of the branch, so you can attribute all 20 ms of CPU time and 3962 ms of elapsed time to the index seek on [Visit].[IDX_Visit_ClientID]. When executing this query you spent almost four seconds waiting for something other than CPU work. Looking at the wait stats for the select operator provides a valuable clue:

Almost four seconds waiting on PAGEIOLATCH_SH. We can get more information about the IO that was actually done by looking at the RunTimeInformation in the XML:

SQL Server only needed to do 50 physical reads for the index seek, yet it waited almost four seconds to do them. You're reading data at a rate of 100 KB per second. There could be a problem with the server configuration or perhaps the server is just overloaded. Paul Randal published detailed instructions here for determining the root cause of the problem and how to fix it. Good luck.

On the subject of the pattern matching, SQL Server took about a third of a second to do that with just a single row in the temp table. That part of the query could become a bottleneck as the number of rows increases in the temp table. But you won't get good performance by changing that part of the query without resolving the issue with PAGEIOLATCH_SH waits first.

StackExchange Database Administrators Q#195844, answer score: 4