CA Service Management

Expand all | Collapse all

Delay Server Response during Scoreboard Loading

  • 1.  Delay Server Response during Scoreboard Loading

    Posted Jan 15, 2016 01:09 AM

    Hi Team,

     

    Whenever I log in to the Service Desk 14.1 console for the first time everyday, my scoreboard do not load properly and it gives delay server response time msg for a long time. When I switch between the tabs to load it again, It gives the message as displayed in the following screenshot:

     

    Scoreboard.png

     

    After log out and log in again, it loads properly and does not give delay server response either.

     

    When I check my logs, I see the following log:

    A FETCH for the following statement took 102219 milliseconds: SELECT call_req.open_date, call_req.id, call_req.ref_num, call_req.id FROM call_req WHERE ( call_req.assignee IS NOT NULL OR call_req.group_id IS NOT NULL ) AND ( call_req.type = N'R' OR call_req.type = N'' OR call_req.type IS NULL ) AND call_req.active_flag = 1 ORDER BY call_req.open_date DESC NULLS LAST

     

     

    What can I do to load my scoreboard properly everytime?

     

    Regards,

    Balram Singh Deswal



  • 2.  Re: Delay Server Response during Scoreboard Loading

    Broadcom Employee
    Posted Jan 15, 2016 01:20 AM

    Hi Balram,

     

    Could you please check if this knowledge document helps you.

    http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec1200339.aspx

     

     

     

     

     

     

     


    Are you able to replicate the same kind of of behavior on your test environment which is replica of production.

     

    With Regards,Vinod.

     



  • 3.  Re: Delay Server Response during Scoreboard Loading

    Posted Jan 15, 2016 01:58 AM

    Hi Vinod,

     

    Thanks for your response!

     

    I have performed the steps mentioned in the document earlier to set the delay response time to 60 seconds. But the problem still persists.

     

    This behavior is only occurring in the test environment. In Production, it is working fine.

     

    When I work on the servers provided for application and database, they tend to loose connections in every 10-15 minutes (Try to reconnect). Even the ping time from application server to database server is sometimes 2ms/3ms/10ms as shown in the following screenshot:

    Ping Statistics.png

     

    Could this be the reason, or there is issue from the application/database side?

     

    Regards,

    Balram



  • 4.  Re: Delay Server Response during Scoreboard Loading

    Posted Jan 15, 2016 04:26 AM

    Hi Vinod,

     

    Kindly suggest on my reply. It is urgent, as it would be wastage of effort if the problem lies within the server itself.

     

    Thanks.



  • 5.  Re: Delay Server Response during Scoreboard Loading
    Best Answer

    Broadcom Employee
    Posted Jan 15, 2016 10:06 AM

    Hi Balram,

     

    I do not believe the issue is with network. This is more on the SQL Server side.

     

    Is your SQL Server host virtualized too?  Is it sharing disk/memory with other hosts on the same VMware server? 

     

    Below statement is a clear indication that it took SQL Server 102 seconds to complete this SQL statement. 

    A FETCH for the following statement took 102219 milliseconds: SELECT call_req.open_date, call_req.id, call_req.ref_num, call_req.id FROM call_req WHERE ( call_req.assignee IS NOT NULL OR call_req.group_id IS NOT NULL ) AND ( call_req.type = N'R' OR call_req.type = N'' OR call_req.type IS NULL ) AND call_req.active_flag = 1 ORDER BY call_req.open_date DESC NULLS LAST

     

    Now, SDM would need to use that result, process it on domsrvr, web engine and present it to the browser.

     

    That's why you are seeing delayed response message.

     

    I've seen such behavior happen in our shared virtual labs, where in my host was totally swapped out from "real memory" of the virtual server  because I was not using my machine over night but someone else was using "their machine" which needed more resources from the virtual server.  So, when its time for me to use my box, it takes several seconds for the virtual server to give me enough resources.  And so the delay on the very first time I use my SDM/SQLServer on a given morning.  I would not see such slowness there after anymore, because the virtual server would have given me the needed.

     

    Checkout this doc: http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec580653.aspx    that explains a bit more on how to identify such issues and troubleshoot them further.

     

    _R

     

     



  • 6.  Re: Delay Server Response during Scoreboard Loading

    Posted Jan 15, 2016 10:36 AM

    Hi Raghu,

     

    I think you have pointed out the correct reason behind this delay server issue. My database server (Oracle 11g) is on virtual server as well as it has got a shared drive too which is used by the other machines on the server to transfer the data from one server to another. Lack of resources every morning is the prime reason for the query to take that much time. I have even cleared the cache of the browser to test the delay server response, but it only give me during the morning first log in.

     

    Thanks a lot for your valuable suggestion!

     

    Regards,

    Balram Singh Deswal



  • 7.  Re: Delay Server Response during Scoreboard Loading

    Posted Jan 16, 2016 12:46 AM

    Hi Raghu,

     

    The query that is generating the delay response time is returning 108415 rows. When I run this query in Oracle (after set timing on), it shows 100 seconds approx on both application as well as database server.

     

    What can be the root cause for the delay server response that I am facing? Is it My database server on virtual server with shared drive or my query that is executing during loading of scoreboard?

     

    Kindly suggest.

     

    Thanks & Regards,

    Balram



  • 8.  Re: Delay Server Response during Scoreboard Loading

    Broadcom Employee
    Posted Jan 20, 2016 05:54 PM

    Hi Balram,

     

    I'd engage the DBA and have him look into this:

     

    Via SQLplus/SQL client connect to the MDB database / mdbadmin schema.

     

    # below alter sessions are used by Service Desk for Oracle:

     

    alter session set NLS_COMP=LINGUISTIC;

    alter session set NLS_SORT=BINARY_CI;

    set autotrace ON;

     

    SELECT call_req.open_date, call_req.id, call_req.ref_num, call_req.id FROM call_req WHERE ( call_req.assignee IS NOT NULL OR call_req.group_id IS NOT NULL ) AND ( call_req.type = N'R' OR call_req.type = N'' OR call_req.type IS NULL ) AND call_req.active_flag = 1 ORDER BY call_req.open_date DESC NULLS LAST;

     

     

    Now it would print out the execution plan.  If you see any Full Table scans (example: TABLE ACCESS FULL)  then that's probably what is causing the slowness.

     

    Adding an appropriate Case-Insenstive Index for the specific join that's showing up as Full table scan, would normally help in such a case.

     

    _R



  • 9.  Re: Delay Server Response during Scoreboard Loading

    Posted Jan 21, 2016 01:15 AM

    Ok Raghu,

     

    I am going to take the help of DBA to diagnose the root cause of the slowness.

     

    Thanks & Regards,

    Balram Singh Deswal



  • 10.  Re: Delay Server Response during Scoreboard Loading

    Posted Jan 15, 2016 01:51 AM

    Hi,

    this is not what you actually looking for, but:

    • seems that stored query returns a lot of data and that's the reason why the form is loading so slow, have you tried to hide it to any subfolder from initial scoreboard tree?
    • also I recommend to trim long queries to reduce database load, via timespans for example (open_date > StartAtTime('PAST_YEAR'));

    Regards,

    cdtj



  • 11.  Re: Delay Server Response during Scoreboard Loading

    Posted Jan 15, 2016 02:07 AM

    Hi cdtj,

     

    Actually this behavior only shows during first time log in everyday. Same queries are working fine in production environment (CA SDM 12.6). Kindly check my reply to Vinod and provide your feedback on the same.

     

    Thanks & Regards,

    Balram



  • 12.  Re: Delay Server Response during Scoreboard Loading

    Posted Jan 15, 2016 06:17 AM

    i'm not sure but:

    - first query could cache information and followed queries uses cache;

    - could you try to implement my suggestions as test, this takes 5 minutes;

    - difference between test and prod env's could be notable due to servers configuration;



  • 13.  Re: Delay Server Response during Scoreboard Loading

    Posted Jan 15, 2016 07:38 AM

    Ya server configuration is completely different in case of production.

     

    Kindly tell how do I configure the following thing

    • seems that stored query returns a lot of data and that's the reason why the form is loading so slow, have you tried to hide it to any subfolder from initial scoreboard tree?

     

    I can configure only servicedesk user scoreboard. There are lot of users with their own scoreboard that are facing this issue.



  • 14.  Re: Delay Server Response during Scoreboard Loading

    Posted Jan 17, 2016 09:40 AM

    Hello Balram,

    as I understood your situation, query that produces lack returns more that 100 000 rows of data.

     

    OOTB Service Desk interface have limitiation to display only first 5000 rows of data,

    so is there real need to make so heavy query and display it on scoreboard?

    I don't know your sphere of activity but I also think that having more than 100 000 active requestion makes appreciable system load,

    is autoclose configured in your environment?

     

    I can't take my proposals as best practice, but I always recommend to my customers to build stored queries that didn't return more that 3000 rows of data.

    You can add timespan option to your query, for example:

    from:     active = 1 AND (type = \'R\' OR type= \'I\' OR type is NULL)

    to:

         active = 1 AND (type = \'R\' OR type= \'I\' OR type is NULL) AND open_date > StartAtTime('PAST_YEAR')

    This query will show only tickets that were created within past 365 days.

     

    Regards,

    Timur



  • 15.  Re: Delay Server Response during Scoreboard Loading

    Posted Jan 17, 2016 11:05 PM

    Hi Timur,

     

    Thanks for your response!

     

    Our environment is a replica of production environment. The same query is running fine in production (May be due to high configuration servers allocated). We cannot change the query as client will not accept this on production. For now, he just want to be assured that this issue won't arise during production rollout.

     

    How to check whether autoclose is configured or not?

     

    Regards,

    Balram



  • 16.  Re: Delay Server Response during Scoreboard Loading

    Posted Jan 17, 2016 11:45 PM

    Autoclose means the time, when resolved tickets become inactive (closed),

    this could be configured in Service Desk Admin Tab -> Service Desk -> Application Data -> Codes -> Autoclose,

    autoclose time should depens on your business needs, but as I remember defult autoclose is 30 work days.

     

    You could build a simple query like this:

    last_mod_dt < StartAtTime('PAST_MONTH')

    which will diplay tickets, that haven't any modifications within past month, if most of them are in Resolved status, I'll highly recommend to plan to close them up.

     

    Regards,

    Timur



  • 17.  Re: Delay Server Response during Scoreboard Loading

    Posted Jan 18, 2016 01:29 AM

    Hi Timur,

     

    Auto close is set at 106 hours.

     

    As the database is being restored about 2 months ago, using the query last_mod_dt < StartAtTime('PAST_MONTH') is returning 108341 out of 108415. When I search for closed tickets, it is returning 63 lakh tickets.

     

    I will suggest client to update the scoreboard query as per your suggestion.

     

    Thanks!

     

    If possible, can you provide your suggestion on the following thread:

    Group Auto Fill during Change Order Creation

     

    Regards,

    Balram