9 replies to this topic

[Bimal]

We are having PROIV application (MSA Focus - Perspective) on one server and SQL 2000 server on a different one (both DELL PE2650) with dual 2.8Mhz processor, 6GB RAM and SCSI drives on Windows 2000 advanced server. On performance monitor all the main components usage are normal like CPU, Memory and I/O, but Network Interface Packages/sec is always on 100% usage and due to this most of the user connections and data retrieval are sluggish and very often the users are thrown out and in the application log we get the error message 'PROIV thread exception occurred'.

Could you please help us solving the issue?

[Joseph Bove]

Bimal,

One thing I would check for is a virus in your network.

Another thing might be to upgrade the driver on the NIC or even swap out the NIC. You could have a "chatty" NIC.

One other idea comes to mind. Turn off the ProIV service. Is the network traffic still at 100%?

hth,

Joseph

[Bimal]

Thanks Joseph for reply.

We have McAfee antivirus on all of servers and Norton on client side and updated on the fly all time. We used Distinct Netowork monitoring tool to identify the traffic between these two servers and found that 90% of traffic is TCP - TDS stream on port 1433 which is SQL server traffic and the rest is PROIV client connections through port 23. We tested by stopping PROIV service and then NIC traffic becomes normal. So it is genuine traffic. Is there any tweaking required on PROIV level to reduce the SQL traffic?

I will check with our Network administrator regarding the 'chatty' NIC.

Namaste,

Bimal

[Joseph Bove]

Bimal

Is there any tweaking required on PROIV level to reduce the SQL traffic?

If there is, I'm blissfully ignorant of it.

I'd be tempted to install the application on another computer - even a workstation and just see if that kind of traffic recurs there as well. For whatever reason, my gut reaction would be to try to isolate the issue as potentially hardware / networking and not necessarily ProIV. Of course, I reserve the right to be wrong!

Regards,

Joseph

[Joseph Bove]

Bimal,

Can it be from the application the way the SQL statement constructed which fetches all the data? Since PROIV uses embedded cursors, how can we see the actual SQL statement executing?

There certainly could be issues of performance problems due to the ProIV generated SQL, the database design, etc.

However, these issues would seemingly not lead to 100% NIC usage all of the time.

If you are seeing 100% NIC with 1 user logged in, it's overwhelmingly likely that you are not dealing with a ProIV issue. If the NIC usage rises with the overall system usage, then application design / lack of SQL tuning could be an issue.

One (unlikely) thing you may want to check:

Make sure that all of your tables have indexes. I have seen issues through one of our clients where, when migrating a SQL database through a bulk data transfer, all of the indexes disappeared.

If you think it is application related, then look for functions that read large amounts of records only to DSELect the majority of records that they just read.

hth,

Joseph

[Bimal]

Joseph,

Since the application is a thirdy party one we can not look into the functions, but I have checked the SQL tables which all of them are having indexes. Is there any debugging tool which we can use it for identifying the SQL sts from PROIV?

Namaste,

Bimal

[Joseph Bove]

Bimal,

You can turn on a ProIV trace of the SQL.

The main problem that you will run into is the sheer volume of information that you'll have to deal with. Is it impossible to get the vendor involved?

Regards,

Joseph

[Bimal]

Thanks a lot for all your support Joseph, now we will try that option.

Namaste,
Bimal