Look at the backend forms that the Table field in the Overview Console queries. There are no indexes built on those so I added one to the forms that pull HPD tickets (I forget the table name at the moment). That increased the opening time of the Overview console for our HelpDesk personnel. Indexes for the other modules are still on my Things-to-do list.
-----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of David Durling Sent: Friday, December 24, 2010 2:39 PM To: [email protected] Subject: Re: Overview Console high CPU usage 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,C30162650 0,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,C100000016 1,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" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
