How can I refine my query in a best way with optimum results (for large database)

Transact-SQL
1

How can I refine my query in a best way with optimum results (for large database tables) ?

I tried to optimize the query with same results.

please see my query. I used two inner join by using the same derived tables which is fine.

Our client is now experiencing the slow performance issues by using below query.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGetCommonList]
@firstName nvarchar(250) = NULL,
@surName nvarchar(250) = NULL,
@title nvarchar(50) = NULL,
@companyName nvarchar(250) = NULL,
@phone nvarchar(50) = NULL,
@email nvarchar(250) = NULL,
@country nvarchar(250) = NULL,
@county nvarchar(250) = NULL,
@town nvarchar(250) = NULL,
@postcode nvarchar(250) = NULL,
@isHighRiskUser bit = NULL,
@note nvarchar(250) = NULL,
@franchiseId int = null,
@renterType int = NULL,
@Take int = 0,
@Skip int = 0
AS
BEGIN

declare @PageSize int = @Take;
declare @PageNumber int = @Skip;

Select tblD.Id as 'Id', [dbo].ConvertToEncryptedId(tblD.Id) as 'EncId',
(CASE
WHEN ISNULL(tblD.RenterType, 0) = 3 THEN
ISNULL(tblD.CompanyName, '')
ELSE
ISNULL(tblD.FirstName, '') + ' ' + ISNULL(tblD.Surname, '')
END) as 'RenterAccountHolderFullName',

ISNULL(tblD.RenterPhone, '') as 'RenterPhone',
ISNULL(tblD.RenterEmail, '') as 'RenterEmail',
ISNULL(tblD.PostCode, '') as 'LicencePostCode',
ISNULL(tblD.CountryName, '') as 'CountryName',
ISNULL(tblD.CountryName, '') as 'CountyName',
ISNULL(tblD.TownName, '') as 'TownName',
ISNULL(tblD.ContactPostCode, '') as 'ContactPostCode',

(CASE
WHEN ISNULL(tblD.RenterType,0) = 3 THEN
'Company'
WHEN ISNULL(tblD.RenterType,0) = 1 THEN
'Individual / Sole Trade'
WHEN ISNULL(tblD.RenterType,0) = 2 THEN
'Individual / Sole Trade'
ELSE
'N/A'
END) as 'RenterTypeName',

ISNULL(tblD.isHighRiskUser, 0) as 'IshighRiskRenter',

(CASE
WHEN ISNULL(tblD.isHighRiskUser, 0) = 1 THEN
'Yes'
ELSE
'No'
END) as 'HighRiskUsrTxt',

ISNULL(tblD.HighRiskUsrComment, '') as 'HighRiskUsrComment',
ISNULL(tblD.CompanyAccountNo, '') as 'RenterCompanyAcNumber',
ISNULL(tblD.CompanyName, '') as 'RenterCompanyName',
ISNULL(tblD.CompanyDetail, '') as 'RenterCompanyDetails',
ISNULL(tblD.FirstName, '') as 'RenterFirstName',
ISNULL(tblD.Surname, '') as 'RenterSurname',
ISNULL(tblD.FranchiseName, '') as 'FranchiseName',
ISNULL(tblD.RACount, 0) as 'RACount',
ISNULL(tblD.ResCount, 0) as 'ResCount',
count(ISNULL(tblD.Id, 0)) over() as 'TotalCount'

FROM
(
SELECT r.[Id],r.[LicenceDetails] AS LicenceDetailAvailable, r.[Id] RenterId,0 LookupRenterId,r.[LicenceIssuedBy],
r.[LicenceIssuedCountry],r.[LicenceExpiryDate],
r.[ETDSeen] IsETDSeen,r.[WebCheck] WebCheck,r.[LicenceSeen] LicenceSeen, r.[CopyMade] CopyMade,r.[RenterType] AS RenterType,
r.Sources ,
r.[Title] ,
r.[FirstName], r.[Surname] , r.[DOB] ,
r.[PhoneNo] AS RenterPhone,
r.[Email] AS RenterEmail,
r.[CompanyAccountNo],
r.[CompanyName],
r.[CompanyDetail],
r.[PostCode], r.[LicenceNo], r.[House], r.[Street] , r.[Village] ,
r.[Country], r.[County] , r.[Town] , (case when isnull(r.CountryName,'') = '-- Select Country --' then '' else r.CountryName end) CountryName, r.CountyName, r.TownName,
r.[ContactPostCode],
r.[ContactAddressType],
r.[ContactAddress1],
r.[ContactAddress2],
r.[ContactAddress3],
r.[ContactCountry],
r.[ContactCounty],
r.[ContactTown], r.[ContactMethod] ContactMethod, r.[ContactCountryName] ContactCountryName, r.[ContactCountyName] ContactCountyName, r.[ContactTownName] ContactTownName,
r.[Occupation] Occupation, r.[OccEmploymentType] OccEmploymentType, r.[OccName] OccName, r.[OccPhone] OccPhone, r.[OccPostcode] OccPostcode, r.[OccHouse] ,
r.[OccStreet] ,r.[OccVillage], r.[OccCountry] OccCountry, r.OccCountryName,r.[OccCounty] OccCounty, r.OccCountyName, r.[OccTown] OccTown, r.OccTownName, r.[OccVerified] OccVerified,
r.[IsApprovedAcountHolder] ,
r.[Identification],
r.[CoiInsuranceCompany] ,
r.[CoiPolicyNumber],
r.[CoiContactName] ,
r.[CoiExpiryDate],
r.[CoiPhone],
r.[CoiCertificateCopy] , r.[LicenceTestPassDate], r.[LicenceType],r.[LicenceGroups] ,
'' RenterAccountHolderFullName , r.[isHighRiskUser], r.[HighRiskUsrComment] HighRiskUsrComment, r.[VatNo] VatNo,r.[IptNo] IptNo, /*rt.RenterType*/ '' RenterTypeName,
/*fr.Name*/ '' FranchiseName,
r.[LicenceIsUkAddress], r.[ContactIsUkAddress], r.[OccIsUkAddress] ,
r.[MainDriverId] MainDriverId,'' OccVerifiedText,r.[FranchiseId],
r.IsPermissionToSpeak , r.InsuranceProvider, r.InsuranceCoverNote, r.InsuranceInsuranceExpiryDate, r.InsurancePhone, r.InsuranceContract, r.IsInsuranceSubmissionCompleted,
r.Note,ra.RACount RACount, res.ResCount ResCount,
ROW_NUMBER() OVER (PARTITION BY r.[Id] ORDER BY r.[Id] desc) AS RowNum
FROM AgreementUsers r WITH (NOLOCK)
left join (Select tblRA.Id,tblRA.UserId,
count(tblRA.Id) OVER (
PARTITION BY tblRA.UserId /*tblRA.Id*/ Order by tblRA.UserId desc
) RACount FROM
(Select distinct ba.Id,ar.RenterUserId UserId
from BookingAgreements ba with (nolock)
join AgreementRenters ar with (nolock) on ba.Id = ar.AgreementId
where ba.FranchiseId = @franchiseId and ba.StatusId = 1 and isnull(ba.RentalAgreementId,'') <> ''
UNION
Select distinct ba.Id,ad.MainDriverUserId UserId
from BookingAgreements ba with (nolock)
join AgreementDrivers ad with (nolock) on ba.Id = ad.AgreementId
where ba.FranchiseId = @franchiseId and ba.StatusId = 1 and isnull(ba.RentalAgreementId,'') <> '') tblRA) ra on ra.UserId = r.Id

left join (Select tblRes.Id,tblRes.UserId,count(tblRes.Id) OVER (
PARTITION BY tblRes.UserId Order by tblRes.UserId desc
) ResCount FROM
(Select distinct ba.Id,ar.RenterUserId UserId
from BookingAgreements ba with (nolock)
join AgreementRenters ar with (nolock) on ba.Id = ar.AgreementId
where ba.FranchiseId = @franchiseId and ba.StatusId = 1 and isnull(ba.RentalAgreementId,'') = ''
UNION
Select distinct ba.Id,ad.MainDriverUserId UserId
from BookingAgreements ba with (nolock)
join AgreementDrivers ad with (nolock) on ba.Id = ad.AgreementId
where ba.FranchiseId = @franchiseId and ba.StatusId = 1 and isnull(ba.RentalAgreementId,'') = '') tblRes ) res on res.UserId = r.Id

Where
(isnull(@franchiseId,0) > 0 and r.FranchiseId = @franchiseId) and
(ISNULL(@firstName,'') = '' OR isnull(REPLACE(lower(FirstName),' ',''),'') like CONCAT(ISNULL(REPLACE(lower(@firstName),' ',''),''),'%')) AND
(ISNULL(@surName,'') = '' OR (isnull(REPLACE(lower(Surname),' ',''),'') like CONCAT(ISNULL(REPLACE(lower(@surName),' ',''),''),'%'))) AND
(ISNULL(@companyName,'') = '' OR isnull(REPLACE(lower(r.CompanyName),' ',''),'') like CONCAT(ISNULL(REPLACE(lower(@companyName),' ',''),''),'%'))
AND (ISNULL(@email,'') = '' OR r.[Email] = ISNULL(@email,''))
AND (ISNULL(@phone,'') = '' OR r.[PhoneNo] = ISNULL(@phone,''))
AND (ISNULL(@postcode,'') = '' OR r.[PostCode] = ISNULL(@postcode,''))
AND (ISNULL(@country,'') = '' OR replace(lower(r.[CountryName]),' ','') = ISNULL(replace(lower(@country),' ',''),''))
AND (ISNULL(@county,'') = '' OR replace(lower(r.[CountyName]),' ','') = ISNULL(replace(lower(@county),' ',''),''))
AND (ISNULL(@town,'') = '' OR replace(lower(r.[TownName]),' ','') = ISNULL(replace(lower(@town),' ',''),''))
AND (ISNULL(@note,'') = '' OR Replace(r.HighRiskUsrComment,' ','') like CONCAT('%',ISNULL(REPLACE(@note,' ',''),''),'%'))
AND (ISNULL(@renterType,'') = '' OR (ISNULL(@renterType,'') = 1 AND r.[RenterType] in (1,2)) OR (ISNULL(@renterType,'') = 3 AND r.[RenterType] = 3))
AND (ISNULL(@isHighRiskUser,'') = '' OR r.[isHighRiskUser] = @isHighRiskUser)
) tblD WHERE tblD.RowNum = 1
order by tblD.[Id] desc
OFFSET (@Take * @Skip) ROWS
FETCH NEXT @Take ROWS ONLY ;

