We had a similar issue and were provided the following by BMC: The Engineer assisting us with this issue has reviewed the information provided and agrees the query we see taking to complete are running row level access. He would like you to test disabling the new RLS implementation and use the old implementation to see if the issue persists or not.
1. Please open the Centralized Configuration form in the com.bmc.arsys.server.shared section 2. Add the following parameter: Disable-New-RLS-Implementation with a value of true Disable-New-RLS-Implementation: T 3. Restart the servers in the group This change will use a LIKE clause to allow the database to search the columns directly. Once the change has been made the servers restarted, please enable API, SQL, and Filter logging and reproduce the issue. If the performance impact is seen searching the fields that have drop-down menus for non admin users after the change has been made, please run the log zipper to gather and send the log files and forward the zip file along with the name of the user who performed the search. Fixed our issue…worth a try. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of LJ LongWing Sent: Friday, November 18, 2016 1:03 PM To: arslist@ARSLIST.ORG Subject: Re: Performance Issues - Multitenancy ** Brian, Turn on SQL Logging and perform the same search between the two different users and compare the SQL, maybe even provide the sql here for analysis....the 'slow vs fast' queries should be fairly obvious what's causing the difference. On Fri, Nov 18, 2016 at 11:24 AM, Brian Pancia <panc...@finityit.com<mailto:panc...@finityit.com>> wrote: ** We are running into some serious performance issues with multitenancy. We're on BMC ITSM 9.1 SP1 and SQL Server 2012. If I do a search on a form like CTM:People with an account that has unrestricted access the search comes back in about 2-3 seconds for 130000 records. That same search with a user that is restricted to a certain company will come back in 70 seconds, which is a significant difference. That is the first issue. The second issue is that the database server CPU utilization will spike to 100% during the searches. During the unrestricted user test not a big deal because it is only a couple seconds and no one notices the spike. However, for the other user it brings the system to a halt for 70 seconds. If the user kills their session prior to the search complete the search will hang in the database and consume 100% of the CPU indefinitely. Any recommendations would be appreciated. We have done all the BMC recommended performance tuning on the systems. Thanks, Brian DISCLAIMER: The information contained in this e-mail and its attachments contain confidential information belonging to the sender, which is legally privileged. The information is intended only for the use of the recipient(s) named above. If you are not the intended recipient, you are notified that any disclosure, copying, distribution or action in reliance upon the contents of the information transmitted is strictly prohibited. If you have received this information in error, please delete it immediately. _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"