Click here to Skip to main content
14,732,778 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
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


What I have tried:

tried increasing the CPU memory and pagination.
Updated 5-Jan-17 2:57am

Use proper table joins to select from multiple (google "sql select join table" and you'll find examples). If the cpu spike is just that, a spike that quickly dies then it's nothing to worry about. Beyond the joins there is nothing in your query that can be optimised, you'd have to optimise the table for fast selecting by adding indexes.
I had the same issue. Basically we need to check for those columns/fields who participate in this query. Think you need to run query execution plan and check

Here are some useful links which I had used and sorted the issue

Execution Plan Basics - Simple Talk[^]

Displaying Graphical Execution Plans (SQL Server Management Studio)[^]

Query Execution Plans[^]

Understanding a SQL Server Query Execution Plan -[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900