Lee, I'm no dba, but if you're still having issues:
When we had bad performance, especially with searches using wildcards that seemed hard to avoid, we ended allocating more memory to Oracle to avoid going to disk. (I don't know if that is relevant to SQL Server.)
In our case, the number of requests in the system had grown to a point where this behavior starting occurring. I suppose another option would have been to archive a portion of the requests.
(That looks like a lot of access groups you're searching there in field 112 - maybe some computed groups? - but maybe that's the way that console works.)
David Durling Enterprise Information Technology Services University of Georgia On 12/22/2010 2:13 AM, Ali A. Musa wrote:
I feel that if you rebuild the indexes the performance will improve. -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of remedy lee Sent: Tuesday, December 21, 2010 7:25 PM To: [email protected] Subject: Overview Console high CPU usage I've noticed that lately our SQL server is hitting 100% cpu usage frequently. I pinned it down to a query that is causing it. It seems to stem from users viewing the Overview Console. We have SRM 2.1, ITSM 7.0.3, SLM 7.1 and ARS 7.1 installed. SQL 2005 with 2 CPU and 4GB ram running in VMware. I can see in SQL Profiler that CPU usage hits 100% when these 2 SELECT statements run SELECT T1479.C1,C1000000000,C7,C1000000164,C301572100,C179,C230000009,C301626500,C1000000182,C1000003230,C3,C1000003297,C1000003298
FROM T1479 WHERE (((T1479.C112 LIKE N'%;''nfu'';%') OR (T1479.C112
LIKE N'%;0;%') OR (T1479.C112 LIKE N'%;-1098;%') OR (T1479.C112 LIKE N'%;-20000;%') OR (T1479.C112 LIKE N'%;803;%') OR (T1479.C112 LIKE N'%; 804;%') OR (T1479.C112 LIKE N'%;20061;%') OR (T1479.C112 LIKE N'%; 20313;%') OR (T1479.C112 LIKE N'%;20316;%') OR (T1479.C112 LIKE N'%; 13006;%') OR (T1479.C112 LIKE N'%;20055;%') OR (T1479.C112 LIKE N'%; 20211;%') OR (T1479.C112 LIKE N'%;20221;%') OR (T1479.C112 LIKE N'%; 20315;%') OR (T1479.C112 LIKE N'%;20000;%') OR (T1479.C112 LIKE N'%; 1000000001;%') OR (T1479.C112 LIKE N'%;1000000044;%') OR (T1479.C112 LIKE N'%;20003;%') OR (T1479.C112 LIKE N'%;20012;%') OR (T1479.C112 LIKE N'%;20007;%') OR (T1479.C112 LIKE N'%;20214;%') OR (T1479.C112 LIKE N'%;20213;%') OR (T1479.C112 LIKE N'%;20302;%') OR (T1479.C112 LIKE N'%;20218;%')) AND (((T1479.C230000009 = N'ASTCIUNAVAILABILITY') AND (T1479.C1000003234 = N'SGP000000000064')) OR ((T1479.C230000009 = N'MAINCHANGE') AND (T1479.C1000000337 = N'SGP000000000064') AND ((T1479.C7 = 1) OR (T1479.C7 = 2) OR (T1479.C7 = 3) OR (T1479.C7 = 4) OR (T1479.C7 = 5) OR (T1479.C7 = 6) OR (T1479.C7 = 7) OR (T1479.C7 = 8) OR (T1479.C7 = 9))) OR ((T1479.C230000009 = N'MAINHELPDESK') AND (T1479.C1000003234 = N'SGP000000000064') AND ((T1479.C7 = 0) OR (T1479.C7 = 1) OR (T1479.C7 = 2) OR (T1479.C7 = 3))) OR ((T1479.C230000009 = N'ASTPUREQUISITION') AND (T1479.C1000003234 = N'SGP000000000064')) OR ((T1479.C230000009 = N'TMSTASK') AND (T1479.C1000003234 = N'SGP000000000064') AND ((T1479.C7 = 1000) OR (T1479.C7 = 2000) OR (T1479.C7 = 3000) OR (T1479.C7 = 4000) OR (T1479.C7 = 5000))) OR ((T1479.C230000009 = N'MAINWORKORDER') AND ((T1479.C1000000079 = N'SGP000000000064') OR (T1479.C1000003234 = N'SGP000000000064')) AND ((T1479.C1000000079 = N'SGP000000000064') OR (T1479.C1000003234 = N'SGP000000000064')) AND ((T1479.C7 = 0) OR (T1479.C7 = 1) OR (T1479.C7 = 2) OR (T1479.C7 = 3) OR (T1479.C7 = 4))) OR ((T1479.C230000009 = N'MAINKNOWLEDGEDATABASE') AND (T1479.C1000003234 = N'SGP000000000064') AND (T1479.C7 = 4)) OR ((T1479.C230000009 = N'MAINKNOWNERROR') AND (T1479.C1000003234 = N'SGP000000000064') AND ((T1479.C7 = 0) OR (T1479.C7 = 1) OR (T1479.C7 = 2))) OR ((T1479.C230000009 = N'MAINPROBLEM') AND (T1479.C1000003234 = N'SGP000000000064') AND ((T1479.C7 = 0) OR (T1479.C7 = 1) OR (T1479.C7 = 2) OR (T1479.C7 = 3) OR (T1479.C7 = 4) OR (T1479.C7 = 5))))) ORDER BY 9 ASC, 1 ASC AND SELECT T943.C1,C1000000000,C7,C1000000164,C179,C230000009,C301626500,C1000000161,C1000000218,C3,C1000000019,C1000000018
FROM T943 WHERE (((T943.C112 LIKE N'%;''nfu'';%') OR (T943.C112 LIKE
N'%;0;%') OR (T943.C112 LIKE N'%;-1098;%') OR (T943.C112 LIKE N'%;-20000;%') OR (T943.C112 LIKE N'%;803;%') OR (T943.C112 LIKE N'%; 804;%') OR (T943.C112 LIKE N'%;20061;%') OR (T943.C112 LIKE N'%; 20313;%') OR (T943.C112 LIKE N'%;20316;%') OR (T943.C112 LIKE N'%; 13006;%') OR (T943.C112 LIKE N'%;20055;%') OR (T943.C112 LIKE N'%; 20211;%') OR (T943.C112 LIKE N'%;20315;%') OR (T943.C112 LIKE N'%; 1000000001;%') OR (T943.C112 LIKE N'%;1000000044;%') OR (T943.C112 LIKE N'%;20012;%') OR (T943.C112 LIKE N'%;20032;%') OR (T943.C112 LIKE N'%;20000;%') OR (T943.C112 LIKE N'%;20218;%') OR (T943.C112 LIKE N'%; 20213;%') OR (T943.C112 LIKE N'%;20003;%') OR (T943.C112 LIKE N'%; 20302;%') OR (T943.C112 LIKE N'%;20007;%')) AND (((T943.C230000009 = N'ASTCIUNAVAILABILITY') AND (T943.C1000000079 = N'SGP000000000064')) OR ((T943.C230000009 = N'MAINCHANGE') AND (T943.C1000000427 = N'SGP000000000064') AND ((T943.C7 = 1) OR (T943.C7 = 2) OR (T943.C7 = 3) OR (T943.C7 = 4) OR (T943.C7 = 5) OR (T943.C7 = 6) OR (T943.C7 = 7) OR (T943.C7 = 8) OR (T943.C7 = 9))) OR ((T943.C230000009 = N'MAINHELPDESK') AND (T943.C1000000079 = N'SGP000000000064') AND ((T943.C7 = 0) OR (T943.C7 = 1) OR (T943.C7 = 2) OR (T943.C7 = 3))) OR ((T943.C230000009 = N'ASTPUREQUISITION') AND (T943.C1000000079 = N'SGP000000000064')) OR ((T943.C230000009 = N'TMSTASK') AND (T943.C1000000079 = N'SGP000000000064') AND ((T943.C7 = 1000) OR (T943.C7 = 2000) OR (T943.C7 = 3000) OR (T943.C7 = 4000) OR (T943.C7 = 5000))) OR ((T943.C230000009 = N'MAINWORKORDER') AND ((T943.C1000000427 = N'SGP000000000064') OR (T943.C1000000079 = N'SGP000000000064')) AND ((T943.C1000000427 = N'SGP000000000064') OR (T943.C1000000079 = N'SGP000000000064')) AND ((T943.C7 = 0) OR (T943.C7 = 1) OR (T943.C7 = 2) OR (T943.C7 = 3) OR (T943.C7 = 4))) OR ((T943.C230000009 = N'MAINKNOWLEDGEDATABASE') AND (T943.C1000000079 = N'SGP000000000064') AND (T943.C7 = 4)) OR ((T943.C230000009 = N'MAINKNOWNERROR') AND (T943.C1000000079 = N'SGP000000000064') AND ((T943.C7 = 0) OR (T943.C7 = 1) OR (T943.C7 = 2))) OR ((T943.C230000009 = N'MAINPROBLEM') AND (T943.C1000000079 = N'SGP000000000064') AND ((T943.C7 = 0) OR (T943.C7 = 1) OR (T943.C7 = 2) OR (T943.C7 = 3) OR (T943.C7 = 4) OR (T943.C7 = 5))))) ORDER BY 8 ASC, 1 ASC The duration in profiler hits 10-15 seconds and the CPU is 10,000 to 20,000 It matches exactly when the CPU hits 100%. Now are these SELECT statements bad? Is the query causing the slowdown or is the that Overview uses the CAI plugin? The problem is that our support staff use Overview often to track their Incidents and Work orders. I can't find any knowledge base tips or any other people with this issue. Any insight or tips on how to optimize or fix this issue? It's causing the overall Remedy performance to be sluggish.
_______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
