Skip to Content

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

  msscompress.png

  1. Wait for the table list to be refreshed 
  2. 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:

 

(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.

To report this post you need to login first.

18 Comments

You must be Logged on to comment or reply to a post.

  1. Sascha Baier

    Very nice summary of easy steps to improve SQL Server performance.

    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

    I think because SQL server is running very smooth most time, some admins become a little inattentive.

    (0) 
  2. Tim Guest

    As a user of SAP Business One I found this very useful. Everyone is talking about HANA now so great to read some useful tips for those of us still on SQL Server!

    (0) 
  3. Edwin Sarmiento

    Great blog post. I’d like to add a few more in the list

    1. Grant the Perform Volume Maintenance Tasks permission to the SQL Server service account. This is to enable SQL Server feature called instant file initialization which skips zeroing out the data files (log files do not use this feature) whenever the autogrowth setting kicks in
    2. Make the autogrowth and file size settings of all data files equal and not in small increments. This will make sure that SQL Server balances out the writing of data on all of the data files and that the appropriate size will avoid external fragmentation on disk
    3. Pre-allocate the size for the log file because this is not affected by the instant file initialization. Also, transactions need to be written to the log file first before written to the data file. If there is not enough space in the log file to contain the transaction, it has to autogrow, thus, causing the transaction to wait.
    4. Monitor the database log files and reduce the number of virtual log files (VLFs). Extreme cases of high number of virtual log files affect inserts, updates and deletes on the database
    5. Remove any shrinking of your database in any of your maintenance plans. This includes disabling the autoshirnk property on the database as well as deleting the Shrink Database Task on your maintenance plans
    6. For tempdb, make sure that not only are the tempdb database files properly sized but also have the appropriate number of data files should you experience contention on the PFS and SGAM pages in tempdb.

    Hope this helps

    (0) 
  4. Aleks Allaire

    Hi Beate,

    Thank you for the great topic, it was helped me a lot when I was trying to squeeze some more performance from the SQL Server 🙂

    I would suggest to start with DBACOCKPIT, as it suggests some basic steps, ie show what’s cousing performance issues and than you topic, as a prolongation to more precize tuning.

    (0) 
  5. Rainer Buchwald

    Hi there,

    although this ist not really tuning the database (but since Beate had a look on our system) I would like to mention it anyways : in our case we found the reason for a very poor performing MSSQL based SAP system in wrongly aligned partitions, causing I/O bottlenecks on the database disk (on SAN storage).
    After re-aligning the partitions correctly, performance increased by factors (up to 10 times).
    For an explanation on partition/disk alignment see here:

    http://blogs.msdn.com/jimmymay/archive/2008/10/14/disk-partition-alignment-for-sql-server-slide-deck.aspx

    Partitions created on Windows 2008 and later will have a 1 MB partition offset and hence should not create a problem. But if you carry over partitions created on Win2003 this might be slowing you down considerably.

    Rainer


    (0) 
  6. Dieter Waelkens

    Hi Beate,

    thanks a lot for this comprehensive overview.

    You don’t mention anything on optimizing queries and indexes on MS SQL. I’ve been doing this for a while on Oracle with help of your colleagues.

    I don’t know whether you can direct me to a good blog post or document about MS SQL performance tuning on index level?

    Kind Regards,

    Dieter

    (0) 
  7. Matt Fraser

    Very useful and succinct blog, Beate.  Thank you.  When referenced in conjunction with Juergen Thomas’ whitepaper (which Eduardo Rezende linked in his comment above), it provides quite a comprehensive overview of the most critical factors.

    In one of your links to a Note, specifically to 1459005 in point 3 (Database Compression), you linked the SAP-internal URL (css.wdf.sap.corp), so for most of us we can’t directly follow the link.  We can find the Note easily through regular channels, however.

    Best regards,

    Matt

    (0) 
      1. Antoine Tran

        Hello Ramesh,

        According to SAP Note 1488135, SAP NetWeaver Application Server Java does not currently support SQL Server database compression.  Since Solution Manager is dual-stack, that’s why I was wondering if anyone has run MSSCOMPRESS.

        Did you have any issues while compressing Solution Manager?

        (0) 

Leave a Reply