1. Check the Data Cache Hit Ratio of your database!
One of the most important things to configure is the memory for SQL Server. If you observe a bad database performance and a low data cache hit ratio for a long time during database load, you should increase the memory for SQL Server. Low Data Cache Hit ratio values means that your server will be performing more I/O operations than it should be.
If you run your SQL Server on a Virtual Machine and your data cache Hit Ratio doesn’t increase overtime, despite of having enough memory to increase the data cache Hit Ratio, check the number of “Free Pages”. If the value is high , it may indicate that VM is using a dynamic memory feature, which is also known as “Memory Ballooning”. If the data cache Hit Ratio is low, there will be a lot of I/O operations and it will “mislead” you to think that your I/O subsystem is not working properly.
Check the following blog page of our Microsoft colleagues on MSDN and read the white paper to better understand:
ℹ Virtualization – Overcommitting memory and how to detect it within the VM
You can use the RAMMAP to analyze this:
ℹ Introduction to the new Sysinternals tool: RAMMap
2. Check the I/O performance!
For the most recent SAP NetWeaver systems, you can access the DBACockpit transaction and open the Performance folder and select “I/O Performance”. Hit the button “Current Values” and observe the ms/Read columns for the data files and the ms/Write columns for the transaction log.
You can get more details by double clicking the files to compare the data load/write and the ms/Read/Write.
If your database have been restarted recently and your data cache Hit Ratio is not high enough, you might notice high data loads (Reads/sec and ms/Read).
In normal operation, the read time of the data files shouldn’t be higher than 15ms and the write to the transaction log shouldn’t be higher than 3ms.
This kind of error is usually caused by hardware. Check and fix the I/O subsystem or approach another I/O subsystem vendor in order to get assistance to deliver an acceptable performance.
More information:
ℹ 987961 – FAQ: SQL Server I/O performance
Juergen’s whitepaper:
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
Ah, make sure you don’t have more than one transaction log file. It’s also a common mistake for DBAs starting with SQL Server to use other database vendor recommendations ???? .
3. Check whether locked pages can be used!
The major part of the main memory allocated by the SQL Server is the Data Cache. It’s important that the Data Cache is not paged out, otherwise the database will read the pages from the disk and not from the memory. With SQL Server 2005 and higher versions, it’s possible to disallow the operating system to page out pages allocated by the SQL Server.
Check the SAP Note ℹ 1134345 – Using locked pages for SQL Server
4. Check the database parameters
Bad or incorrect parametrization can impact the SQL Server I/O performance, specially for the memory settings. Check whether your SQL Server parameters are set according to bellow notes (select the note that fits your SQL Server version):
ℹ 879941 – Configuration Parameters for SQL Server 2005
ℹ 1237682 – Configuration Parameters for SQL Server 2008
ℹ 1702408 – Configuration Parameters for SQL Server 2012
ℹ 1986775 – Configuration Parameters for SQL Server 2014
5. Database Compression
As of SQL Server 2008 you can use row or page compression in SQL Server. Compressing the database objects can significantly decrease the amount of space occupied. With less space occupied, you’ll have less I/O operations. Database compression is default in all newly installed systems as of May 2011.
To compress your database you must first fulfill all the prerequisites from the following SAP Note:
ℹ 1488135 – Database compression for SQL Server
How to compress:
1. Open the SA38 transaction and run the report “MSSCOMPRESS”
2. Filter the tables that are not compressed:
3. If uncompressed objects are found, you should compress them. The above SAP Note 1488135 gives more background on how to do this.
In order to open the SAP Notes you must be logged into Service Marketplace.
Best Regards,
Luis Darui
New NetWeaver Information at SAP.com
Very Helpfull
