SQL Server is lagging despite using only 10GB RAM out of 80GB

Question

I have a server that is hosting 3 Web apps: web app A is not used frequently. web app B is use daily with total 13-14 users. This web app will access SQL Server for create, read, update, delete query. web app C is a folder browser. Web app B will add files in web app C.

Regarding the server spec: Server Spec

The SQL server seems to be accumulating memory. In 1 week, the memory can exceeds to 10GB of RAM usage, and started to get lagging, having SQLException timeout error.
SQL Server memory usage

I thought 80GB of RAM would be enough and SQL server will just use the resources given that I did not change the Max memory value. SQL Server memory default setting

Web app B is a custom made where the query to SQL Server is attached:

internal List<object> GetDbDataSqlConnection(string commandText, Dictionary<string, object> kvp, string columns, string company = null)
        {
            List<object> dataList = new List<object>();

            try
            {
                using (SqlConnection sqlConnection = new SqlConnection($"data source={WebConfigurationManager.AppSettings["sqldatasource"]};initial catalog={company ?? WebConfigurationManager.AppSettings["defaultsqldb"]};user id={WebConfigurationManager.AppSettings["sqlusername"]};password={WebConfigurationManager.AppSettings["sqlpassword"]};MultipleActiveResultSets=True;App=EntityFramework"))
                {
                    using (SqlCommand cmd = new SqlCommand { CommandText = commandText, CommandType = CommandType.Text, Connection = sqlConnection })
                    {
                        if (kvp != null) foreach (KeyValuePair<string, object> p in kvp) cmd.Parameters.AddWithValue($"@{p.Key.Replace(".", string.Empty)}", p.Value);

                        sqlConnection.Open();

                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Dictionary<string, object> data = new Dictionary<string, object>();
                                foreach (string c in columns.Split(','))
                                {
                                    string x = c.Contains(" AS ") ? c.Substring(c.IndexOf(" AS ") + 4) : c;
                                    x = x.Replace("[", string.Empty).Replace("]", string.Empty);
                                    x = x.Substring(x.IndexOf(".") + 1);
                                    data.Add(x, Convert.ToString(reader[x]).Trim());
                                }
                                dataList.Add(data);
                            }

                            reader.Close();
                        }

                        sqlConnection.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog($"[ERROR] on GetDbDataSqlConnection");
                Logger.WriteLog($"Exception message : {ex.Message}");
                Logger.WriteLog($"Exception : {ex}");
                throw ex;
            }

            return dataList;
        }

My question is, is there anything that I need to do to improve the code or the SQL Server configuration to prevent this from happening again?

thank you.

I had to restart SQL service then the web app B become smooth again.

Edit 1: I am attaching the result from query Wait Stat

Edit 2: The Web App B do a lot of things.

1. select data directly from SQL Mobile app send GET request to Web app B. Then web app B do SQL select query, and response back to mobile app.

2. do API request to a web API Mobile app send POST Request to Web app B. Web app B do validation, that consist of SQL select query, and send a POST request to a Web API. The web API is also hosted on the same server. The Web App B response back to mobile app.

Edit 3: Error message

