_sqlsrv2 process not using much RAM or CPU when running SQL queries.

[bruno.vilardo]

Hello All, good day,

OpenEdge 10.1C04
Red Hat Enterprise Linux Server release 6.10 (Santiago)
100GB RAM and 8 CPUs core

Issue:

SQL-92 does not take much CPU or RAM when running SQL statement from third party interface CyberQuery or Excel with ODBC.

Below are the startup parameters we have for both 4GL and SQL servers.

SQL secondary broker parameter file

-db dbname
-S dbname-srv
-L 200000
-ServerType SQL # Broker Service Type
-m3 # SQL-92 broker service
-Ma 5 # Max Clients
-Mi 5 # Min Clients
-Mpb 5 # Max Servers Per Broker
-N TCP # Network

4GL broker parameter file

-db dbname
-S dbname-srv
-B 1000000
-minport 33001
-maxport 39000
-cpinternal ISO8859-1
-cpstream ISO8859-1
-N TCP
-L 200000
-spin 120000
-Mf 10
-bibufs 150
-aibufs 150
-n 1000
-Ma 20
-Mn 30
-Mi 10
-tablerangesize 1000
-indexrangesize 2000
-T /protmp/brokers/brl
-ServerType 4GL
-omsize 8000

I tried running a SQL query in one of the tables of QAD, tr_hist and the _sqlsrv2 process spikes up to 56% usage of CPU for 1-2 seconds then it goes down to less than 1%.

Is there a way to optmize how the _sqlsrv2 process uses the CPU and RAM to help improving the peformance of the SQL queries?

I tried running update statistics as well as update index statistics for this table but did not get any difference. Also, set the fetch array size to 32000 in the ODBC DSN configuration but it does not change.

Please, let me know if any additional information is needed and if you have any recommendations.

Thanks in advance,
Best Regards,

Bruno

 

[TomBascom]

OpenEdge 10.1C is ancient, obsolete and unsupported. You should have upgraded 10 years ago. Is your company still running Windows XP desktops?

Yes, I know "but XYZ Corp only supports/certifies Progress version ABC". 1) BS, 2) Escalate, 3) So what? Running 10.1c04 is not being usefully supported. R-code and the database are both upward compatible. Upgrading from 10.1c04 to 10.2b08 is simple, safe, effective and free (if your Progress support is paid up). 10.2b08 is also retired but at least you get the benefit of a whole bunch of bug fixes and enhancements. The current release is 12.1, that is a somewhat more tedious upgrade but if you can compile your source code it is also simple, safe, effective and free. Actually you would save money because annual maintenance for supported versions is cheaper than annual maintenance for ancient releases.

Aside from the usual concerns about unpatched security vulnerabilities and compatibility issues, a particular concern is that SQL92 had many performance "challenges" and that "update statistics" wasn't always helpful and was indeed sometimes anti-helpful in that era.

Do you really have 1,000 db connections? You've only got about 600 defined for TCP/IP connections.