SQL Server 2012 high number of SQL Sessions

[Former Member]

Hi, We are using a 7.3 portal using ESS+UWL with sql server 2012 and sometimes (suddenly) we have a high cpu (99-100%) consumption by the sql process on the DB-Server of the Portal. In addition the number of sql-sessions increase heavily from normal 2-3 concurrent up to 400 or more until the end is reached (50 per server process?). Sometimes the problem is solved itself (head blocker SQL-session disappears) after half an hour or more, sometimes it is not solved and we have a system standstill.

The strange thing is, that this situation occurs sometimes at times of heavy load, sometimes on weekends or in the night, when the system is not busy at all.

Do you have any hint how to start analyzing the root cause when next time sessions and CPU usage rise up dramatically?

[former_member188883]

Hi Franz,

Need to more about you server configuration like CPU and RAM capacity.

Secondly please have a look into SAP note for MSSQL configuration

1702408 - Configuration Parameters for SQL Server 2012

Hope this helps.

Regards,

Deepak Kori

[Former Member]

Hi Deepak,

thank you for the note, i checked it already, everything is set like recommended.

RAM: 16, Cores: 8

regards,

Franz

[Former Member]

Was there any backup activity ON, when you observed this behaviour?

Regards,

Nick Loy

[Former Member]

Hi Nick! No backup activity that time.

regards,

Franz

[Sriram2009]

Hi Franz

You can define over all 30% memory space to MS Sql MMC. and than check the system performance.

and also you refer the SAP Note 1612283 - Hardware Configuration Standards and Guidance

BR

SS

[Former Member]

Dear S Sriram,

sorry, i did not understand: where shall i define additional memory? By the way memory usage is not the problem.

[Sriram2009]

Hi

You can define in MS SQL studio management on Windows OS. Could you refer the Microsoft link

Server Memory Server Configuration Options

BR

SS

[Former Member]

Thank you for this link. But memory is not our problem.

[Sriram2009]

Hi Franz

1. Could you explain about your SAP & DB servers environment?

2. During the CPU peak could you check the MS Sql log any error message or deadlock and in SAP Application log transaction code SM21?

3. Refer SCN link  the MS Sql Performance tuning of MS Sql DB

http://scn.sap.com/community/sqlserver/blog/2012/09/28/spring-clean-and-tune-your-sql-server-sap-dat...

BR

SS