SAP on SQL checklists for maximum performance and high availability

While one can search a lot of documents in SAP on SQL. Running SAP on SQL server and notes in BC-DB-MSS, what I want to do is integrate all materials togather and provide a single place to check your SAP on SQL system. Okay, let’s build a checklist and if you have any comments, please give me a feedback below.

1. First things first, the most important factor to affect the system performace is memory so please make sure you install and allocate sufficient memory for SQL server. SAP KBA 1612283 – Hardware Configuration Standards and Guidance mentions this in great detail. I recommend the size of data cache should be more than 20% of the database size after compressed.

2. Check if you use the correct core-based licening E.E. if more than 40 logical processors are used.

3. Check if all configurations for SAP on SQL are met. SAP KBA 1744217 – MSSQL: Improving the database performance.

4. Leverage AlwaysOn or Database mirroring to prevent from Corruption Handling in SAP Databases.

5. Also check juergen’s white paper for the sake of completeness. SAP with Microsoft SQL Server 2008 and SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability – Part I: SAP Architecture and SQL Server Basic Configurations, Features Used, and Windows Configurations.

6. If thousands of IOPS is still required, consider to use Fusion IO to maximize IO performance. Compared to traditional SAN storages, MLC NAND flash is much cost effective.

7. Leverage RZ20 auto reactions to perform daily monitor like oldest open transactions, transaction log utilization, SAP DB backup status…etc. and read EWA to check additional configurations and system performance.

8. Leverage DBACockpit to run DBCC CHECKDB and monitor expensive SQL statements(SQL Statement History is supported after SAP Netweaver 7.0 EHP2).