CA Service Management

 View Only
  • 1.  MDB optimization for DBAs?

    Posted Jul 01, 2014 11:56 AM

    Hi Experts,

    I am looking for best practices regarding MDB caring and feeding.

    Background: One of my customers (with SDM, Catalog, UAPM, ITCM,...) has issues with DB blockings. We found out that most frequently used tables and their indexes are highly (>99%) fragmented.

    The customer asked me for guidance from a product perspective, which tables their DBAs need to care for, what tables should be put in a different DB-file to balance I/O and so on.

     

    Do we have a set of recommendations for our customers' DBAs?

     

    Any hint is welcome

     

    Schöne Grüße,

    Tom



  • 2.  Re: MDB optimization for DBAs?

    Posted Jul 01, 2014 02:22 PM

    Hi Tom,

    This is a great subject!  In fact there is a DB Tuning section outlined in the Service Desk 12.5 Green Book that may help you or at least give you some ideas to start with when looking to optimize the MDB. 

     

    You can find the green book here:

    https://support.ca.com/phpdocs/7/common/greenbooks/CA_SDM_r125_Green_Book_ENU.pdf

     

    See chapter 12  - there is a section on general tuning guidelines which talks about Service Desk performance and includes some information on tuning the database as well.

     

    Hope that helps to get you started.  Other folks may be able to respond with more detailed things that they do preventatively on the database side of things.

     

    Thanks,

    Jon Israel

    Principal Support Engineer

    CA Technologies



  • 3.  Re: MDB optimization for DBAs?

    Posted Jul 01, 2014 11:17 PM

    Hi Tom,

     

    In addition to what Jon had mentioned, chapter 12 has detailed information about the archive purge. Along with performing archive purge, it is recommended to perform dbshrink with the help of your DBA which helps to reduce the size of the MDB files. You may refer below thread for your reference:

     

    https://communities.ca.com/thread/113611484

     

    Thanks,

    Naveen



  • 4.  Re: MDB optimization for DBAs?

    Posted Jul 02, 2014 04:55 AM

    Hi Friends,

    thank you for your input!

    I already contacted customers DBA-team to figure out, what they do to keep the MDB healthy.

    A shrink of their MDB will take the whole system offline for hours - which they cannot affort. Thats the reason, why they asked me if we have a list of DB-Objects (Index, view, table,...) they need to care for.

    Since there is way more than only SDM the archive and purge facilities of SDM are not sufficient. Especially Asset Managment (UAPM 11.3.4) and Catalog (12.7) are lacking archive and purge functionality.

    But archiving and purging is only one side of the story. We need to keep the MDB healty from an SQL-Server perspective-too.

     

    My plan is analyzing DB objects regarding space consumption and usage frequency. Then I can compare the list with my knowledge of the MDB schema. After that I can provide a list of low hanging fruit for the DBA-Team.

     

    Do we plan for a consolidated archive and purge approach for the whole MDB in the future?

     

    Cordially,

    Tom



  • 5.  Re: MDB optimization for DBAs?

    Posted Jul 02, 2014 06:11 AM

    Tom,

     

    If the dbshrink does not work for you, then you may refer below tech document to see if it  helps:

     

    https://comm.support.ca.com/?legacyid=TEC580653

     

    Thanks,

    Naveen



  • 6.  Re: MDB optimization for DBAs?

    Broadcom Employee
    Posted Jul 02, 2014 08:56 PM

    Hi Tom,

    These are my 2 cents.....

    You may also keep a check on a table : not_log , this will consume lot of space, as all the notification that are being fired by SDM will be recorded in it. You may run a simple queries to delete the row of inactive  tickets Ex: Request.

    Sample Query :

    DELETE  from not_log

      WHERE cntxt_obj in (Select persid from call_req where (persid in (select

      cntxt_obj from not_log where cntxt_obj

      like 'cr%') AND active_flag =0))

     

    You may check with your audit team, if they need these data. and also Seek assistance from your DBA for any direct DB operation.

     

    ~vinod.



  • 7.  Re: MDB optimization for DBAs?

    Posted Nov 27, 2014 05:43 AM

    Hi Vinod,

    we identified not_log for cleaning up, too.

    Instead of deleting inactive tickes' notifications, we decided for cutting out the HTML-part, leaving only pure text. This cleansing task reduced the space consumed by not_log by 90%, while the customer has still access to historic notification data.

     

    Schöne Grüße,

    Tom



  • 8.  Re: MDB optimization for DBAs?

    Broadcom Employee
    Posted Jul 23, 2014 05:50 PM

    Tom, I am afraid we don't have a mdb tool to archive and purge data. When i read this discussion, one thing came

    in my mind is, the DBMS dba should have some tool to monitor and collect analystic data to determine which

    tables are used often and what kind of data fragmentation they have and base on that DBA can do some turning

    operations from DBMS point of view. Considering the same mdb is used by many products it may not be practical

    to do this product by product by rather using some dbms tool to do it as a whole. 2 cents comment. Thanks _Chi



  • 9.  Re: MDB optimization for DBAs?

    Broadcom Employee
    Posted Jul 10, 2014 10:09 PM

    Hi Tom,

    Regarding SDM, the long running SQL will be written in the stdlog.


    for example:

    05/13 01:09:25.89 ******   sql_agent             596 SIGNIFICANT  sqlclass.c            1043 The following statement took 2360 milliseconds: Clause (SELECT ca_resource_class.name , ca_resource_class.family_id , ca_resource_class.id FROM ca_resource_class) Input (<None>)

     

    You can find them in stdlog with keywords: took SELECT

    And you can also count the frequecy of each long running SQL

     

    I would suggest you to ask your DBA to work on the most frequent SQL first, and then the SQL took longer time.



  • 10.  Re: MDB optimization for DBAs?

    Broadcom Employee
    Posted Nov 27, 2014 05:54 AM

    Hi All,

     

    Please have a look at USRD problem 2494.

     

    Title: DATABASE SIZE GROWING DUE TO NOTIFICATIONS

     

    If Service Desk is configured to process and send out email notifications

    to specific contacts defined in the system, when a notification is sent out,

    it will update the 'nlh_msg_html' column of the 'not_log' table and may

    cause size of the database to grow fast.

     

    The correction to this problem introduces a new NX_INSERT_NLH_MSG_HTML

    variable that can be tweaked to avoid the problem. If this variable is set

    to 'YES' then the html message body of the notification can be inserted

    into not_log table. If it is not set, html part will not be inserted into the not_log table.

     

    Thanks & Regards,

    Hema.



  • 11.  Re: MDB optimization for DBAs?

    Posted Nov 27, 2014 07:19 AM

    This post might be helpful

     

    SQL Database Maintenance on MDB