Together all the SQLs takes 5.8 sec and are mostly due to following API calls.
API Form Time ARGetListEntryWithFields HPD:Help Desk 1453 ARGetListEntryWithFields CHG:Infrastructure Change 1250 ARGetListEntryWithFields HPD:Help Desk 1235 ARGetListEntryWithFields TMS:Task 1156 ARGetListEntryWithFields TMS:Task 672 ARGetListEntryWithFields WOI:WorkOrder 31 ARGetListEntryWithFields WOI:WorkOrder 16
I don't know why the Get List Entry was executed twice for all the forms except Change form. May be this is how the vendor form works. The way I would proceed is consider where condition of one of form, lets say, Help Desk, in which C1000000716,C230000009,C4,C7 fields are used EVERY time. so probability having composite index on these fields make sense. OR you may try executing this SQL manually from SQL console, keep removing one of these fields at a time from where clause and check which is that column causing trouble.
Also, returning 300 records indicate the search was wide, try with narrow search result if it reduces the time? What was value of 'Show' field ?
I had the same issue while back even though there was proper index. I was informed perform archiving.
Hope this would help.
@ anandsagar sah - Many thanks for response Man.
I was also planning to Suggest the client- Lets Start with Archiving of Data.
From Console If we select Assigned to My Self or Group in Both Cases it takes avg 5-7 Seconds and Maximum is takes by Help Desk form (1-2.5 Sec).
returning 300 records indicate the search was wide, try with narrow search result if it reduces the time? What was value of 'Show' field ?--> Dev Env and Assigned to Group.
Upon Directly Firing Query i am still waiting for access to DB (So Many Security Points), Once I have access i will fire these Queries.
Please keep posted with more Ideas if it comes in Mind. (All Experts )
BMC has an overview console fix available for performance issue on 7.6.04.
Also a plugin comes into the picture called ARSYS. ARDBC. APPQUERY.
Try increasing the threads for plugin and also increase plugin loop back
But lookin at time taken I feel its not that slow provided there is not
much data in database.
I posted above Analysis for Dev Box which has less Load. When i did Analysis for Prod Server and Its taking like 20+ Seconds where Major Chunk is Going Querying HpD:Help Desk form.
Plugin-ARDBC-Threads: 8 24
These settings are already in Place , Just Checked.
More Inputs Please--
I will keep you posted- Help desk form seems to be area of more Concern( On Live Its not in Good Shape)
Has this been recently observed or it's occurring since long?
Is it occurring for all users or specific users?
Have you checked for application preference?
What is the setting for server side table chunk size n max entries per
Check with the fields that are in where clause if those are already indexed
Also check with storage space and input output operations are occurring
frequently which are causing delay.
Also has all database related settings already applied like snapshot
isolation, is read committed, max degree of parallelism.
Check with bmc support for overview console fix.
On 31-Aug-2015 11:09 PM, "Rahul Priyadarshy" <email@example.com>
Sounds to me like at the very least, inefficient SQL queries are being performed. At worst, you may be performing table scans. I think it would be helpful to see the same log for the Production environment. I don't think there's enough info in the Dev log to conclusively say which query is causing the performance issues in Prod. From there, we can start investigating why individual queries are taking too long. We'll likely need to add an index to resolve the issue.
@ Evan Ruble - Same i did on Live and Its 4 time SLow than Live Server. I am waiitng for DB Access so that I can fire query on DB and see. I see thru Logs of Live Server all Top SQL are coming from Hpd:Help Desk form. I will test these queries once i have DB Access on All 3 env.
Response is below
Has this been recently observed or it's occurring since long?-- Since Long I joined the assignment recently.
Is it occurring for all users or specific users?-- Almost all
Have you checked for application preference?-- I Checked for my self and seems its fine there.
What is the setting for server side table chunk size n max entries per query setting?---> 100 and 2000
Check with the fields that are in where clause if those are already indexed at database?--> From Form Property i can see its indexed. I am waiting for DB access for More Play
Also check with storage space and input output operations are occurring frequently which are causing delay--> Storage Seems fine but I am not sure about I/O Going on at Storage Level.
Also has all database related settings already applied like snapshot isolation, is read committed, max degree of parallelism.---> I hope but can not confirm at this point.
BMC Support Ticket we will raise -
Try setting chunk size to 1000 so there will be less read operations and
data will be readily available in cache.
I recommend to check with storage level io operations as I have recently
observed that too many operation at storage level has lead to big
performance issue. Open few more ports at storage level.
On 31-Aug-2015 11:42 PM, "Rahul Priyadarshy" <firstname.lastname@example.org>
What are T2070, T1920, and T1277? In the Dev log, all of the time was spent on those three forms (except .0470 seconds). Based on the queries, they appear to be joins/unions.
That's curious. I don't have a 7.6.04 environment anymore, so I don't have a perfect environment to compare to, but I thought the query should have been run against SHR:Union_OverviewConsole. Based on what you've provided, it looks like the query is being run against each of the base forms, rather than the join/union.
I tested the query against HPD:Help Desk in my environment, and was able to replicate the problem (on Oracle rather than SQL). It took some 7-8 seconds to complete the query. The execution plan is hideous, which makes me think even more that it's being run against the wrong form. Is this environment customized?
Is this environment customized?--> I dont think its Customized to run against base form. but i will look as i am pretty new to this environment (2-3 days only).
I do not see the name which listed in 7.6.04 env here is the screenshot. I see few as Overlay, i will explore more.
My memory says it's something related to APPQUERY PLUGIN in 7.604 version and to resolve this we did some server configuration changes. Issue occurs mainly to the users who are part of many number of support groups. (You can test and confirm the behaviour with user who is part of single support group and user having more than 5 support group. Capture SQL+plugin Logs for this test and post here.
Archive is always a best option to improve the performance but I know it will take some time to implement the archival process if it is already not there. Till that time you can suggest user to select 'show' from my selected group option on the console. I know it will help you!
#tips & #tricks
The backend form of main table of overview console was replace from SHR:ARDBC_OverviewConsole to SHR:Union_OverviewConsole in 8.1.
When my environment was upgraded from 7604 to 81, I noticed this change and because of which we face performance issue, therefore I think the vendor form (SHR:ARDBC_OverviewConsole) is faster than view form (SHR:Union_OverviewConsole), at least as per my environment.
@All, I am not expert in plugin thread configuration, but if i observe from log is, over 90% of time is consumed by SQLs, and less then 10% by plugin or other area. By thing logic, I think it is more of tuning with Database, SQL where clause and indexes and less with plugin.
Rahul, may be you can confirm once you are able to run the same query manually.