I’ve tried to refactor my request because it’s taking a lot of IO operations and DTU is very high on our Azure.

Then, I’ve found the problem query and logs said that it read around 2kk pages * 8 kb = around 15.25 GB per request.

I’ve found some solution with using of CTE for pre-filtering big table which taking the biggest part of IO operations.
With using SET STATISTICS IO ON I’m getting such info:

...
Table 'TRN_CaseProperties'. Scan count 16670, logical reads 795223,
Table 'AspNetUsers'. Scan count 1, logical reads 99975,
Table 'TRN_CaseAddresses'. Scan count 16670, logical reads 54294,
...

In result I’m getting only 1 rows which was got by “TRN_CaseProperties” table condition.
I’ve found, if I’m deleting OFFSET FETCH operation from my query, then it’s much better. And certainly I’m getting the same single row.

...
Table 'TRN_CaseProperties'. Scan count 1, logical reads 10862,
Table 'AspNetUsers'. Scan count 2, logical reads 16,
Table 'TRN_CaseAddresses'. Scan count 26987, logical reads 88068
...

If compare statistic from sys.dm_exec_query_stats, then avg_logical_reads (total_logical_reads /execution_count) is next: with OFFSET FETCH is 1 950 546, and without 197471.

My query looks like:

--  SqlServer.2016
set statistics io on;
set statistics profile on;

DECLARE @searchBy NVarChar(4000) -- String
SET     @searchBy = N'chonchon'
DECLARE @searchBy_2 NVarChar(4000) -- String
SET     @searchBy_2 = N'%chonchon%'
DECLARE @_openCaseStatus NVarChar(4000) -- String
SET     @_openCaseStatus = N'Open'
DECLARE @unassignedCaseValue NVarChar(4000) -- String
SET     @unassignedCaseValue = N'Unassigned'
DECLARE @LiveCaseStatusIdOrderType Int -- Int32
SET     @LiveCaseStatusIdOrderType = 0
DECLARE @take Int -- Int32
SET     @take = 1
DECLARE @take_1 Int -- Int32
SET     @take_1 = 1
DECLARE @take_2 Int -- Int32
SET     @take_2 = 1
DECLARE @take_3 Int -- Int32
SET     @take_3 = 1
DECLARE @take_4 Int -- Int32
SET     @take_4 = 12
DECLARE @skip Int -- Int32
SET     @skip = 0
DECLARE @UserCustomerId BigInt -- Int64
SET     @UserCustomerId = 1001;

WITH [PropertiesCTE] ([CaseCommonId])
AS
(
    SELECT DISTINCT
        [x].[CaseCommonId]
    FROM
        [dbo].[TRN_CaseProperties] [x]
    WHERE
        ([x].[DataType] = @searchBy OR [x].[Value] LIKE @searchBy_2 ESCAPE N'~')
)
SELECT
    [common].[CaseCommonId]
FROM
    [dbo].[TRN_CaseCommons] [common]
        INNER JOIN [dbo].[CFG_Customers] [customer] ON [common].[CustomerId] = [customer].[CustomerId]
        LEFT JOIN [dbo].[AspNetUsers] [c_2] ON [c_2].[Id] = [common].[CreatorId]
        LEFT JOIN [dbo].[CFG_CaseStatusIdOrderConfig] [x_3] ON [x_3].[OrderType] = @LiveCaseStatusIdOrderType AND [x_3].[StatusId] = [common].[StatusId]
        LEFT JOIN [dbo].[TRN_CaseCompanies] [x_4] ON [x_4].[CaseCommonId] = [common].[CaseCommonId]
        LEFT JOIN [dbo].[TRN_CaseIndividuals] [x_5] ON [x_5].[CaseCommonId] = [common].[CaseCommonId]
        LEFT JOIN [dbo].[Countries] [x_6] ON [x_6].[CountryId] = [x_4].[CountryId]
        LEFT JOIN [dbo].[Countries] [x_7] ON [x_7].[CountryId] = [x_5].[CountryId]
        LEFT JOIN [dbo].[TRN_CaseAddresses] [x_8] ON [x_8].[CaseCommonId] = [common].[CaseCommonId]
        LEFT JOIN [dbo].[AspNetUsers] [assignedUsers] ON [common].[AssignedUserId] = [assignedUsers].[Id]
        LEFT JOIN [PropertiesCTE] [caseProperties] ON [common].[CaseCommonId] = [caseProperties].[CaseCommonId]
        
WHERE
    (
        [common].[Type] = 1 AND 
        [common].[CustomerId] = @UserCustomerId AND 
        ((Lower([x_4].[EntityName]) LIKE N'%chonchon%' ESCAPE N'~' OR Lower([x_8].[Address]) LIKE N'%chonchon%' ESCAPE N'~') 
        OR [caseProperties].[CaseCommonId] IS NOT NULL) 
    
        OR [common].[Type] = 2 AND 
        [common].[CustomerId] = @UserCustomerId AND 
        ((Lower([x_5].[FirstName] + N' ' + [x_5].[LastName]) LIKE N'%chonchon%' ESCAPE N'~' 
        OR Lower([x_8].[Address]) LIKE N'%chonchon%' ESCAPE N'~') 
        OR [caseProperties].[CaseCommonId] IS NOT NULL)
    ) AND
    [common].[IsVisible] = 1 AND
    [common].[IsVisible] IS NOT NULL AND
    [common].[IsDeactivated] = 0 AND
    [common].[IsLiveMonitoring] IS NULL AND
    [common].[CustomerId] = @UserCustomerId AND
    ([common].[Status] = @_openCaseStatus OR [common].[Status] IS NULL)
ORDER BY
    [common].[CaseCommonId] DESC
OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY   

chonchon is keyword for search by tables.

I suppose that the biggest problem in this query is LIKE '%pattern%' because it couldn’t use index.
I’ve tried update statistics and rebuild index – difference in time is insignificant.

What I’m doing wrong and how to improve it?