Performance issues related to form SMT:Social_FollowConfig should there be housekeeping done on this form?

Version 1
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Remedy with Smart IT


    COMPONENT:

    Remedy with Smart IT


    APPLIES TO:

    Smart IT 18.05, 18.08



    DETAILS:

    SMT:Social_FollowConfig is a temp table that is created based on a CCS parameter : Server-Temp-Table-Threshold. Recommended value for this is 900. It means if follow config records for current user is more than 900 then only Temp Table is created. Temp Table should contain less number of data if followed Tickets are having huge number of Worknotes. If Worknotes are less follow config records can be kept more in Temp Table.

    Smart IT dashboard performance and DB CPU can be impacted if large amount of data is stored in this table.

    Clean up recommendations for SMT:Social_FollowConfig
     

      
       
    1. The first recommendation would be to validate how many records exist on this form and if the Follow Config records can be removed, specially old records and only keep latest 1000 created records.
    You can use this query to find out here the T table below is Follow Config form. 

    select C1000000159, count(*)
    from T4017
    group by C1000000159
    order by 2 desc


    If removing all these records is not an option, then following queries can be used to remove Duplicate, Invalid and Closed records:    
      Remove Duplicate records:
      
       
    • For Oracle :
      

    delete from  SMT_Social_FollowConfig 
    where C1 in (
    select request_id from (select  C1 AS request_id,C1000000182 AS ticketid,C1000000159 AS followerId,C1000001563 AS TicketGUID,C5 AS Last_Modified_By, C4 AS Assignee_Groups,C6 AS modified_date,row_number() over(partition by C1000000182 , C1000000159 order by C6 desc) as rownumber 
    from SMT_Social_FollowConfig
    order by C1000000159 ) where rownumber > 1 )

      
       
    • For SQL :
      

    With Duplicates_SOCIAL_FC as 
    (select ticketid,followerId,TicketGUID,Last_Modified_By,Assignee_Groups,modified_date,row_number() over(partition by ticketid , followerId order by Modified_Date desc)
    rownumber from SMT_Social_FollowConfig ) delete from Duplicates_SOCIAL_FC where rownumber>1 and rownumber is not NULL

      
     
      Remove Invalid records: 
      
      
      delete from SMT_Social_FollowConfig  where C1000000159 = 'Remedy Application Service'  
    delete from SMT_Social_FollowConfig  where C1000000159 = 'AR_ESCALATOR'  
      
       
      Remove Closed records (Closed tickets): 
      
      
       
    • How many tickets are in Closed status:
      
      select count(*) from T4017 where C1000000159 = 'na230289' and C1000000182 in (select C1000000161 from T1447 where 7 >5) → this is for workorder form.  
      
      
       
    • Delete closed Change Requests:
      
      DELETE FROM Table1 WHERE C1000000182 IN (SELECT chg.C1000000182 from Table2 chg join Table1 social on chg.C1000000182 = social.C1000000182 WHERE chg.C7=11);   
    where Table1 = Schema ID for SMT:Social_FollowConfig  
    Table2 = Schema ID for CHG:Infrastructure Change  
      
      
       
    • Delete closed or bypassed Tasks:
      
      DELETE FROM Table1 WHERE C1000000182 IN (Select task.C179 from Table2 task join Table1 social on task.C179 = social.C1000001563 WHERE (task.C7 = 6000) OR (task.C7 = 7000));   
    where Table1 = Schema ID for SMT:Social_FollowConfig  
    Table2 = Schema ID for TMS:Task 
      
        
      
       
    • Delete closed or cancelled Problems:
      
      DELETE FROM Table1 WHERE C1000000182 IN (Select pbi.C1000000232 from Table2 pbi join Table1 social on pbi.C179 = social.C1000001563 where pbi.C1000000232 = social.C1000000182 AND ((pbi.C7 = 8) OR (pbi.C7 = 9)));  
    where Table1 = Schema ID for SMT:Social_FollowConfig  
    Table2 = Schema ID for PBM:Problem Investigation 
      
        
      
       
    • Delete closed or cancelled Known Errors:
      
      DELETE FROM Table1 WHERE C1000000182 IN (Select pke.C1 from Table2 pke join Table1 social on pke.C179 = social.C1000001563 where pke.C1 = social.C1000000182 AND ((pke.C7 = 5) OR (pke.C7 = 6)));  
    where Table1 = Schema ID for SMT:Social_FollowConfig  
    Table2 = Schema ID for PBM:Known Error 
      
        
      
       
    • Delete closed Knowledge Articles:
      
      DELETE FROM Table1 WHERE C1000000182 IN (Select rkm.C302300507 from Table2 rkm join Table1 social on rkm.C179 = social.C1000001563 WHERE ((rkm.C302312185 = 1000) OR (rkm.C302312185 = 1100)));  
    where Table1 = Schema ID for SMT:Social_FollowConfig  
    Table2 = Schema ID for RKM:KnowledgeArticleManager  
      
      
       
    • Delete closed Releases:
      
      DELETE FROM Table1 WHERE C1000000182 IN (Select rel.C303489800 from Table2 rel join Table1 social on rel.C179 = social.C1000001563 WHERE rel.C303489800 = social.C1000000182 AND ((rel.C7 = 7000) OR (rel.C7 = 8000)));  
    where Table1 = Schema ID for SMT:Social_FollowConfig  
    Table2 = Schema ID for RMS:Release  
      
      
       
    • Delete from Activity
      
      DELETE FROM Table1 WHERE C1000000182 IN (Select aas.C1000000182 from Table2 aas join Table1 social on aas.C1000000182 = social.C1000000182 WHERE aas.C179 = social.C1000001563 AND ((aas.C7 = 70) OR (aas.C7 = 80)));  
    where Table1 = Schema ID for SMT:Social_FollowConfig  
    Table2 = Schema ID for AAS:Activity 
      
        
      
       
    • Delete closed and cancelled Incidents:
      
      DELETE FROM Table1 WHERE C1000000182 IN (Select hpd.C1000000161 from Table2 hpd join Table1 social on hpd.C179 = social.C1000001563 where hpd.C1000000161= social.C1000000182 AND ((hpd.C7 = 5) OR (hpd.C7 = 6)));  
    where Table1 = Schema ID for SMT:Social_FollowConfig  
    Table2 = Schema ID for HPD:Help Desk  
      
      
       
    • Delete closed Workorders:
      
      DELETE FROM Table1 WHERE C1000000182 IN (Select woi.C1000000182 from Table2 woi join Table1 social on woi.C179 = social.C1000001563 WHERE woi.C1000000182 = social.C1000000182 AND((woi.C7 = 7) OR (woi.C7 = 8)));  
    where Table1 = Schema ID for SMT:Social_FollowConfig  
    Table2 = Schema ID for WOI:WorkOrder  
      
      
       
    • Delete closed Requests:
      
      DELETE FROM Table1 WHERE C1000000182 IN (Select srm.C1000000829 from Table2 srm join Table1 social on srm.C179 = social.C1000001563 WHERE social.C1000000182 = srm.C1000000829 AND ((srm.C7 = 8000) OR (srm.C7 = 9000)));  
    where Table1 = Schema ID for SMT:Social_FollowConfig  
    Table2 = Schema ID for SRM:Request 
      
        
      



     

     


    Article Number:

    000170189


    Article Type:

    Product/Service Description



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles