Smart IT Dashboard slowness causes unresponsive system

Version 16
    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:

    Slowness, GLEWF calls, Dashboard Console, SMT:WorklogView This also applies to Smart IT 18.08



    QUESTION:

    Environment Details:
    OS: Windows Server 2016 Standard. 
    DB: MSSQL 2016 
    ARS, ITSM, CMDB, RKM, SRM, SLM: 1805 patch 5 
    SmartIT: {"buildNumber":"78","buildTime":1537339920000,"smartITVersion":"18.05.00.005","smartITPreviousVersion":null,"changelist":"4c2f60ee264b3941e4c04deebe99a93c3fa31b0d","ucBuildNumber":"70"} 

    Accessing the Dashboard causes slowness which leads to an unresponsive system.


    ANSWER:

     

    Solution when culprit is SMT:Social_WorklogView:

         
    Analyzing the API +FILTER+SQL logs we realized that there is lot of slowness reported for an GLEWF API call on SMT:Social_WorklogView and it takes around 2 min.  
       

    Based on the observation, it looks like the query is running on T2118, which is SMT:Social_WorkLogView. This view refers to the database view U_SmartIT_Social_Worklogs. This query is generated when the user is accessing the Smart IT dashboard.

        (LA screencapture) 
      LogAnalysis 
     
      GLEWFcall 
     
      Query:
    ==================================================
    WITH AR_SQL_Alias$1 AS (SELECT T2118.C1, T2118.C1000000182, T2118.C179, T2118.C1000005261, T2118.C2, T2118.C1000000151, T2118.C1000000159, T2118.C1000000761, T2118.C1000000218, T2118.C1000000655, T2118.C1000001563, T2118.C10001959, T2118.C304309590, T2118.C304309600, T2118.C302308591, ROW_NUMBER() OVER (ORDER BY T2118.C1000005261 DESC, T2118.C1 ASC) AS 'AR_RowNumber_Alias$1' FROM T2118 WHERE (((T2118.C1000000182 IN (SELECT FVALUE FROM #AR704NVARCHAR415)) AND (T2118.C1000005261 > ?)) OR ((T2118.C1000001563 = ?) AND (T2118.C1000000182 = ?) AND (T2118.C1000005261 > ?)))) SELECT AR_SQL_Alias$1.C1, AR_SQL_Alias$1.C1000000182, AR_SQL_Alias$1.C179, AR_SQL_Alias$1.C1000005261, AR_SQL_Alias$1.C2, AR_SQL_Alias$1.C1000000151, AR_SQL_Alias$1.C1000000159, AR_SQL_Alias$1.C1000000761, AR_SQL_Alias$1.C1000000218, AR_SQL_Alias$1.C1000000655, AR_SQL_Alias$1.C1000001563, AR_SQL_Alias$1.C10001959, AR_SQL_Alias$1.C304309590, AR_SQL_Alias$1.C304309600, AR_SQL_Alias$1.C302308591 FROM AR_SQL_Alias$1 WHERE (AR_SQL_Alias$1.AR_RowNumber_Alias$1 BETWEEN ? AND ?) 
    ==================================================
       
        
              
       
       
       Server-Temp-Table-Threshold: 1000 and then proceed to restart the AR Server
    (with this parameter be aware of the below defect/considerations) 
      
         
                                                        
    SW00551614Value of Server-Temp-Table-Threshold should be less than 2100 for SQL Server and less than 1000 for ORACLE. if you set something higher than above values then you will get below errors. 
    SQL Server: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL ; SQL state S0001; error code 8003; The incoming request has too many parameters. The server supports a maximum of 2100 parameters. 
    ORACLE: com.bmc.arsys.domain.etc.ARException: ERROR (552): The SQL database operation failed.; ORA-01795: maximum number of expressions in a list is 1000
         
        USE CASE 2 (CPU performance AR and at DB side it is also high)
    If above things have been applied and there is still some performance issues, you may want to double check the SQL logs you may notice that the SQL query has a lot of 'OR' operator as below snippet:


    SELECT * FROM (SELECT AR_SQL_Alias$1.*, rownum as AR_RowNumber_Alias$1 FROM
                    (SELECT T7801.C1, T7801.C1000000182, T7801.C179, T7801.C1000005261, T7801.C2, T7801.C1000000151, T7801.C1000000159, T7801.C1000000761, T7801.C1000000218, T7801.C1000000655, T7801.C1000001563, T7801.C10001959,
                    T7801.C304309590, T7801.C304309600, T7801.C302308591
                    FROM T7801 WHERE (((T7801.C1000000182 IN (SELECT FVALUE FROM ARFTS707201276_401_0)) OR ((T7801.C1000001563 = :1 ) AND (T7801.C1000000182 = :2 )))
                    AND ((T7801.C2 = :3 ) OR exists ( select /*+ NO_EXPAND */ 1 from dual where ((T7801.C112 LIKE :4 ) OR (T7801.C112 LIKE :5 ) OR (T7801.C112 LIKE :6 ) OR (T7801.C112 LIKE :7 ) OR (T7801.C112 LIKE :8 ) OR
                    (T7801.C112 LIKE :9 ) OR (T7801.C112 LIKE :10 ) OR (T7801.C112 LIKE :11 ) OR (T7801.C112 LIKE :12 ) OR (T7801.C112 LIKE :13 ) OR (T7801.C112 LIKE :14 ) OR (T7801.C112 LIKE :15 ) OR (T7801.C112 LIKE :16 ) OR
                    (T7801.C112 LIKE :17 ) OR (T7801.C112 LIKE :18 ) OR (T7801.C112 LIKE :19 ) OR (T7801.C112 L IKE :20 ) OR (T7801.C112 LIKE :21 ) OR (T7801.C112 LIKE :22 ) OR (T7801.C112 LIKE :23 ) OR (T7801.C112 LIKE :24 ) OR
                    (T7801.C112 LIKE :25 ) OR (T7801.C112 LIKE :26 ) OR (T7801.C112 LIKE :27 ) OR (T7801.C112 LIKE :28 ) OR (T7801.C112 LIKE :29 ) OR (T7801.C112 LIKE :30 ) OR (T7801.C112 LIKE :31 ) OR (T7801.C112 LIKE :32 ) OR
                    (T7801.C112 LIKE :33 ) OR (T7801.C112 LIKE :34 ) OR (T7801.C112 LIKE :35 ) OR (T7801.C112 LIKE :36 ) OR (T7801.C112 LIKE :37 ) OR (T7801.C112 LIKE :38 ) OR (T7801.C112 LIKE :39 ) OR (T7801.C112 LIKE :40 ) OR
                    (T7801.C112 LIKE :41 ) OR (T7801.C112 LIKE :42 ) OR (T7801.C112 LIKE :43 ) OR (T7801.C60900 LIKE :44 ) OR (T7801.C60900 LIKE :45 ) OR (T7801.C60900 LIKE :46 ) OR (T7801.C60900 LIKE :47 ) OR (T7801.C60900 LIKE :48 ) OR
                    (T7801.C60900 LIKE :49 ) OR (T7801.C60900 LIKE :50 ) OR (T7801.C60900 LIKE :51 ) OR (T7801.C60900 LIKE :52 ) OR (T7801.C60900 LIKE :53 ) OR (T7801.C60900 LIKE :54 ) OR (T7801.C60900 LIKE :55 ) OR (T7801.C60900 LIKE :56 ) OR
                    (T7801.C60900 LIKE :57 ) OR (T7801.C60900 LIKE :58 ) OR (T7801.C60900 LIKE :59 ) OR (T7801.C60900 LIKE :60 ) OR (T7801.C60900 LIKE :61 ) OR (T7801.C60900 LIKE :62 ) OR (T7801.C60900 LIKE :63 ) OR (T7801.C60900 LIKE :64 ) OR
                    (T7801.C60900 LIKE :65 ) OR (T7801.C60900 LIKE :66 ) OR (T7801.C60900 LIKE :67 ) OR (T7801.C60900 LIKE :68 ) OR (T7801.C60900 LIKE :69 ) OR (T7801.C60900 LIKE :70 ) OR (T7801.C60900 LIKE :71 ) OR (T7801.C60900 LIKE :72 ) OR......


    On this use case we added Disable-New-RLS-Implementation  F
    (on the docs it says it defaults it to T) 


    This increased performance dramatically and reduced CPU performance

    Note: Disable-New-RLS-Implementation has impact only on the rls query. If true, the query is made using or and when false, the query uses regex for dynamic group permissions.

    **When the culprit is the SMT:Social:FollowConfig form, please review this article 
        000170189  
       
      

     


    Article Number:

    000163021


    Article Type:

    FAQ/Procedural



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