CPU Usage
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<> 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.
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.
>> id<> 59476
This search argument is non-sargable.
http://en.wikipedia.org/wiki/Sargable
HTH
David