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" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
