MDB optimization for DBAs?

[TomStarke]

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

[Jon_Israel]

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

[Anon Anon]

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

[TomStarke]

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

[Anon Anon]

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

[Vinod Subburaj]

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.

[TomStarke]

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

[Chi Chen]

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

[Zhijun He]

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.

[Hemalatha Simhadri]

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.

[Legacy User]

This post might be helpful

 

SQL Database Maintenance on MDB