Tune your SQL Server SAP Database

Hi again,

In my last blog post I’ve already discussed a major topic for SQL Server databases, the common misconceptions.

Now I want to elaborate on another topic which I come across very frequently…

PERFORMANCE

Performance-tuning is a very complex domain – good and deep knowledge and understanding of how SQL Server works is required to tune.

For this reason it’s simply impossible to quickly deal with all facts and details you need to thorougly look into every single corner of your database that could be tuned.

Why am I still writing a blog post about it then?

Because I very often see SQL Server-based SAP systems where little effort could improve performance so much – and many of the tasks which I’ll talk about can even be carried out without a downtime. For this reason I always find it too bad if I look at a system and see that these basic tasks were not carried out.

I have the impression that some SAP recommendations for SQL Server databases which were communicated via SAP Notes within the last couple of years are still not so wellknown yet for some reason so I want to seize the opportunity and broadcast them 🙂 as these are general ones… they are not supposed to be followed in special cases but they should be followed in any case…

As for my last blog post I have again written a KBA which contains everything I want to share while I again post the initial version of it here for those of you who don’t have access to SAP Notes and KBAs.

SAP KBA 1744217 – Basic requirements to improve the performance of a SQL Server Database

Points 2, 3, 4, 5, 8  and 9 don’t even require a downtime so you can go ahead and apply them right away.

Point 3 will cause some load for large objects and should therefore be carried out when the overall system load is low and you’re able to monitor it. Small tables can be compressed quite quick and won’t cause considerable load. It’s a good idea to simply test it on a handful of tables with different sizes so you can see how long it takes in your system. You’ll be astonished how much space (and thereby indirectly I/O accesses) page-compression will save you.

(1) Kernel and Database Shared Library (DBSL) patchlevel

We frequently fix bugs or problems in the kernel and DBSL executables. Some of these are related with error messages but many are as well related with performance issues. For this reason it is important to make sure that your kernel and DBSL executables are updated to the most recent patchlevels provided by SAP on a regular basis. To to do, please follow SAP Note 19466.

(2) Statistics

If you follow point 7 SQL Server itself will take care of automatically updating statistics. Please do not schedule any additional statistics updates unless SAP explicitly recommends you to. Besides the automatic statistics update, please implement SAP Note 1558087.

(3) Database Compression

As of version SQL Server 2008 you can page or row compress database objects. We’ve seen many cases where compressing database objects could significantly decrease the amount of space occupied by the database. This in turn means that fewer I/O accesses are required to read and write data. For this reason SAP decided to use page compression by default in all newly installed systems as of May 2011.

If you are using SQL Server Version 2008 or higher and you fulfill all requirements from SAP Notes 1488135 and SAP Note 1459005 we strongly recommend to implement compression.

To check, if your database objects are already compressed please:

1. Goto transaction SE38 or SA38 
2. Start report MSSCOMPRESS 
3. Set the Data Compression Type and Index Compression Type Filter Options to Not compressed

4. Wait for the table list to be refreshed 
5. If uncompressed objects are found, follow SAP Note 1488135 to page-compress them.
   Note that you can choose between:
    
   • Always ONLINE 
   • ONLINE, retry OFFLINE 
   • Always OFFLINE

Please be aware that compressing an object will implicitly require to lock the object being compressed at certain times. If you use the online option SQL Server will use as few locks as possible. If you use the offline option, the object will be locked and will not be available for access until the compression has finished. For large objects compression can take a while for this reason ensure to use the first option if you want to avoid this. For tables which contain columns with data type image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml, an online compression is not possible with SQL Server Releases lower than SQL Server 2012. Please consider this when planning the compression of your database.

(4) Tempdb size

Especially in BW/BI systems, the tempdb is heavily used for certain tasks. Please make sure that it is correctly sized as described in SAP Note 1174635.

(5) Datafiles

To ensure that the data can be distributed over all existing data files, it is important that all data files provide free space at all times.

Please follow SAP Note 1238993 to ensure that your data files are configured correctly.

It’s also recommended that you have ~ 0.5 – 1 datafiles per CPU core (e.g. if your SQL Server can use 4 CPU cores, 2-4 datafiles make sense). If you are using a BW system it makes sense to have the same number of datafiles for the tempdb.

(6) Lock Pages in Memory Feature

As of SQL Server 2005 it is possible to disallow the operating system to page out pages allocated by SQL Server to the page file. As a major part of the main memory allocated by SQL Server is the Data Cache it is important that it is not being paged out. Otherwise it would in the end be read from disk (the page file) instead of from the main memory which decreases performance. Please follow SAP Note 1134345 to make sure that you are using the lock pages in memory feature.

(7) Parameters

Please make sure that the database parameter are set as recommended in SAP Notes:

• 327494 – SQL Server 2000 
• 879941 – SQL Server 2005 
• 1237682 – SQL Server 2008 
• 1702408 – SQL Server 2012

(8) sp_autostats

We recommend to switch on sp_autostats for all objects in the database in order to leave the task of updating statistics to SQL Server. For some tables we’ve experienced better performance if the automatic statitsics update is switched off. To correctly configure these for your database release, please follow SAP KBA 1649078.

(9) Disallow Page Level Locks

For several tables you need to disallow page level locks. Please follow SAP KBA 1648817 to properly configure this.

(10) Service Packs and Cumulative Updates

Make sure that you apply the most recent service pack and cumulative update for your SQL Server Release both, on server side as well as on all client sides.

Please see SAP Note 62988 and SAP KBA 1733195 for details.