CPU Usage

Posted on 2014-08-12
Medium Priority
Last Modified: 2014-08-22
I have a website that has SQL Server Db as back end. I found that the CPU usage was more than normal. I found a SQL code to show most CPU consuming query I fixed some of them and it return noticeable amount of CPU Usage.

The only SQL code that I dont know how to get it optimized to not use too much CPU redources is this one:

SELECT top 5 [ID], [Title], [Details],[IPhoto] FROM [qryWords] WHERE lang=1 and id<> 59476  and [Title] like N'%no%' order by id desc

Note: qryWord is a view and the SQL code is as following:

SELECT        word, ID, Title, Details, Visible, Lang, IPhoto
FROM            dbo.tblBabeteLekchwekan
WHERE        (Visible = 1) AND (Lang = 1)

Do you have any Idea how can I tune this query to use as low as CPU resources?
Question by:Yadtrt
LVL 75

Accepted Solution

Aneesh Retnakaran earned 2000 total points
ID: 40256915
'%no%' filter condition seems to be the reason.  I know its always possible to avoid those sorts of searches, is highly recommended. Your option to speed up this operation will be the full text indexing.
also you don't really need lang=1 in your where condition because its already on the view .
LVL 35

Expert Comment

by:David Todd
ID: 40257128

>>  id<> 59476  
This search argument is non-sargable.

LVL 49

Expert Comment

ID: 40257383
<> is sargable, but not likely to improve performance

so the most likely performance culprit is: [Title] like N'%no%'
which is probably causing a table scan

by the way. top 5 isn't helping either because it adds the need to order, and with '%no%' probably matching many more than 5 results all records are scanned to evaluate the filter then all matches sorted by id then just 5 of those returned.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question