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.