END

and the query plain is mentioned below:

[type or paste code here](https://1drv.ms/u/s!AtPCgaqki20WhDY5VvrxjsUp4FE9?e=NWJEEV)

and my new query which I developed using CTE is as follow:

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGetCommonRenterListHOD]
@firstName nvarchar(250) = NULL,
@surName nvarchar(250) = NULL,
@title nvarchar(50) = NULL,
@companyName nvarchar(250) = NULL,
@phone nvarchar(50) = NULL,
@email nvarchar(250) = NULL,
@country nvarchar(250) = NULL,
@county nvarchar(250) = NULL,
@town nvarchar(250) = NULL,
@postcode nvarchar(250) = NULL,
@isHighRiskUser bit = NULL,
@note nvarchar(250) = NULL,
@franchiseId int = null,
@renterType int = NULL,
@Take int = 0,
@Skip int = 0
AS
BEGIN

declare @PageSize int = @Take;
declare @PageNumber int = @Skip;
SET NOCOUNT ON;
;WITH CTECount AS (
Select dr.Id, dr.AgreementStatus ,dr.RentalAgreementId,dr.UserId, dr.RACount, dr.ResCount, dr.FranchiseId
FROM
(
Select ba.Id,userDetail.UserId,ba.RentalAgreementId, ba.AgreementStatus, ba.FranchiseId,
ROW_NUMBER() over (partition by userDetail.UserId order by userDetail.UserId desc) RowNo ,
(case when ba.RentalAgreementId <> '' then Count(ba.Id)
OVER (Partition by (case when ba.RentalAgreementId <> '' then userDetail.UserId else 0 end)) else 0 end) RACount,
(case when ba.RentalAgreementId = '' then Count(ba.Id)
OVER (Partition by (case when ba.RentalAgreementId = '' then userDetail.UserId else 0 end)) else 0 end) ResCount
from BookingAgreements ba with (nolock)
outer apply
(
Select ar.AgreementId,ar.RenterUserId UserId, ar.FranchiseId
FROM
AgreementRenters ar with (nolock)
where ar.FranchiseId = ba.FranchiseId and ar.AgreementId = ba.Id
UNION
Select ad.AgreementId,ad.MainDriverUserId UserId, ad.FranchiseId
FROM AgreementDrivers ad with (nolock)
where ad.FranchiseId = ba.FranchiseId and ad.AgreementId = ba.Id
) userDetail
Where ba.FranchiseId = userDetail.FranchiseId and ba.AgreementStatus <> 2
) dr where dr.RowNo = 1
),
CTE_RecordRows AS(
Select tblD.Id,tblD.EncId,tblD.CompanyName,
tblD.CompanyAccountNo,
tblD.CompanyDetail,
tblD.FirstName, tblD.Surname,
tblD.RenterPhone ,
tblD.RenterEmail ,
tblD.PostCode,
tblD.CountyName,
tblD.CountryName,
tblD.TownName,
tblD.ContactPostCode,
tblD.RenterType,
tblD.isHighRiskUser,
tblD.HighRiskUsrComment,
tblD.FranchiseName FranchiseName,
tblD.RACount, tblD.ResCount,
tblD.FranchiseId
FROM
(SELECT r.Id ,
[dbo].ConvertToEncryptedId(r.Id) as 'EncId',
r.CompanyName,
r.CompanyAccountNo,
r.CompanyDetail,
r.FirstName, r.Surname,
r.PhoneNo RenterPhone,
r.Email RenterEmail,
r.PostCode,
r.CountyName,
(case when isnull(r.CountryName,'') = '-- Select Country --' then '' else r.CountryName end) CountryName,
r.TownName,
r.ContactPostCode,
r.RenterType,
r.isHighRiskUser,
r.HighRiskUsrComment,
frn.Name FranchiseName,
c.RACount,
c.ResCount,
r.FranchiseId
FROM AgreementUsers r WITH (NOLOCK)
inner join CTECount c on r.Id = c.UserId
inner join Franchise frn on r.FranchiseId = frn.Id
Where frn.Status_Id = 1 and
-- (isnull(@franchiseId,0) > 0 and r.FranchiseId = @franchiseId) and
(ISNULL(@firstName,'') = '' OR isnull(REPLACE(lower(FirstName),' ',''),'') like CONCAT(ISNULL(REPLACE(lower(@firstName),' ',''),''),'%')) AND
(ISNULL(@surName,'') = '' OR (isnull(REPLACE(lower(Surname),' ',''),'') like CONCAT(ISNULL(REPLACE(lower(@surName),' ',''),''),'%'))) AND
(ISNULL(@companyName,'') = '' OR isnull(REPLACE(lower(r.CompanyName),' ',''),'') like CONCAT(ISNULL(REPLACE(lower(@companyName),' ',''),''),'%'))
AND (ISNULL(@email,'') = '' OR r.[Email] = ISNULL(@email,''))
AND (ISNULL(@phone,'') = '' OR r.[PhoneNo] = ISNULL(@phone,''))
AND (ISNULL(@postcode,'') = '' OR r.[PostCode] = ISNULL(@postcode,''))
AND (ISNULL(@country,'') = '' OR replace(lower(r.[CountryName]),' ','') = ISNULL(replace(lower(@country),' ',''),''))
AND (ISNULL(@county,'') = '' OR replace(lower(r.[CountyName]),' ','') = ISNULL(replace(lower(@county),' ',''),''))
AND (ISNULL(@town,'') = '' OR replace(lower(r.[TownName]),' ','') = ISNULL(replace(lower(@town),' ',''),''))
AND (ISNULL(@note,'') = '' OR Replace(r.HighRiskUsrComment,' ','') like CONCAT('%',ISNULL(REPLACE(@note,' ',''),''),'%'))
AND (ISNULL(@renterType,'') = '' OR (ISNULL(@renterType,'') = 1 AND r.[RenterType] in (1,2)) OR (ISNULL(@renterType,'') = 3 AND r.[RenterType] = 3))
AND (ISNULL(@isHighRiskUser,'') = '' OR r.[isHighRiskUser] = @isHighRiskUser)
) tblD -- Where tblD.rowNo = 1
)

