Virtual Memory Question

[sguslan]

Terry ,

I've setup an dedicated SQL server 2000/SP2 with one mission: read rows from OS390/DB2 through HIS2000 and linked server to DB2, then store and forward data to 2 Oracle servers[7.3 and 8i]
I experienced VM [at least that is my conclusion] problems where, immediately, after running a linked server query against DB2, the VM grows too fast up to >600 MB; after which the SQL server can not execute any queries against DB2 till re-started. That prompted me to schedule a DOS NET command to restart SQL 3 times a day. The server is quite equipped for its mission: Compaq 7000 with 512 RAM, RAID 0 with 3 SCSI drives and 3 NIC. CPU usage is quite low.
After installing HIS2000 SP1 the situation improved a lot but still fails every 10 days or so. VM grows up to > 700 MB. Now again i scheduled a restart only once a day.


Any thoughts ...

 

[tlbroadbent]

How many rows are returned from DB2 when the query is run? How long are the rows? How large is the SQL Server transaction log? How much disk space is available for database transaction log and tempdb growth? What other services and applicarions run on the SQL Server?

We run several servers and they can run for weeks without rebooting - even the server with 256 MB of memory. Most of our servers have 1GB or more of RAM. We also try to maintain at least 1GB of available disk space on the system drive and 2GB on data drives. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[sguslan]

Thank you Terry for taking the time to help.
Each query returns between 1 to 13000 rows as the DB2 is our main OLTP system. The queries returning large numbers of rows is run only a few times a day. The size of each row is not much around 400 bytes.
Disk subsys has one partition containing Data , SQL and Win2k. There is an ample free disk space around 7 GB all the time. The transaction log grows to 3G with a nightly scheduled task to fully backup the database and the logs. There are two production databases on the server. Tempdb growth is normal.
The services that run on the machine are: sql, HIS200 and IIS5. HIS2000 is used by the SQL server only. IIS is hardly used; one user uses it 3 to 4 times week to fetch a few records from the DB2 through the SQL linked server using .ASP pages.

I do have another similar machine, albait newer and more equipped with 1 G memory. It does not have the same problem though running the same services as the troublesome server.
The only difference is it was not patched with any sql SP.

Forgot to mention both servers have dual processors....

 

[tlbroadbent]

I probably can't be of much help on this question. I don't know what HIS2000 is and I'm not very knowledgable about operating systems.

What is the physical and virtual memory usage for each service and process? How much memory is actually available for SQL Server? Which SQL performance stats are you monitoring? Which OS stats are you monitoring in addition to VM and processor usage? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[sguslan]

Thanks Terry for trying. I do not expect an immediate answer as the subject involves many services and databases from different vendors. But even Microsoft.com technical chat sites did not help me. HIS2000 is MS mainframe integration platform for connecting to IBM DB2 and CICS systems. It serves 2 purposes: provide SNA LU2,3 and 6.2 network connectivity to the win domains[aka gateway], and create and run COM objects to interact to CICS transaction monitor which can be called from any client/application server that needs to invoke any transaction on the mainframe. It is pretty neat and in my view it is the best in its class.
People use it to preserve their investments in CICS yet modernizing the client. So instead of relying solely on the legacy 3270 terminals, the same COBOL CICS programs are simultaneously accessed by terminal and clientserver/web clients.

As for memory I leave tuning for SQL itself. I never set the boundaries for ohysical memroy usage.
I only monitor VM and CUP usage as i am not conversant with win2k performance monitoring.
By the way all services/processes on the server keep almost constant VM usage except SQL. It starts with 242MB and grows to over 700MB.
Sorry to trouble you but i am away from office and can not provide you with more details, but will when back in office.

Thanks for your insights any way. We all consider you our right arm when it comes to SQL.

GOD bless ...