Performance problem when accessing big tables parallely

[former_member412764]

Hi Gurus,

Please find our system details.

SAP Product: ECC 6.0 - Retail environment

Hardware: 32 GB RAM, 8 processors - 2.66 Ghz , - 2 systems of same config (CI and DB)

OS: Windows 2003 R2, 64 bit

Database: SQL server 2005

Problem:

When 3 to 4 users concurrently execute pending invoice reports which reads BSIS table, the response time is very high and users are complaining continuously.

This is not only for BSIS table whenever there is concurrent read activity by more than 3 users on same table then the response time is increasing considerably.

Is there any parameter at DB level or fine tuning of ABAP code can be done to make sure concurrent read operations does not affect the performance of the system.

Regards,

Nalla.

[markus_doehr2]

> When 3 to 4 users concurrently execute pending invoice reports which reads BSIS table, the response time is very high and users are complaining continuously.

How much memory is assigned to the database?

Markus

[former_member412764]

Hi Markus,

We have assigned 16 GB both for min server value and max server value. DB is running on Node 2 along with DI where only limited users will be logging in.

Also I have one more doubt, if i have to update a field in index for a table, do I need to do both in MS SQL server through SQL studio and also in sap for the same table in se14 ? or only updating in SQL server is enough? Because if I add new filed to already existing index in DB, it is not reflecting in SAP and vice versa?

Regards,

Nalla.

[former_member412764]

Hi Markus,

We have assigned 16 GB both for min server value and max server value. DB is running on Node 2 along with DI where only limited users will be logging in.

Also I have one more doubt, if i have to update a field in index for a table, do I need to do both in MS SQL server through SQL studio and also in sap for the same table in se14 ? or only updating in SQL server is enough? Because if I add new filed to already existing index in DB, it is not reflecting in SAP and vice versa?

Regards,

Nalla.

[former_member412764]

Hi Markus,

We have assigned 16 GB both for min server value and max server value. DB is running on Node 2 along with DI where only limited users will be logging in.

Also I have one more doubt, if i have to update a field in index for a table, do I need to do both in MS SQL server through SQL studio and also in sap for the same table in se14 ? or only updating in SQL server is enough? Because if I add new filed to already existing index in DB, it is not reflecting in SAP and vice versa?

Regards,

Nalla.

[markus_doehr2]

> We have assigned 16 GB both for min server value and max server value. DB is running on Node 2 along with DI where only limited users will be logging in.

So that means all your physical memory is used by the database and all other necessary memory will need to be taken from swap. This slows down the system tremendeously. You have to keep in mind that the OS and the SAP applications (buffers + usable memory) is also taken from swap. That may be the cause why your system is slowing down when the SAP system allocates memory which is not there and hence being taking from swap. I'd decrease both database to 8 GB.

> Also I have one more doubt, if i have to update a field in index for a table, do I need to do both in MS SQL server through SQL studio and also in sap for the same table in se14 ? or only updating in SQL server is enough? Because if I add new filed to already existing index in DB, it is not reflecting in SAP and vice versa?

You change the index through SE11, then activate the index, then use SE14 to create the index on the database.

Markus