Select tblD.Id as 'Id', [dbo].ConvertToEncryptedId(tblD.Id) as 'EncId',
(CASE
WHEN ISNULL(tblD.RenterType, 0) = 3 THEN
ISNULL(tblD.CompanyName, '')
ELSE
ISNULL(tblD.FirstName, '') + ' ' + ISNULL(tblD.Surname, '')
END) as 'RenterAccountHolderFullName',

ISNULL(tblD.RenterPhone, '') as 'RenterPhone',
ISNULL(tblD.RenterEmail, '') as 'RenterEmail',
ISNULL(tblD.PostCode, '') as 'LicencePostCode',
ISNULL(tblD.CountryName, '') as 'CountryName',
ISNULL(tblD.CountryName, '') as 'CountyName',
ISNULL(tblD.TownName, '') as 'TownName',
ISNULL(tblD.ContactPostCode, '') as 'ContactPostCode',

(CASE
WHEN ISNULL(tblD.RenterType,0) = 3 THEN
'Company'
WHEN ISNULL(tblD.RenterType,0) = 1 THEN
'Individual / Sole Trade'
WHEN ISNULL(tblD.RenterType,0) = 2 THEN
'Individual / Sole Trade'
ELSE
'N/A'
END) as 'RenterTypeName',

ISNULL(tblD.isHighRiskUser, 0) as 'IshighRiskRenter',

(CASE
WHEN ISNULL(tblD.isHighRiskUser, 0) = 1 THEN
'Yes'
ELSE
'No'
END) as 'HighRiskUsrTxt',

ISNULL(tblD.HighRiskUsrComment, '') as 'HighRiskUsrComment',
ISNULL(tblD.CompanyAccountNo, '') as 'RenterCompanyAcNumber',
ISNULL(tblD.CompanyName, '') as 'RenterCompanyName',
ISNULL(tblD.CompanyDetail, '') as 'RenterCompanyDetails',
ISNULL(tblD.FirstName, '') as 'RenterFirstName',
ISNULL(tblD.Surname, '') as 'RenterSurname',
ISNULL(tblD.FranchiseName, '') as 'FranchiseName',
ISNULL(tblD.RACount, 0) as 'RACount',
ISNULL(tblD.ResCount, 0) as 'ResCount',
count(ISNULL(tblD.Id, 0)) over() as 'TotalCount',
ISNULL(tblD.FranchiseId, 0) as 'Franchise_Id'
FROM CTE_RecordRows tblD
order by tblD.[FranchiseName] asc
OFFSET (@Take * @Skip) ROWS
FETCH NEXT @Take ROWS ONLY OPTION (RECOMPILE);

END

and the query plan is mentioned below:

