Hi Everyone,
I have a query running for an applications which spikes to 100% while running on multiple threads. Could you please help in tuning the below query and avoid cPU spike.
select SubTable.RelationId, SubTable.NickName, SubTable.ProductTypeCode, SubTable.SubProductTypeCode, SubTable.CurrencyCode,
Users.UserId, Users.Name, Users.Lang, Users.UserLoginId, Users.UserLoginIdBase, Users.SendUnsolicitMessages,
Users.SMSCountryCode, Users.SMSNumber, Users.SMSAllowed, Users.SMSActivationStatus, Users.SMSTermsAndConditions, Users.SMSDailyThreshold,
Loyalty.BranchCode, Loyalty.BranchName, Loyalty.BranchCountry
from SubTable WITH(NOLOCK), Users WITH(NOLOCK), Loyalty WITH(NOLOCK) where
SubTable.AccountNumber = @P1 and
SubTable.AccountExternalId = @P2 and
SubTable.SubProductTypeCode = @P3 and
SubTable.CurrencyCode = @P4 and
SubTable.UserId = Users.UserId and
Users.ActivationFlag = 'Y' and
SubTable.BranchIdRl = Loyalty.BranchId
Thanks,
Manowj.
What I have tried:
tried increasing the CPU memory and pagination.