Exception : System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at SalesMaster.Business.DataGeneral.DatabaseDataProcess.GetDbDataSqlConnection(String commandText, Dictionary`2 kvp, String columns, String company) 

Edit 4: these are some of the query that is used.

[13-03-2024T08:42:19]    Command Text=SELECT C.IDCUST,C.NAMECUST,C.PRICLIST,C.TEXTPHON1 
FROM JDCUSGRP CG INNER JOIN ARCUS C ON CG.IDCUST = C.IDCUST 
WHERE CODESLSP=@CODESLSP AND SWACTV=@SWACTV 
ORDER BY C.IDCUST;
[13-03-2024T08:42:22]    Command Text=SELECT DISTINCT T.ITEMNO, T.FMTITEMNO, T.[DESC], T.DEFPRICLST, T.TQUANTITY - T.FQUANTITY + T.AQUANTITY - T.OQUANTITY + T.CQUANTITY + T.XQUANTITY AS STOCK 
FROM(
    SELECT I.ITEMNO, I.FMTITEMNO, I.[DESC], I.DEFPRICLST, 
    CASE WHEN FTF.QUANTITY IS NOT NULL THEN FTF.QUANTITY ELSE 0 END AS FQUANTITY, 
    CASE WHEN TTF.QUANTITY IS NOT NULL THEN TTF.QUANTITY ELSE 0 END AS TQUANTITY, 
    CASE WHEN AD.QUANTITY IS NOT NULL THEN AD.QUANTITY ELSE 0 END AS AQUANTITY, 
    CASE WHEN ORD.QUANTITY IS NOT NULL THEN ORD.QUANTITY ELSE 0 END AS OQUANTITY, 
    CASE WHEN CRD.QUANTITY IS NOT NULL THEN CRD.QUANTITY ELSE 0 END AS CQUANTITY, 
    CASE WHEN XCH.QUANTITY IS NOT NULL THEN XCH.QUANTITY ELSE 0 END AS XQUANTITY 
    FROM JDITMGRP IG 
    INNER JOIN ICITEM I ON IG.ITEMNO = I.ITEMNO 
    LEFT JOIN ICLOC L ON IG.CODESLSP = L.[DESC] 
    OUTER APPLY(SELECT DISTINCT SUM(D.QUANTITY) AS QUANTITY 
                FROM ICTRED D 
                INNER JOIN ICTREH H ON D.TRANFENSEQ = H.TRANFENSEQ 
                WHERE D.ITEMNO = I.FMTITEMNO AND D.FROMLOC = L.LOCATION AND H.TRANSDATE = @Transdate AND H.DOCTYPE = 1 
                GROUP BY D.ITEMNO) FTF 
    OUTER APPLY(SELECT DISTINCT SUM(D.QUANTITY) AS QUANTITY 
                FROM ICTRED D INNER JOIN ICTREH H ON D.TRANFENSEQ = H.TRANFENSEQ 
                WHERE D.ITEMNO = I.FMTITEMNO AND D.TOLOC = L.LOCATION AND H.TRANSDATE = @Transdate AND H.DOCTYPE = 1 
                GROUP BY D.ITEMNO) TTF 
    OUTER APPLY(SELECT DISTINCT SUM(D.QUANTITY) AS QUANTITY 
                FROM ICADEH H 
                INNER JOIN ICADED D ON H.ADJENSEQ = D.ADJENSEQ 
                WHERE D.ITEMNO = I.FMTITEMNO AND H.REFERENCE = L.LOCATION AND H.TRANSDATE = @Transdate 
                GROUP BY D.ITEMNO) AD 
    OUTER APPLY(SELECT DISTINCT SUM(D.ORIGQTY) AS QUANTITY 
                FROM OEORDH H 
                INNER JOIN OEORDD D ON H.ORDUNIQ = D.ORDUNIQ 
                WHERE D.ITEM = I.FMTITEMNO AND H.LOCATION = L.LOCATION AND H.ORDDATE = @Transdate 
                GROUP BY D.ITEM) ORD 
    OUTER APPLY(SELECT DISTINCT SUM(D.QTYRETURN) AS QUANTITY 
                FROM OECRDH H 
                INNER JOIN OECRDD D ON H.CRDUNIQ = D.CRDUNIQ 
                WHERE D.ITEM = I.FMTITEMNO AND H.LOCATION = L.LOCATION AND H.CRDDATE = @Transdate 
                GROUP BY D.ITEM) CRD 
    OUTER APPLY(SELECT DISTINCT SUM(X.XCHGQTY) AS QUANTITY 
                FROM JDXCHGRT X WHERE X.ITEMNO = I.ITEMNO AND X.CODESLSP = IG.CODESLSP AND XCHGDATE = @Transdate 
                GROUP BY X.ITEMNO) XCH 
                WHERE IG.CODESLSP = @Codeslsp) T 
                ORDER BY T.ITEMNO;
[13-03-2024T08:42:23]    Command Text=SELECT DISTINCT T.*, CASE WHEN P2.SALESTART <> 0 AND P2.SALEEND <> 0 THEN P2.UNITPRICE ELSE P1.UNITPRICE END AS UNITPRICE
FROM (
    SELECT CURRENCY, ITEMNO, PRICELIST FROM ICPRICP
    WHERE PRICELIST IN(
        SELECT DISTINCT C.PRICLIST AS PRICLIST FROM JDCUSGRP CG INNER JOIN ARCUS C ON CG.IDCUST = C.IDCUST WHERE CG.CODESLSP = 'S1'
        UNION
        SELECT DISTINCT I.DEFPRICLST AS PRICLIST FROM JDITMGRP IG INNER JOIN ICITEM I ON IG.ITEMNO = I.ITEMNO WHERE IG.CODESLSP = 'S1'
    ) AND ITEMNO IN (SELECT ITEMNO FROM JDITMGRP WHERE CODESLSP = 'S1')
) T
LEFT JOIN ICPRICP P1 ON T.CURRENCY = P1.CURRENCY AND T.ITEMNO = P1.ITEMNO AND T.PRICELIST = P1.PRICELIST AND P1.DPRICETYPE = 1
LEFT JOIN ICPRICP P2 ON T.CURRENCY = P2.CURRENCY AND T.ITEMNO = P2.ITEMNO AND T.PRICELIST = P2.PRICELIST AND P2.DPRICETYPE = 2
;
[13-03-2024T08:43:06]    Command Text=SELECT BANK,NAME FROM BKACCT  ORDER BY BANK;
[13-03-2024T08:43:06]    Command Text=SELECT LOCATION,[DESC] FROM ICLOC  ORDER BY [DESC];
[13-03-2024T08:44:32]    Command Text=SELECT H.REFERENCE,H.INVNUMBER,H.CUSTOMER,C.NAMECUST,C.PRICLIST,C.TEXTPHON1 
FROM OEINVH H 
INNER JOIN ARCUS C ON H.CUSTOMER = C.IDCUST 
WHERE H.INVDATE=@HINVDATE AND H.SALESPER1=@HSALESPER1 
GROUP BY H.REFERENCE,H.INVNUMBER,H.CUSTOMER,C.NAMECUST,C.PRICLIST,C.TEXTPHON1 
ORDER BY H.REFERENCE;
[13-03-2024T08:44:40]    Command Text=SELECT H.INVNUMBER,H.REFERENCE,B.IDBANK,R.TXTRMITREF,R.IDRMIT,H.CUSTOMER,C.NAMECUST,
CASE WHEN R.CODEPAYM IS NULL THEN CASE WHEN C.CODETERM = 'CASH' THEN 'UNPAID' ELSE 'CREDIT' END ELSE R.CODEPAYM END AS PAYTYPE,
CASE WHEN P.AMTPAYM IS NULL THEN H.INVNETWTX ELSE P.AMTPAYM END AS AMOUNT,H.INVDISCAMT,I.ITEMNO,D.ITEM,D.[DESC],D.QTYORDERED,D.UNITPRICE 
FROM OEINVH H 
INNER JOIN OEINVD D ON H.INVUNIQ = D.INVUNIQ 
LEFT JOIN ARTCP P ON H.INVNUMBER = P.IDINVC 
LEFT JOIN ARTCR R ON P.CNTBTCH = R.CNTBTCH AND P.CNTITEM = R.CNTITEM 
LEFT JOIN ARBTA B ON R.CNTBTCH = B.CNTBTCH 
INNER JOIN ARCUS C ON H.CUSTOMER = C.IDCUST 
INNER JOIN ICITEM I ON D.ITEM = I.FMTITEMNO 
WHERE H.INVDATE=@HINVDATE AND H.SALESPER1=@HSALESPER1  ;
[13-03-2024T08:44:59]    Command Text=SELECT DISTINCT IG.ITEMNO, I.FMTITEMNO, I.[DESC], ISNULL(TFT.QUANTITY, 0) - ISNULL(TFF.QUANTITY, 0) + ISNULL(AD.QUANTITY, 0) AS STOCK, ISNULL(ORD.QUANTITY, 0) AS SALE, ISNULL(CRD.QUANTITY, 0) AS CREDIT, ISNULL(XCH.QUANTITY, 0) AS EXCHANGE 
FROM JDITMGRP IG 
INNER JOIN ICITEM I ON IG.ITEMNO = I.ITEMNO 
LEFT JOIN ICLOC L ON IG.CODESLSP = L.[DESC] 
OUTER APPLY(SELECT DISTINCT SUM(D.QUANTITY) AS QUANTITY 
            FROM ICTRED D 
            INNER JOIN ICTREH H ON D.TRANFENSEQ = H.TRANFENSEQ 
            WHERE D.ITEMNO = I.FMTITEMNO AND D.TOLOC = L.LOCATION AND H.TRANSDATE = @Transdate AND H.DOCTYPE = 1 GROUP BY D.ITEMNO) TFT 
OUTER APPLY(SELECT DISTINCT SUM(D.QUANTITY) AS QUANTITY FROM ICTRED D 
            INNER JOIN ICTREH H ON D.TRANFENSEQ = H.TRANFENSEQ 
            WHERE D.ITEMNO = I.FMTITEMNO AND D.FROMLOC = L.LOCATION AND H.TRANSDATE = @Transdate AND H.DOCTYPE = 1 
            GROUP BY D.ITEMNO) TFF 
OUTER APPLY(SELECT DISTINCT SUM(D.QUANTITY) AS QUANTITY 
            FROM ICADED D 
            INNER JOIN ICADEH H ON D.ADJENSEQ = H.ADJENSEQ 
            WHERE D.ITEMNO = I.FMTITEMNO AND H.REFERENCE = L.LOCATION AND H.TRANSDATE = @Transdate 
            GROUP BY D.ITEMNO) AD 
OUTER APPLY(SELECT DISTINCT SUM(D.ORIGQTY) AS QUANTITY 
            FROM OEORDH H 
            INNER JOIN OEORDD D ON H.ORDUNIQ = D.ORDUNIQ 
            WHERE D.ITEM = I.FMTITEMNO AND H.LOCATION = L.LOCATION AND H.ORDDATE = @Transdate 
            GROUP BY D.ITEM) ORD 
OUTER APPLY(SELECT DISTINCT SUM(D.QTYRETURN) AS QUANTITY 
            FROM OECRDH H 
            INNER JOIN OECRDD D ON H.CRDUNIQ = D.CRDUNIQ 
            WHERE D.ITEM = I.FMTITEMNO AND H.LOCATION = L.LOCATION AND H.CRDDATE = @Transdate 
            GROUP BY D.ITEM) CRD 
OUTER APPLY(SELECT DISTINCT SUM(X.XCHGQTY) AS QUANTITY 
            FROM JDXCHGRT X WHERE X.ITEMNO = I.ITEMNO AND X.CODESLSP = IG.CODESLSP AND X.XCHGDATE = @Transdate 
            GROUP BY X.ITEMNO) XCH 
WHERE IG.CODESLSP=@IGCODESLSP ;
[13-03-2024T08:44:59]    Command Text=SELECT DISTINCT I.ITEMNO, D.ITEM, D.[DESC], D.UNITPRICE, D.QTYORDERED, 
STRING_AGG(TRIM(COALESCE(R.CODEPAYM, CASE WHEN C.CODETERM = 'CASH' THEN 'UNPAID' ELSE 'CREDIT' END)), ' & ') WITHIN GROUP (ORDER BY TRIM(COALESCE(R.CODEPAYM, CASE WHEN C.CODETERM = 'CASH' THEN 'UNPAID' ELSE 'CREDIT' END))) AS PAYTYPE 
FROM OEINVH H 
INNER JOIN OEINVD D ON H.INVUNIQ = D.INVUNIQ 
INNER JOIN ICITEM I ON D.ITEM = I.FMTITEMNO 
LEFT JOIN ARTCP P ON H.INVNUMBER = P.IDINVC 
LEFT JOIN ARTCR R ON P.CNTBTCH = R.CNTBTCH AND P.CNTITEM = R.CNTITEM 
INNER JOIN ARCUS C ON H.CUSTOMER = C.IDCUST 
WHERE H.SALESPER1=@HSALESPER1 AND H.INVDATE=@HINVDATE 
GROUP BY H.INVNUMBER, I.ITEMNO, D.ITEM, D.[DESC], D.UNITPRICE, D.QTYORDERED ;
[13-03-2024T08:45:10]    Command Text=SELECT SUM(R.AMTRMITTC) AS CASHONHAND 
FROM ARTCR R 
INNER JOIN ARTCP P ON R.CNTBTCH = P.CNTBTCH AND R.CNTITEM = P.CNTITEM 
INNER JOIN AROBL D ON P.IDINVC = D.IDINVC 
WHERE R.DATERMIT=@RDATERMIT AND R.CODEPAYM=@RCODEPAYM AND D.CODESLSP1=@DCODESLSP1  ;
[13-03-2024T08:46:42]    Command Text=SELECT CONAME,ADDR01,ADDR02,ADDR03,PHONE,HOMECUR FROM CSCOM WHERE ORGID=@ORGID ;