(https://1drv.ms/u/s!AtPCgaqki20WhDSTz9CQ7zvZhmsM?e=rldYeJ)

CREATE TABLE [dbo].[Franchise](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Franchise] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BookingAgreements](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FranchiseId] [int] NOT NULL,
[ReservationId] [varchar](9) NULL,
[RentalAgreementId] [varchar](9) NULL,
[AgreementStatus] [int] NULL,
[StatusId] [int] NULL,
[OrderNumber] [nvarchar](250) NULL,
[ChargedDays] [decimal](18, 2) NULL,
CONSTRAINT [PK_BookingAgreements] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[AgreementDrivers](
[AgreementId] [bigint] NOT NULL,
[DriverId] [bigint] NOT NULL,
[FranchiseId] [bigint] NOT NULL,
[StatusId] [int] NULL,
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[DriverPhone] [varchar](50) NULL,
[DriverTitle] [varchar](150) NULL,
[DriverFirstName] [varchar](150) NULL,
[DriverSurname] [varchar](150) NULL,
[DriverDOB] [datetime] NULL,
[DriverCompanyName] [varchar](150) NULL,
[DriverCompanyDetails] [varchar](500) NULL,
[DriverEmail] [varchar](150) NULL,
[DriverSources] [varchar](50) NULL,
[DriverPostCode] [varchar](50) NULL,
[DriverHouse] [varchar](250) NULL,
[DriverStreet] [varchar](250) NULL,
[DriverVillage] [varchar](50) NULL,
[DriverCountry] [int] NULL,
[DriverCounty] [int] NULL,
[DriverTown] [int] NULL,
[DriverDetailTownName] [nvarchar](250) NULL,
[DriverDetailCountyName] [nvarchar](250) NULL,
[DriverCountryName] [varchar](250) NULL,
[LicenceCopyMade] [bit] NULL,
[LicenceDetailCountry] [varchar](100) NULL,
[LicenceType] [int] NULL,
[LicenceTestPassDate] [datetime] NULL,
[LicenceExpiryDate] [datetime] NULL,
[RenterId] [int] NULL,
[MainDriverUserId] [int] NULL,
CONSTRAINT [PK_AgreementDrivers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[AgreementRenters](
[Id] [int] IDENTITY(1,1) NOT NULL,
[AgreementId] [int] NULL,
[FranchiseId] [int] NULL,
[isRenterAgreedAccHolder] [bit] NULL,
[RenterTypeId] [int] NULL,
[isHighRiskUser] [bit] NULL,
[MainDriverId] [int] NULL,
[RenterSources] [varchar](50) NULL,
[RenterTitle] [varchar](150) NULL,
[RenterFirstName] [varchar](150) NULL,
[RenterSurname] [varchar](150) NULL,
[RenterDOB] [datetime] NULL,
[RenterPhone] [varchar](50) NULL,
[RenterEmail] [varchar](150) NULL,
[RenterCompanyName] [varchar](150) NULL,
[RenterCompanyDetails] [varchar](500) NULL,
[RenterCompanyAcNumber] [varchar](50) NULL,
[LicencePostCode] [varchar](50) NULL,
[LicenceHouse] [varchar](250) NULL,
[LicenceStreet] [varchar](250) NULL,
[LicenceVillage] [varchar](50) NULL,
[LicenceCountry] [int] NULL,
[LicenceCounty] [int] NULL,
[LicenceTown] [int] NULL,
[LicenceTownName] [nvarchar](250) NULL,
[LicenceCountyName] [nvarchar](250) NULL,
[LicenceCountryName] [nvarchar](250) NULL,
[LicenceNumber] [varchar](50) NULL,
[LicenceDetails] [bit] NULL,
[LicenceType] [int] NULL,
[LicenceIssuedBy] [varchar](50) NULL,
[LicenceIssuedCountry] [varchar](50) NULL,
[LicenceTestPassDate] [datetime] NULL,
[LicenceExpiryDate] [datetime] NULL,
[LicenceSeen] [bit] NULL,
[LicenceWebCheck] [bit] NULL,
[LicenceETDSeen] [bit] NULL,
[LicenceCopyMade] [bit] NULL,
[LicenceGroups] [varchar](50) NULL,
[RenterLicenceIsUkAddress] [bit] NOT NULL,
[ContactPostCode] [varchar](50) NULL,
[ContactAddressType] [varchar](50) NULL,
[ContactAddress1] [varchar](250) NULL,
[ContactAddress2] [varchar](250) NULL,
[ContactAddress3] [varchar](250) NULL,
[ContactCountry] [int] NULL,
[ContactCounty] [int] NULL,
[ContactTown] [int] NULL,
[ContactMethod] [varchar](50) NULL,
[RenterContactIsUkAddress] [bit] NOT NULL,
[ContactTownName] [nvarchar](250) NULL,
[ContactCountyName] [nvarchar](250) NULL,
[ContactCountryName] [nvarchar](250) NULL,
[Occupation] [varchar](50) NULL,
[OccEmploymentType] [varchar](150) NULL,
[OccName] [nvarchar](250) NULL,
[OccPhone] [varchar](50) NULL,
[OccPostcode] [nvarchar](50) NULL,
[OccAddress1] [varchar](250) NULL,
[OccAddress2] [varchar](250) NULL,
[OccAddress3] [varchar](250) NULL,
[OccCountry] [int] NULL,
[OccCounty] [int] NULL,
[OccTown] [int] NULL,
[OccVerified] [bit] NULL,
[RenterOccIsUkAddress] [bit] NOT NULL,
[OccTownName] [nvarchar](250) NULL,
[OccCountyName] [nvarchar](250) NULL,
[OccCountryName] [nvarchar](250) NULL,
[RenterIdentification] [varchar](max) NULL,
[CoiInsuranceCompany] [varchar](50) NULL,
[CoiPolicyNumber] [varchar](50) NULL,
[CoiContactName] [varchar](50) NULL,
[CoiExpiryDate] [datetime] NULL,
[CoiPhone] [varchar](20) NULL,
[CoiCertificateCopy] [bit] NULL,
[HighRiskUsrComment] [nvarchar](500) NULL,
[VatNo] [varchar](50) NULL,
[IptNo] [varchar](50) NULL,
[StatusId] [int] NULL,
[IsPermissionToSpeak] [bit] NULL,
[InsuranceProvider] [varchar](100) NULL,
[InsuranceCoverNote] [varchar](250) NULL,
[InsuranceInsuranceExpiryDate] [datetime] NULL,
[InsurancePhone] [varchar](50) NULL,
[InsuranceContract] [varchar](250) NULL,
[IsInsuranceSubmissionCompleted] [bit] NULL,
[RenterUserId] [int] NULL,
[Note] [nvarchar](500) NULL,
[CreatedBy] [int] NULL,
[ModifiedBy] [int] NULL,
[CreatedDate] [datetime] NULL,
[ModifiedDate] [datetime] NULL,
[IsGlobalUserIgnored] [int] NULL,
CONSTRAINT [PK_AgreementRenters] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


DROP TABLE [dbo].[AgreementUsers]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AgreementUsers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[IsRenter] [bit] NULL,
[IsMainDriver] [bit] NOT NULL,
[IsAdditionalDriver] [bit] NULL,
[Title] [varchar](150) NULL,
[FirstName] [varchar](150) NULL,
[MidName] [varchar](150) NULL,
[Surname] [varchar](150) NULL,
[DOB] [datetime] NULL,
[RenterId] [int] NULL,
[FranchiseId] [int] NULL,
[MainDriverId] [int] NULL,
[AdditionalDriverId] [int] NULL,
[RentalType] [int] NULL,
[RenterType] [int] NULL,
[RenterCompanyType] [smallint] NULL,
[CompanyAccountNo] [varchar](150) NULL,
[CompanyName] [varchar](150) NULL,
[CompanyDetail] [varchar](150) NULL,
[StatusId] [int] NULL,
[CreatedOn] [datetime] NULL,
[ModifiedOn] [datetime] NULL,
[CreatedUserId] [int] NULL,
[ModifiedUserId] [int] NULL,
[PostCode] [varchar](150) NULL,
[PhoneNo] [varchar](150) NULL,
[Email] [varchar](150) NULL,
[Street] [varchar](250) NULL,
[LicenceNo] [varchar](150) NULL,
[Sources] [varchar](50) NULL,
[IsApprovedAcountHolder] [bit] NULL,
[LicenceIsUkAddress] [bit] NOT NULL,
[House] [varchar](250) NULL,
[Village] [varchar](50) NULL,
[Country] [int] NULL,
[County] [int] NULL,
[Town] [int] NULL,
[CountryName] [varchar](250) NULL,
[LicenceDetails] [bit] NULL,
[LicenceType] [int] NULL,
[LicenceIssuedBy] [varchar](50) NULL,
[LicenceIssuedCountry] [varchar](50) NULL,
[LicenceTestPassDate] [datetime] NULL,
[LicenceExpiryDate] [datetime] NULL,
[LicenceSeen] [bit] NULL,
[WebCheck] [bit] NULL,
[ETDSeen] [bit] NULL,
[CopyMade] [bit] NULL,
[LicenceGroups] [varchar](50) NULL,
[TownName] [nvarchar](250) NULL,
[CountyName] [nvarchar](250) NULL,
[Occupation] [varchar](50) NULL,
[OccEmploymentType] [varchar](150) NULL,
[OccName] [nvarchar](250) NULL,
[OccPhone] [varchar](50) NULL,
[OccPostcode] [nvarchar](50) NULL,
[OccHouse] [varchar](250) NULL,
[OccStreet] [varchar](250) NULL,
[OccVillage] [varchar](250) NULL,
[OccCountry] [int] NULL,
[OccTown] [int] NULL,
[OccVerified] [bit] NULL,
[OccIsUkAddress] [bit] NULL,
[OccTownName] [nvarchar](250) NULL,
[OccCountyName] [nvarchar](250) NULL,
[OccCountryName] [nvarchar](250) NULL,
[Identification] [varchar](max) NULL,
[IsRenterMainDriver] [bit] NULL,
[HasMedicalIssue] [bit] NULL,
[HasAccident] [bit] NULL,
[HasConviction] [bit] NULL,
[HasEverRefusedInsurance] [bit] NULL,
[HasVehicleOrTrailerForCarriage] [bit] NULL,
[OccCounty] [int] NULL,
[Note] [nvarchar](500) NULL,
[ContactPostCode] [varchar](50) NULL,
[ContactAddressType] [varchar](50) NULL,
[ContactAddress1] [varchar](250) NULL,
[ContactAddress2] [varchar](250) NULL,
[ContactAddress3] [varchar](250) NULL,
[ContactCountry] [int] NULL,
[ContactCounty] [int] NULL,
[ContactTown] [int] NULL,
[ContactMethod] [varchar](50) NULL,
[ContactIsUkAddress] [bit] NOT NULL,
[ContactTownName] [nvarchar](250) NULL,
[ContactCountyName] [nvarchar](250) NULL,
[ContactCountryName] [nvarchar](250) NULL,
[CoiInsuranceCompany] [varchar](50) NULL,
[CoiPolicyNumber] [varchar](50) NULL,
[CoiContactName] [varchar](50) NULL,
[CoiExpiryDate] [datetime] NULL,
[CoiPhone] [varchar](20) NULL,
[CoiCertificateCopy] [bit] NULL,
[BKIsOwnInsurance] [bit] NULL,
[isHighRiskUser] [bit] NULL,
[HighRiskUsrComment] [nvarchar](500) NULL,
[VatNo] [varchar](50) NULL,
[IptNo] [varchar](50) NULL,
[IsPermissionToSpeak] [bit] NULL,
[InsuranceProvider] [varchar](100) NULL,
[InsuranceCoverNote] [varchar](250) NULL,
[InsuranceInsuranceExpiryDate] [datetime] NULL,
[InsurancePhone] [varchar](50) NULL,
[InsuranceContract] [varchar](250) NULL,
[IsInsuranceSubmissionCompleted] [bit] NULL,
CONSTRAINT [PK_AgreementUsers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

REPLACE(), & LOWER() are being used for matching in all cases.

ISNULL() can be ignored except in where conditions.

and These are the online plans with query

Newly created plan ( with query) :
https://www.brentozar.com/pastetheplan/?id=HkPsA1dBj

First plan :
https://www.brentozar.com/pastetheplan/?id=HJlN1xuHi

2

I suggest reading Kimberly's article here:

https://www.sqlskills.com/blogs/kimberly/high-performance-procedures/

There's also video of her presentation on it:

The ISNULL() use in the WHERE clause is not helping. Kimberly discusses options for dynamic WHERE conditions that eliminate the need for them and can better use any indexes on the columns. ISNULL() will force a table to be scanned, especially with all of the conditions you have.

You can simplify some of it by simply modifying the parameters:

SET @email=ISNULL(@email,'')

Then your WHERE condition becomes:

AND @email= '' OR r.[Email] = @email

Based on the query plan, the most expensive operation in the CTE is the UNION, as it causes a DISTINCT SORT operator. As you are only using 3 columns each from AgreementRenters and AgreementDrivers, you can probably create an index containing those 3 columns and see some improvement. It would scan the index rather than the entire table.

And if those 3 columns represent a unique combination/key, making the index unique would be ideal, as the optimizer can probably avoid the distinct sort and use a stream aggregate instead.

After you do that, you may want to look at removing the UNION and querying each of those table with a separate OUTER APPLY for each. In the SELECT clause where userDetail is referenced, you could wrap each APPLY query in an ISNULL to return the relevant columns. I apologize if this sounds vague, I'm still wrapping my head around the query.

Regarding the ROW_NUMBER() and rowNo=1 stuff, consider replacing those with a CROSS APPLY(SELECT TOP 1 FROM tbl ORDER BY <order by from ROW_NUMBER()> instead. It's possible it can use an index seek to return just the one row you need.

Some other tips/suggestions:

  1. Thank you for posting the query plan and DDL of your tables and procedures. However, please do not link to files on OneDrive or other file sharing sites. The plans posted on Paste The Plan are sufficient. Files posted for download cannot be considered safe, virus-free, etc.

  2. Recommend not using single quotes ' to delimit identifiers. Square brackets are preferred: Select tblD.Id as [Id] as they cannot be misinterpreted by users or the query parser.

  3. If there are indexes on the tables, please include their definitions in the DDL that you post. It's easier than trying to glean that info from the query plan.

  4. If you are not using Plan Explorer, I highly recommend it:

It's free and is very easy to use to find your most expensive operations, especially in complex plans. There are videos on their site, and also in the PASSTV YouTube channel that's hosting Kimberly's video.

3

thank you robert.

Please see my concerns If someone can recommend

Point#1 : spGetCommonRenterListHOD is ny new query. Will it effect to rename the stored procedure as it starts from sp (which shouldn't be) .

Point#2:

Avoid too many OR conditions. Please try to rewrite where clause by removing unnecessary conditions or changing to use a function such as case or decode.

Does it mean I need to must need to create a dynamic query to make filter if I remove OR expression

like I mentioned below

 (ISNULL(@firstName,'') = '' OR  isnull(REPLACE(lower(FirstName),' ',''),'') like CONCAT(ISNULL(REPLACE(lower(@firstName),' ',''),''),'%'))

and

(CASE
WHEN ISNULL(tblD.RenterType, 0) = 3 THEN

Second thing is that : Would I need to remove isnull, replace and lower function because It causes a full scan (replace, lower and isnull are the reasons to ignore the indexes) ?

Point#3:
I am using a ConvertToEncryptedId scalar function in my query to directly encryption of Id column. Do I need to use it ? If yes then how can I use it efficiently for a single page size ( 25 to 100 records) .
See my following function If somebody can improve it or suggest something better?

ALTER FUNCTION [dbo].[ConvertToEncryptedId](@pId varchar(50))
RETURNS  varchar(200)
AS BEGIN

Declare  @fullId varchar(50) = '';
Declare @localEncId varchar(200)  = '';
SET @fullId = Concat(@pId,'$',@pId);

SET @localEncId = (SELECT CAST(@fullId as varbinary(max)) FOR XML PATH(''), BINARY BASE64);

Return @localEncId;
    
END

Point#4: Some experts said following union is the highest performance issue under CTECount.

AgreementRenters ar with (nolock)
where ar.FranchiseId = ba.FranchiseId and ar.AgreementId = ba.Id
UNION
Select ad.AgreementId,ad.MainDriverUserId UserId, ad.FranchiseId
FROM AgreementDrivers ad with (nolock)

Union is not an issue, I can use the left joins like as follow

Select ba.Id,ar.RenterUserId,ad.MainDriverUserId,ba.RentalAgreementId, ba.AgreementStatus, ba.FranchiseId,
ROW_NUMBER() over (partition by userDetail.UserId order by userDetail.UserId desc) RowNo ,
(case when ba.RentalAgreementId <> '' then Count(ba.Id)
OVER (Partition by (case when ba.RentalAgreementId <> '' then ar.RenterUserId,ad.MainDriverUserId else 0 end)) else 0 end) RACount,
(case when ba.RentalAgreementId = '' then Count(ba.Id)
OVER (Partition by (case when ba.RentalAgreementId = '' then ar.RenterUserId,ad.MainDriverUserId else 0 end)) else 0 end) ResCount
from BookingAgreements ba with (nolock)
left join AgreementRenters ar with (nolock) on ba.Id = ar.AgreementId
left join AgreementDrivers ad with (nolock) on ba.Id = ad.AgreementId

Again, there are now two columns ( ar.RenterUserId,ad.MainDriverUserId) which need to be joined uniquely

Point#5:

and see partition by Will it give the optimum performance wise results because of using the case statements in it . Kindly suggest or recommend.

This part is crucial as It give the accurate counts.

(case when ba.RentalAgreementId <> '' then Count(ba.Id)
OVER (Partition by (case when ba.RentalAgreementId <> '' then userDetail.UserId else 0 end)) else 0 end) RACount,

and

Point#6:

The paging related thing . Can We improve it more as well ?

order by tblD.[FranchiseName] asc
OFFSET (@Take * @Skip) ROWS
FETCH NEXT @Take ROWS ONLY OPTION (RECOMPILE)

4

I'll make a few additional comments, I don't know how helpful they'll be, so I'll apologize in advance if they're not.

I've been struggling to understand the flow of the queries/procedures, mostly due to the nested SELECT statements. Generally speaking it's better to avoid doing SELECT ... FROM (SELECT ... FROM). You're already using CTEs, it would be easier to read the overall query if those subqueries were rewritten as CTEs. Unfortunately I can't figure out an easy example from what you've posted.

To address the points you listed:

  1. Renaming the procedure won't hurt anything, as long as you keep copies of the SQL code somewhere safe. A source control system like Git is preferred, so that you can do proper version control and maintain history of the code, and can revert to a past version if needed.

  2. You definitely have too many OR conditions, and the entire reasoning behind them is flawed. I'll come back to this later.

  3. That function isn't encrypting anything, it's simply encoding a value as Base64, using FOR XML in SQL Server. You can almost certainly do the same thing inline in your query without using the UDF: SELECT tblD.ID as [ID], (SELECT tblD.Id FOR XML PATH(''), BINARY BASE64) as [EncId]
    ... rest of query

  4. If by "some experts" you mean me, yes, the UNION clause had the most expensive operator in the execution plan. If you can replace it with a functionally equivalent statement, and/or add the indexes I suggested, you should reduce the cost and improve the overall performance.

  5. Regarding performance around PARTITION BY and CASE expressions: I don't think I understand what you're asking for. The wording is unclear. You're trying to do a COUNT based on a specific condition and additionally partitioning by that condition. There may be another way of getting that count, possibly using a CROSS APPLY, but I would need to see an example of raw data and the expected results.

  6. Paging always performs badly when the OFFSET size gets to a certain point, because the query engine has to process OFFSET + Page size and then throw away OFFSET rows. I don't have an easy answer for this one, but can suggest reading a few articles:

There's also some historical methods you might want to research:

The last 2 don't use OFFSET/FETCH as it wasn't available at the time of writing, however it covers additional details. You should also check any linked articles in there as well.

I mentioned I would address the "too many OR conditions" in another reply, it will have to wait until tomorrow, I'm traveling out of town and am jet lagged at the moment.

If you haven't already done so, PLEASE read the blog by Kimberly Tripp, and if you can watch the video please do so as well. She will probably explain it better than I can. Even if you can't rewrite the procedure to be dynamically generated, you'll get a better grasp on what the underlying problem is. Hopefully I can spend some time composing an alternative that you can more easily test.

5

Ok. Thank you very much. Much Appreciated. I am again re-creating a better query with dynamic where filter.

The MS Sql server at our production is : Microsoft SQL Server 2017 (RTM) - Microsoft Corporation Web Edition (64-bit)

6

Hi Robert_volk,
I have developed a new query by adopting your recommendations
Please see my newly developed/created query below:

Is it good enough now ? Can we improve it further ? I think it should be refined/ optimized more.

It is still taking 24 seconds to populate the results.

and this is the sql server plan

https://www.brentozar.com/pastetheplan/?id=HkYZ899rs

   Declare @PageNumber INT = 1;
  Declare @PageSize   INT = 100;
DECLARE
      @s nvarchar(MAX) = N'',
	  @Where NVARCHAR(MAX) = N'', 
	  @paging NVARCHAR(MAX) = N'', 
      @firstName nvarchar(100) = N'omer';
      SET @s = N' ;WITH CTERA AS ( 
	 Select  ba.[Id],ba.[RentalAgreementId],ba.[AgreementStatus],ba.[FranchiseId], 1 [IsRA], fr.Name [FranchiseName] 
	 FROM BookingAgreements ba (nolock)
	 Inner join Franchise fr (nolock) on ba.FranchiseId = fr.Id
	 Where ba.StatusId = 1 and fr.Status_Id = 1 and ba.RentalAgreementId is not null
  ),
  CTERES AS ( 
	 Select  ba.[Id],ba.[RentalAgreementId],ba.[AgreementStatus], ba.[FranchiseId], 1 [IsRes],fr.Name [FranchiseName]
	 FROM BookingAgreements ba (nolock)
	 Inner join Franchise fr (nolock) on ba.FranchiseId = fr.Id
	 Where ba.StatusId = 1 and fr.Status_Id = 1 and ba.RentalAgreementId is null
  )	
 ,CTEAgRenters AS ( 
	Select ar.[AgreementId],ar.RenterUserId [UserId],ar.[FranchiseId], nullif(ra.FranchiseName,res.FranchiseName) [FranchiseName],
	Count(ra.IsRA) OVER (Partition by ar.RenterUserId) RACount,
	Count(res.IsRes) OVER (Partition by ar.RenterUserId) ResCount
	FROM AgreementRenters ar (nolock)
	left join CTERA ra on ra.Id = ar.AgreementId and ra.FranchiseId = ar.FranchiseId
	left join CTERES res on res.Id = ar.AgreementId and res.FranchiseId = ar.FranchiseId
	UNION ALL
	Select ad.[AgreementId],ad.MainDriverUserId [UserId],ad.[FranchiseId],nullif(ra.FranchiseName,res.FranchiseName) [FranchiseName],
	Count(ra.IsRA) OVER (Partition by ad.MainDriverUserId) RACount,
	Count(res.IsRes) OVER (Partition by ad.MainDriverUserId) ResCount
	FROM AgreementDrivers ad (nolock)
	left join CTERA ra on ra.Id = ad.AgreementId and ra.FranchiseId = ad.FranchiseId
	left join CTERES res on res.Id = ad.AgreementId and res.FranchiseId = ad.FranchiseId
  ) 
  
  SELECT r.[Id] ,
 (Select CAST(Concat(r.Id,''$'',r.Id) as varbinary(50)) FOR XML PATH(''''), BINARY BASE64) as [EncId] ,
 (CASE   
  WHEN r.RenterType = 3 THEN
	r.CompanyName
  ELSE
  r.FirstName + '' '' + r.Surname
END) as [RenterAccountHolderFullName],
r.CompanyName [RenterCompanyName],
r.CompanyAccountNo [RenterCompanyAcNumber],
r.CompanyDetail [RenterCompanyDetails],
r.FirstName [RenterFirstName], r.Surname [RenterSurname],
r.PhoneNo [RenterPhone],
r.Email [RenterEmail],
r.PostCode [LicencePostCode],
r.[CountyName],
(case when r.CountryName = ''-- Select Country --'' then '''' else r.CountryName end) [CountryName],
r.[TownName],
r.[ContactPostCode],
(CASE   
  WHEN r.RenterType = 3 THEN
  ''Company''
  WHEN r.RenterType = 1 THEN
  ''Individual / Sole Trade''
  WHEN r.RenterType = 2 THEN
  ''Individual / Sole Trade''
  ELSE
  ''N/A''
END) as [RenterTypeName],
r.isHighRiskUser [IshighRiskRenter],
(CASE   
  WHEN r.isHighRiskUser = 1 THEN
  ''Yes''
  ELSE
  ''No''
END) as [HighRiskUsrTxt],
r.[HighRiskUsrComment],
c.[FranchiseName],
c.[RACount],
c.[ResCount],
count(r.Id) over() as [TotalCount],
r.[FranchiseId]
FROM AgreementUsers r WITH (NOLOCK)
inner join CTEAgRenters c on r.Id = c.UserId
'; 
 
 IF @firstName <> '' 
 SET @Where = N' FirstName like @firstName + ''%''';


 If(@Where <> '' )
   Set @s = @s + ' Where ' + @Where;

   /*
 (ISNULL(@firstName,'') = '' OR  isnull(REPLACE(lower(FirstName),' ',''),'') like CONCAT(ISNULL(REPLACE(lower(@firstName),' ',''),''),'%')) AND
	 (ISNULL(@surName,'') = '' OR (isnull(REPLACE(lower(Surname),' ',''),'')  like CONCAT(ISNULL(REPLACE(lower(@surName),' ',''),''),'%'))) AND
	 (ISNULL(@companyName,'') = '' OR isnull(REPLACE(lower(r.CompanyName),' ',''),'')  like CONCAT(ISNULL(REPLACE(lower(@companyName),' ',''),''),'%'))		
	AND (ISNULL(@email,'') = '' OR  r.[Email] = ISNULL(@email,''))		
	AND (ISNULL(@phone,'') = '' OR  r.[PhoneNo] = ISNULL(@phone,''))		
	AND (ISNULL(@postcode,'') = '' OR  r.[PostCode] = ISNULL(@postcode,'')) 
	AND (ISNULL(@country,'') = '' OR  replace(lower(r.[CountryName]),' ','') = ISNULL(replace(lower(@country),' ',''),'')) 
	AND (ISNULL(@county,'') = '' OR  replace(lower(r.[CountyName]),' ','') = ISNULL(replace(lower(@county),' ',''),'')) 
	AND (ISNULL(@town,'') = '' OR  replace(lower(r.[TownName]),' ','') = ISNULL(replace(lower(@town),' ',''),'')) 
	AND (ISNULL(@note,'') = '' OR  Replace(r.HighRiskUsrComment,' ','') like CONCAT('%',ISNULL(REPLACE(@note,' ',''),''),'%'))
	AND (ISNULL(@renterType,'') = '' OR (ISNULL(@renterType,'') = 1 AND r.[RenterType] in (1,2)) OR (ISNULL(@renterType,'') = 3 AND r.[RenterType] = 3)) 
	AND (ISNULL(@isHighRiskUser,'') = '' OR  r.[isHighRiskUser] = @isHighRiskUser)
	*/
SET @paging = N'
	Order by r.Id desc
 OFFSET @PageSize * ( @PageNumber - 1 ) ROWS
  FETCH NEXT @PageSize ROWS ONLY
 OPTION (RECOMPILE);';

 If(@paging <> '' )
   Set @s = @s + ' ' + @paging;

  EXEC sys.sp_executesql @s, N'@firstName nvarchar(100),@PageSize INT, @PageNumber INT ',@firstName,@PageSize,@PageNumber;

and this is the plain query

    Declare @PageNumber INT = 1;
  Declare @PageSize   INT = 100;
DECLARE
      @s nvarchar(MAX) = N'',
	  @Where NVARCHAR(MAX) = N'', 
	  @paging NVARCHAR(MAX) = N'', 
      @firstName nvarchar(40) = N'omer';
 
 ;WITH CTERA AS (  
 Select  ba.[Id],ba.[RentalAgreementId],ba.[AgreementStatus],ba.[FranchiseId], 1 [IsRA], fr.Name [FranchiseName]  
 FROM BookingAgreements ba (nolock)   
 Inner join Franchise fr (nolock) on ba.FranchiseId = fr.Id   
 Where ba.StatusId = 1 and fr.Status_Id = 1 and ba.RentalAgreementId is not null 
 ),    
 CTERES AS (     
 Select  ba.[Id],ba.[RentalAgreementId],ba.[AgreementStatus], ba.[FranchiseId], 1 [IsRes],fr.Name [FranchiseName]    
 FROM BookingAgreements ba (nolock)    
 Inner join Franchise fr (nolock) on ba.FranchiseId = fr.Id    
 Where ba.StatusId = 1 and fr.Status_Id = 1 and ba.RentalAgreementId is null    ) 
 ,CTEAgRenters AS (
 Select ar.[AgreementId],ar.RenterUserId [UserId],ar.[FranchiseId],
 nullif(ra.FranchiseName,res.FranchiseName) [FranchiseName],   
 Count(ra.IsRA) OVER (Partition by ar.RenterUserId) RACount,   
 Count(res.IsRes) OVER (Partition by ar.RenterUserId) ResCount   
 FROM AgreementRenters ar (nolock)   
 left join CTERA ra on ra.Id = ar.AgreementId and ra.FranchiseId = ar.FranchiseId   
 left join CTERES res on res.Id = ar.AgreementId and res.FranchiseId = ar.FranchiseId   
 UNION ALL  
 Select ad.[AgreementId],ad.MainDriverUserId [UserId],ad.[FranchiseId],nullif(ra.FranchiseName,res.FranchiseName) [FranchiseName],   
 Count(ra.IsRA) OVER (Partition by ad.MainDriverUserId) RACount,   
 Count(res.IsRes) OVER (Partition by ad.MainDriverUserId) ResCount   
 FROM AgreementDrivers ad (nolock)   
 left join CTERA ra on ra.Id = ad.AgreementId and ra.FranchiseId = ad.FranchiseId   
 left join CTERES res on res.Id = ad.AgreementId and res.FranchiseId = ad.FranchiseId    )         
 SELECT r.[Id] ,   (Select CAST(Concat(r.Id,'$',r.Id) as varbinary(50)) FOR XML PATH(''), BINARY BASE64) as [EncId] ,  
 (CASE       WHEN r.RenterType = 3 THEN   r.CompanyName    ELSE    r.FirstName + ' ' + r.Surname  END) as [RenterAccountHolderFullName], 
 r.CompanyName [RenterCompanyName],  r.CompanyAccountNo [RenterCompanyAcNumber],  r.CompanyDetail [RenterCompanyDetails],  
 r.FirstName [RenterFirstName], r.Surname [RenterSurname],  r.PhoneNo [RenterPhone], 
 r.Email [RenterEmail],  r.PostCode [LicencePostCode],  r.[CountyName],  
 (case when r.CountryName = '-- Select Country --' then '' else r.CountryName end) [CountryName],  r.[TownName],  r.[ContactPostCode],
 (CASE       WHEN r.RenterType = 3 THEN    'Company'    WHEN r.RenterType = 1 THEN    'Individual / Sole Trade'    
 WHEN r.RenterType = 2 THEN    'Individual / Sole Trade'    ELSE    'N/A'  END) as [RenterTypeName],  r.isHighRiskUser [IshighRiskRenter],
 (CASE       WHEN r.isHighRiskUser = 1 THEN    'Yes'    ELSE    'No'  END) as [HighRiskUsrTxt],  r.[HighRiskUsrComment], 
 c.[FranchiseName],  c.[RACount],  c.[ResCount],  count(r.Id) over() as [TotalCount],  r.[FranchiseId] 
 FROM AgreementUsers r WITH (NOLOCK)  
 inner join CTEAgRenters c on r.Id = c.UserId   
 Where  FirstName like @firstName + '%'   
 Order by r.Id desc   
 OFFSET @PageSize * ( @PageNumber - 1 ) ROWS   
 FETCH NEXT @PageSize ROWS ONLY   
 OPTION (RECOMPILE);
7

This is my final query. I have finalized it.
If anybody has any issue in it, Please share your feedback now.
I tired to implement all your suggestions and recommendations.
Is there anything left now which can be improved ?

Declare @firstName nvarchar(250) = 'omer';	
Declare	@surName nvarchar(250) = NULL;--'mehboob';	
Declare	@title nvarchar(50) = NULL;	
Declare	@companyName nvarchar(250) = NULL;	
Declare	@phone nvarchar(50) = NULL; --'03214189055'; -- NULL;
Declare	@email nvarchar(250) = NULL; --'omer.tek@gmail.com';
Declare	@country nvarchar(250) = NULL;--'UK';		
Declare	@county nvarchar(250) = NULL; --'westyorkshire';		
Declare	@town nvarchar(250) = NULL; --'leeds';		
Declare	@postcode nvarchar(250) = NULL; --'ls178qd';		
Declare	@isHighRiskUser bit = NULL;--1;
Declare	@note nvarchar(250) = NULL; --'one';
Declare	@franchiseId int = null;
Declare	@renterType int = 1;
Declare	@Take int = 25;
Declare	@Skip int = 1;

Declare @PageNumber INT = @Skip;
Declare @PageSize   INT = @Take;


DECLARE
      @s nvarchar(MAX) = N'',
	  @Where NVARCHAR(MAX) = N'', 
	  @paging NVARCHAR(MAX) = N''; 
      SET @s = N' ;WITH CTERA AS ( 
	 Select  ba.[Id],ba.[RentalAgreementId],ba.[AgreementStatus],ba.[FranchiseId], 1 [IsRA], fr.Name [FranchiseName] 
	 FROM BookingAgreements ba (nolock)
	 Inner join Franchise fr (nolock) on ba.FranchiseId = fr.Id
	 Where ba.StatusId = 1 and fr.Status_Id = 1 and ba.RentalAgreementId is not null
  ),
  CTERES AS ( 
	 Select  ba.[Id],ba.[RentalAgreementId],ba.[AgreementStatus], ba.[FranchiseId], 1 [IsRes],fr.Name [FranchiseName]
	 FROM BookingAgreements ba (nolock)
	 Inner join Franchise fr (nolock) on ba.FranchiseId = fr.Id
	 Where ba.StatusId = 1 and fr.Status_Id = 1 and ba.RentalAgreementId is null
  )	
 ,CTEAgRenters AS ( 
	Select ar.[AgreementId],ar.RenterUserId [UserId],ar.[FranchiseId], nullif(ra.FranchiseName,res.FranchiseName) [FranchiseName],
	Count(ra.IsRA) OVER (Partition by ar.RenterUserId) RACount,
	Count(res.IsRes) OVER (Partition by ar.RenterUserId) ResCount
	FROM AgreementRenters ar (nolock)
	left join CTERA ra on ra.Id = ar.AgreementId and ra.FranchiseId = ar.FranchiseId
	left join CTERES res on res.Id = ar.AgreementId and res.FranchiseId = ar.FranchiseId
	UNION ALL
	Select ad.[AgreementId],ad.MainDriverUserId [UserId],ad.[FranchiseId],nullif(ra.FranchiseName,res.FranchiseName) [FranchiseName],
	Count(ra.IsRA) OVER (Partition by ad.MainDriverUserId) RACount,
	Count(res.IsRes) OVER (Partition by ad.MainDriverUserId) ResCount
	FROM AgreementDrivers ad (nolock)
	left join CTERA ra on ra.Id = ad.AgreementId and ra.FranchiseId = ad.FranchiseId
	left join CTERES res on res.Id = ad.AgreementId and res.FranchiseId = ad.FranchiseId
  )   
  SELECT r.[Id] ,
 (Select CAST(Concat(r.Id,''$'',r.Id) as varbinary(50)) FOR XML PATH(''''), BINARY BASE64) as [EncId] ,
 (CASE   
  WHEN r.RenterType = 3 THEN
	r.CompanyName
  ELSE
  r.FirstName + '' '' + r.Surname
END) as [RenterAccountHolderFullName],
r.CompanyName [RenterCompanyName],
r.CompanyAccountNo [RenterCompanyAcNumber],
r.CompanyDetail [RenterCompanyDetails],
r.FirstName [RenterFirstName], r.Surname [RenterSurname],
r.PhoneNo [RenterPhone],
r.Email [RenterEmail],
r.PostCode [LicencePostCode],
r.[CountyName],
(case when r.CountryName = ''-- Select Country --'' then '''' else r.CountryName end) [CountryName],
r.[TownName],
r.[ContactPostCode],
(CASE   
  WHEN r.RenterType = 3 THEN
  ''Company''
  WHEN r.RenterType = 1 THEN
  ''Individual / Sole Trade''
  WHEN r.RenterType = 2 THEN
  ''Individual / Sole Trade''
  ELSE
  ''N/A''
END) as [RenterTypeName],
r.isHighRiskUser [IshighRiskRenter],
(CASE   
  WHEN r.isHighRiskUser = 1 THEN
  ''Yes''
  ELSE
  ''No''
END) as [HighRiskUsrTxt],
r.[HighRiskUsrComment],
c.[FranchiseName],
c.[RACount],
c.[ResCount],
count(r.Id) over() as [TotalCount],
r.[FranchiseId]
FROM AgreementUsers r WITH (NOLOCK)
inner join CTEAgRenters c on r.Id = c.UserId'; 

 IF @firstName <> '' 
 SET @Where = N' AND r.FirstName like @firstName + ''%''';

 IF @surName <> '' 
 SET @Where = @Where + N' AND r.Surname like @surName + ''%''';
 
 IF @companyName <> '' 
 SET @Where = @Where + N' AND r.CompanyName like @companyName + ''%''';

 IF @email <> '' 
 SET @Where = @Where + N' AND r.[Email] = @email ';

 IF @phone <> '' 
 SET @Where = @Where + N' AND r.[PhoneNo] = @phone ';

 IF @postcode <> '' 
 SET @Where = @Where + N' AND r.[PostCode] = @postcode ';

 IF @country <> '' 
 SET @Where = @Where + N' AND r.[CountryName] like @country + ''%''';

 IF @county <> '' 
 SET @Where = @Where + N' AND r.[CountyName] like @county + ''%''';

 IF @town <> '' 
 SET @Where = @Where + N' AND r.[TownName] like @town + ''%''';

 IF @note <> '' 
 SET @Where = @Where + N' AND r.HighRiskUsrComment like @note + ''%''';

 IF @renterType <> '' 
 SET @Where = @Where + N' AND r.[RenterType] = @renterType ';

 IF @isHighRiskUser <> '' 
 SET @Where = @Where + N' AND r.[isHighRiskUser] = @isHighRiskUser ';
 
 If(@Where <> '' )
   Set @s = @s + ' Where  1 = 1 ' + @Where;
   
SET @paging = N'
	Order by r.Id desc
 OFFSET @PageSize * ( @PageNumber - 1 ) ROWS
  FETCH NEXT @PageSize ROWS ONLY
 OPTION (RECOMPILE);';

 If(@paging <> '' )
   Set @s = @s + ' ' + @paging;

  Select @s;
  EXEC sys.sp_executesql @s, N'@firstName nvarchar(100),@PageSize INT, @PageNumber INT,@surName nvarchar(250),
  @title nvarchar(50),
  @companyName nvarchar(250),
  @phone nvarchar(50),
  @email nvarchar(250),
  @country nvarchar(250),
  @county nvarchar(250),
  @town nvarchar(250),
  @postcode nvarchar(250),
  @isHighRiskUser BIT,
  @note nvarchar(250),
  @franchiseId INT,
  @renterType INT ',@firstName,@PageSize,@PageNumber,@surName,@title,@companyName,@phone,@email,@country,@county,@town,@postcode,@isHighRiskUser,@note,@franchiseId,@renterType;

Please check my plan now.
https://www.brentozar.com/pastetheplan/?id=SyqCBjjHo

thank you