Drop Table AR0float
Our environment - Application Server: MS Windows 2003 SP 1 ARS 7.0.01, patch 001 ITSM 6 CMDB 1.1, patch 4 Database Server: MS Windows 2003 SP 2 MS SQL 2005 Our Remedy service suddenly began to run very sluggishly with users reporting long wait times to bring up or to update a case, or to login. This issue occurred for a couple of minutes (5 at most) before it cleared and users were able to access the system normally. A check of the SQL logs at about the time of the slowness revealed the following: SQL TID: 000620 RPC ID: 0001033776 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1530 */DROP TABLE AR0int620 SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1530 */OK SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1530 */UPDATE T28 SET C18255=NULL,C179='AG0019B9BBD70CSCWLTg9ArbDQr#cQ',C18254=5,C5='Remedy Application Service',C6=1317741937 WHERE C1 = '0508322' SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1530 */OK SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1530 */DELETE FROM T25 WHERE C1 = '8337604' SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1530 */OK SQL TID: 000604 RPC ID: 0001033777 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1530 */OK SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1530 */DELETE FROM H25 WHERE entryId = '8337604' SQL TID: 000604 RPC ID: 0001033777 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1530 */UPDATE T29 SET C18022 = 'placeholder' WHERE C1 = '3097234' SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1530 */OK SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1530 */COMMIT TRANSACTION SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1680 */OK SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1680 */SELECT T27.C1,T27.C1 FROM T27 WHERE (T27.C18032 = '00018333725') ORDER BY 1 ASC SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1680 */OK SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1680 */BEGIN TRANSACTION SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1680 */OK SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1680 */DELETE FROM T31 WHERE C1 = '00018333725' SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1680 */OK SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1680 */DELETE FROM H31 WHERE entryId = '00018333725' SQL TID: 000620 RPC ID: 0001033776 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1680 */*** ERROR *** Cannot drop the table 'AR0int620', because it does not exist or you do not have permission. (SQL Server 3701) SQL TID: 000620 RPC ID: 0001033776 Queue: Fast Client-RPC: 390620USER: Remedy Application Service/* Tue Oct 04 2011 11:25:37.1680 */DROP TABLE AR0float620 This message repeats with references to drop table AR0float or drop table AR0char or drop table AR0decimal. I've never seen this before and wonder what does this refer to? Joe Kasell Navy Federal
Re: Drop Table AR0float
or you do not have permission. (SQL Server 3701) SQL TID: 000620 RPC ID: 0001033776 Queue: Fast Client-RPC: 390620USER: Remedy Application Service /* Tue Oct 04 2011 11:25:37.1680 */DROP TABLE AR0float620 This message repeats with references to drop table AR0float or drop table AR0char or drop table AR0decimal. I've never seen this before and wonder what does this refer to? Joe Kasell Navy Federal Credit Union ___ 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
Re: Drop Table AR0float
I would check all the searches that happen through workflow (set field if and push field if conditions) and otherwise (table field qualifications on tables that load on window open when the home page is opened). I’ll lay a bet some of the searches on these are using fields that are not indexed in the where clause.. This may have happened with the customizations done to the product as I think the out of the box version of the apps have refined their searches for good performance on ITSM 6.. I have seen this problem with the out of the box ITSP product that had a number of searches that were not using indexed fields in the where clause... It worked great for a while until their record counts in the customer database started increasing.. Another problem that could be a possible problem is if the searches are on join forms that have millions of records in them.. Remedy join form view descriptions in some cases is not the optimal, from the performance standpoint. This is because union joins are not supported. If you can rewrite these join form views as union joins, it will improve searches on that. Mind you, this is not recommended by BMC Support. So if the join form underlying view definition is one of the problem, understand that trying to fix it is not a recommended BMC solution. You would need to do it at your own risk. The good news is that there is not much of a risk trying to do this. The only risk is that every time the join form view definition changes because of changes in the underlying primary and secondary table definition, your union view you create will be deleted, and replaced by the AR System generated view. So you would need to save a copy of the script/sql that generates that customized view to regenerate it on demand. Also it would be a good idea to save a copy of the original view, to replace the customized with the original at the time of patching the application – just in case – although technically this is not necessary.. The advantage of using a union view is that when you are talking about millions of records in either or both the primary and secondary form, a query to a union view would fetch the results probably 10 or 20 or more times faster than a query to the view that is generated OTB by an AR System join. Joe From: Rick Cook Sent: Tuesday, October 04, 2011 1:04 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Drop Table AR0float ** I could comment on the drop table issue, but I think Misi or someone will probably be able to do so more intelligently than I, and I don't think that is causing your problem anyway. The thing that jumped out at me was your Entry IDs, which were in seven or eight non-zero digits (3 million+/18 million+). How many records do you still have in those tables? At high row counts, deleting a record, as was tracked in the /DELETE FROM H25 WHERE entryId = '8337604' or DELETE FROM H31 WHERE entryId = '00018333725' statements, is something that causes the indexes on those forms to be re-shuffled. That can take some time, especially if you have very high row counts. Rick On Tue, Oct 4, 2011 at 9:55 AM, Joseph Kasell joseph_kas...@navyfederal.org wrote: Our environment - Application Server: MS Windows 2003 SP 1 ARS 7.0.01, patch 001 ITSM 6 CMDB 1.1, patch 4 Database Server: MS Windows 2003 SP 2 MS SQL 2005 Our Remedy service suddenly began to run very sluggishly with users reporting long wait times to bring up or to update a case, or to login. This issue occurred for a couple of minutes (5 at most) before it cleared and users were able to access the system normally. A check of the SQL logs at about the time of the slowness revealed the following: SQL TID: 000620 RPC ID: 0001033776 Queue: Fast Client-RPC: 390620USER: Remedy Application Service /* Tue Oct 04 2011 11:25:37.1530 */DROP TABLE AR0int620 SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service /* Tue Oct 04 2011 11:25:37.1530 */OK SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service /* Tue Oct 04 2011 11:25:37.1530 */UPDATE T28 SET C18255=NULL,C179='AG0019B9BBD70CSCWLTg9ArbDQr#cQ',C18254=5,C5='Remedy Application Service',C6=1317741937 WHERE C1 = '0508322' SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service /* Tue Oct 04 2011 11:25:37.1530 */OK SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service /* Tue Oct 04 2011 11:25:37.1530 */DELETE FROM T25 WHERE C1 = '8337604' SQL TID: 002504 RPC ID: 0001033778 Queue: Fast Client-RPC: 390620USER: Remedy Application Service
