1 2 3 Previous Next

SAP on SQL Server

37 Posts

Hi all.

I want to share with you, what do you do to extract info from SQL Server to MsExcel.


You have to follow this steps:


1.- create a New View in SQL.


vista en SQL2.JPG


You have to write a query (this is a single example):


SELECT docnum, doctotal, cardcode, cardname


WHERE docdate between getdate()-30 and getdate()


this query show you the invoices saved in last 30 days.


2.- Go to MsExcel --> Main Menu --> Data  --> other sources --> from SQL server

vista en SQL3.JPG


3.- clic on: From SQL Server

vista en SQL4.JPG


In this windows, you have to write a loca IP of your server of SQL. In my case is

You have to write a user (in my case the user is INFO), write a password.

clic in NEXT bottom


4- Select the database where you saved your view

and  (below) Select the view

vista en SQL5.JPG


5.- clic on finish bottom

vista en SQL6.JPG


6- clic on OK

vista en SQL7.JPG


7- The result of your SQL View, show on msExcel

vista en SQL8.JPG


8- Ready. You have a SAP information from your SQL Database on MsExcel.

9- If you want to refresh the information, you have to go to:


msExcel Main Menu --> Data --> update all

in the windows that apear you have to write

user (in my case the user is "info")



clic on OK.


And the info on msExcel from your SAP database will be updated.

vista en SQL10.JPG


Note: the user info and the password you have to create on SQL Server like a security login. (I show you in the image below)


vista en SQL11.jpg


I hope that it help you.

Best regards.


Saul Hernandez.

Hi all.

I want to share this scritp.


Whit this one you can make a SQL Job and scheduler it to execute the backup on a specific date and time.


I wish It help us.


Best regards.





USE [YourDataBase]
CREATE procedure [dbo].[SP_CrearBackupsDiario]
--This script create a backup of every database.
--This script use a cursor that go one by one making the backup.
--Saul Hernandez.
DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'c:\backups\'  
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  
       FETCH NEXT FROM db_cursor INTO @name  
CLOSE db_cursor  
DEALLOCATE db_cursor 

You probably have heard that SAP Product Support is creating Knowledge Base Articles with embedded videos.

Our Product Support team is now working on to create articles with videos to help customers and partners on how to perform specific tasks, troubleshoot, etc.

We added recently a specific wiki page in our SCN Wiki SAP on SQL Server to list these articles, in order to facilitate users who are navigating to find them. You can then navigate to 'KBA VIDEOS' section or you can access directly it from by clicking here.

In time we will be adding more articles with embedded videos.

As always, feedback is welcome. You can leave your comments, questions or suggestions about them in the wiki page.

Related Spaces:

SAP NetWeaver Application Server

SAP NetWeaver Administrator

SAP NetWeaver Technology Platform




Best Regards,

Luis Darui

SAP Product Support - SAP on SQL Server



I've been an SAP Basis Administrador and Database Administrator for almost eight years, and this guideline helps me to do the fastest Homogeneous System Copy's / System Refresh that I have known so far. Since I work in a somewhat demanding environment with tight schedules, sometimes the fastest solution is the best solution for my clients.


This is a guide that I'm working with my own personal experiences. All of the information can be found in the SAP Community Network. The respective links are in the guide as well as the respective appreciation for the people that have wasted time writing the posts. I just gathered the information.


Thank you for your time and effort.

Best regards


Rui Fontinha

MS SQL Server


Manual R/3 System Copy



     1- Important SAP Notes:


Note 1294762 - SCHEMA4SAP.VBS

Note 683447 - SAP Tools for MS SQL Server

Note 151603 - Copying an SQL Server database


     2 - Important: This method can be followed only for already installed systems (or) for System Refresh (Only works for Homogeneous System Copies).


     3 – Steps:


     Source System: 


    1. Stop SAP (R/3) System.
    2. Open the SQL Server Management Studio.
    3. Please note down the properties of your current DB.
    4. Take the DB offline.
    5. Detach the Database.
    6. Copy the Data (MDF, NDF) files and log (LDF) files to the Target system.


     Target System:


    1. Stop SAP (R/3) System.
    2. Open the SQL Server Management Studio.
    3. Please note down the properties of your current DB.
    4. Take the DB offline.
    5. Detach the Database.
    6. Rename source system  files <SID> with target <SID> in the Data (MDF,NDF) files and log (LDF) files as follow:




    1. Attach Database for SQL Server in the target system using SQL Server Management Studio.
    2. Convert the Database to the new target system <SID> and schema using the SCHEMA4SAP.VBS from note 1294762. You can also run the STM Tool provided by SAP for the schema conversion from note 683447.
    3. Please do the Homogeneous System Copy Post Procedures.


     Additional Comments:


     Re: Schema4SAP vs. SWPM->System Copy->Database instance?


     Link: https://scn.sap.com/thread/3386983


     Beate Grötschnig


     Schema4sap.vbs does not touch anything else but the database. If offers two options:


     Schema repair and schema move.


     A schema repair script will fix all logins on SQL Server level and the mapping to the corresponding users in the SID DB. If any users or logins are missing,      the script will create them for you. If any authorizations or privileges are not set correctly the script will set them for you.


     The schema move script will move everything in your database into a different schema, e.g. if you copy the DB from P11 to Q11 you can use the schema      move to move all objects in the DB to schema Q11.


     SWPM in contrast does not touch DB level only. If you use SWPM for a system copy it will:


     - Install the SAP software on the target machine, install common system files, do all the schema move and schema repair tasks for you, and so on.


     Schema4SAP.vbs within the course of a system copy is useful if the target system is already fully installed and the only task you want to carry out is      refresh the target system with a new copy of the source DB. Then you can simply restore the source DB and use schema4sap.vbs to move and repair the      schema.


     If you want to copy a system to a host with no SAP being installed yet, you'll have to carry out a full system copy with SWPM by following the system      copy guide.


     Regards, Beate.


Hi everyone!


I'm very proud to announce that as of November 1st, 2015 the new SCN Wiki SAP on SQL Server is available for community access in the following address:



Make sure to bookmark this in your favorite web browser!





What is SAP on SQL Server SCN Wiki?

SAP on SQL Server is a SCN Wiki space dedicated to provide SAP NetWeaver and MS SQL Server related content for the SAP community.

There is a lot of content about SQL Server around in MSDN blog SAP on Microsoft Platform and Books Online, so in the Wiki you'll find content that is really relevant and required for SAP NetWeaver.



Who maintains SAP on SQL Server SCN Wiki?

Currently, this SCN Wiki is maintained by the SAP support employees. Working closely with SAP Customers, they're able to get involved with many scenarios, questions and issues and then transform them into new content for the wiki.

What changes in the existing SCN space SAP on SQL Server?

Nothing is changed. The existing SCN space will continue existing, and the SCN discussion for SAP on SQL Server will still be used for questions, discussions, blog posts etc. The purpose of having a SCN Wiki for SAP on SQL Server is the ability to organize content under topics and create wiki documents to help SAP community, customers and partners to understand SAP NetWeaver and SQL Server (in addition to the existing content) and troubleshoot problems that affect their systems.

Where should I post my feedback for the existing wiki pages or content?

In each wiki page there is a place to write comments. We expect feedback and questions related to the wiki content there:


If I have an issue or am looking for help, where should I post?

Nothing changes, you should still use the SCN Discussion SAP on SQL Server for this:

SAP on SQL Server



Kind Regards,

Luis Darui

Hi everyone,


Months ago I worked with some SAP customers where they had some problems to startup their SAP Netweaver system after implementing AlwaysOn feature on SQL Server.


Basically the problem was strdbs.cmd script in yellow status and never starting the disp+work. This also affects the strdbsj2ee.cmd script during the startup of a JAVA stack.


After some troubleshooting it was discovered that the problem was related with the environment variable MSSQL_SERVER values. It contained the AlwaysOn Availability Group Listener and the parameter MultiSubnetFailover, like "MyAAGListener;MultiSubnetFailover=YES". Later it was discovered that the same would happen to customers that use DBM with FailoverPartner, because like AlwaysOn, you need to add connection sgring options to MSSQL_SERVER (and to the default profile also).


More recently, SQL Server named instances or default instances running on non-default port and AlwaysOn Availability Group Listener running on TCP/IP port other than 1433 (default). This happens because you need to add the TCP/IP port number after the Database Server hostname/Instance or AlwaysOn Availability Group Listener. Here are some examples:








The more recent versions of strdbs.cmd and strdbsj2ee.cmd introduced a new code that in the above scenarios causes strdbs.cmd/strdbsj2ee.cmd to hang in yellow state during the startup of SAP NetWeaver ABAP/JAVA systems.

The SAP Knowledge Base Article 2137130 - SAP startup hangs with SQL Server address the above scenarios and provide the solution and possible workaround for them.

Related spaces:

SAP NetWeaver Application Server

SAP NetWeaver Administrator

SAP NetWeaver Technology Platform

SAP on Microsoft Windows



In order to view the contents of the SAP Notes and KBAs, you will need to be logged into Service Marketplace.



I hope you enjoy this blog and any comments or suggestions are welcome.


Kind regards,

Luis Darui



During this week I've encountered some customers having difficult to upgrade their SAP NetWeaver JAVA stack on SQL Server. (It means that this post is specific for customers running SAP NetWeaver systems with JAVA stack on SQL Server.)


They were getting stuck in the PRE-PROCESSING phase with SUM SP13 PL6. Later it was identified this was an error in SUM SP13 PL6 causing SUM to look at wrong location for the JDBC driver.


This is fixed with SUM SP13 PL7. You can download it from http://service.sap.com/sltoolset (You need to be logged into Service Marketplace).


If you have downloaded previous version of SUM and is planning to upgrade your JAVA system running on SQL Server, we recommend you to download the version SP13 PL7 of SUM, or higher, otherwise you will get errors during the upgrade.


Please find more information on SAP Knowledge Base Article 2211685.


Small update:


The errors in question are:


[Error ]: Could not connect

[Error ]: com.sap.sql.log.OpenSQLException: Could not load class com.microsoft.sqlserver.jdbc.SQLServerDriver with class loader System

[Error ]: Could not establish open SQL connection. Exception com.sap.sql.log.OpenSQLException: Could not load class com.microsoft.sqlserver.jdbc.SQLServerDriver with class loader System


Related spaces:


Software Logistics

SAP NetWeaver Administrator

SAP NetWeaver Application Server


Best Regards,

Luis Darui

Moving the SAP DATA FILES from existed to newly added drive


If your SAP database or log files are running out of disk space, you have to add new disks to the file system.

Security Permissions :


Your Windows user account must be a member of the db_owner fixed server role in the SQL Server instance to detach DQS databases.

Your Windows user account must have CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permission to attach a database.

You must have the dqs_administrator role on the DQS_MAIN database to terminate any running activities or stop any running processes in DQS.


If an existing device cannot be extended, you have to add an entire new device to your system.

In this case, you first have to configure the new device as an NTFS partition. Then you have to move existing SAP database files have to the new device by detaching from and re-attaching the database to the SQL Server.


Case study - I have taken an instance SM1.


Procedure –


1. Shut down the instance.

2. Detach the Database.

3. Copy the Data files from Existed drive.

4. Paste the copied Data files to newly added drive.

5. Attach the Database.

6. Start the instance.


  • Instance has shut down.


  1. In the SQL Server Management Studio right-click the SAPSM1 database and choose Tasks -->Detach, The Detach Database dialog box appears.


Select the “Drop connections” check box, Choose OK to detach the database.


The SAP database is detached from the SQL Server and is no longer visible in the SQL Server Management Studio.

Copy the required SM1 Data files from Existing device,


Paste the SM1 Data files to newly added drive


In the SQL Server Management Studio, expand the server, right-click Databases and choose Attach. The Attach Database dialog box appears.



Specify the mdf file of the SAPSM1 database by choosing the Add button.

All the files belonging to the SAPSM1 database are listed under Original File Name.


We have to change the path of which one need to change

Now where we have located, it’s assigned that path only. Choose ok to attach the Data base.



Now able to view the SAP Instance in SQL server Management studio, and is again visible and accessible for the SQL Server. The SQL Server can now use the newly added drive.




Started the SAP instance, and Instance is up on running.

This blog post will cover some part of this topic which seems to be the most painful and unpredictable  situation: database corruption. They're mostly physical inconsistencies, associated with storage and raid controllers (physical devices), drivers, software controllers, etc.


Database corruptions can happen at any time due to many factors, but most of them caused by hardware issues. You should be prepared for it.


Of course this blog post is intended to help BASIS administrators or new DBAs starting with SQL Server. If you're already an experienced SQL Server DBA and is new to SAP, this blog has some useful information, but nothing that you haven't seen before I hope. If you're an "old sea dog" on SQL Server and SAP, then probably the content of this blog is irrelevant with redundant information that bothers you every day .



Identifying database corruptions:


I can say that 2 DBCC CHECKDB are enough, most of the times, to check the database consistency.  1 result against the database and a second result against either the same database on the same database server/instance after you clear the buffers (it may be only a transient corruption, but still a corruption!) or you restore the current database against a second server (preferable) or another instance on the same server and get the results to compare.


Make sure you read completely the SAP Note 142731 - DBCC checks of SQL Server.



Performing the first DBCC CHECKDB:


In the SQL Server Management Studio perform the command:

DBCC CHECKDB ('<SID>') with no_infomsgs;



Save the results for later analysis.

Re-validating the results:


When occurs a first access a database page, it's first stored in the data buffer of the SQL Server. Due to software bugs or defective RAM modules, a transient corruption may occur. When you invalidate the data cache, the database corruption can simply go away - but you shouldn't sit and forget about it! Always investigate the source of the corruption! For this it's necessary to have a second result to ensure that the database is really free of corruption or the corruption was transient. Or in the worst case, confirm a database corruption.




When you invalidate the data cache, the SQL Server will have to read all the data from the disks again. If you use a RAID system, some pages can be out of sync (RAID system mirror not identical). Due to optimization the reads are taken sometimes from the original and sometimes from the mirror. With this, the results of the DBCC CHECKDB can differ each time you execute it after you clear the data cache.



I personally suggest to backup / restore your database into another server/instance. Preferably, to another hardware, to eliminate a complete investigation over a system that is suspect.

  1. Reset the data cache:

    To clear the buffer cache, perform the command:



    Note: This command invalidate the buffer cache and if you perform it in a production system, the database will have to read all the data from the disk again. The users will observe bad performance and even TIME OUTS when running programs and transactions. If it's not feasible for you to invalidate the buffer cache, then backup and restore the database into another server/instance!


  2. Backup the database and restore it into another server/instance.

    This option will eliminate some questions about the effectiveness of the analysis and efforts in order to fix/restore your database/system. I won't enter in details on how to backup / restore your database.

  3. Repeat the DBCC CHECKDB, save the results to analyze it.



An inconsistency has been detected. What now?


Well it's time to identify them in the results. Depending on the following message, you're able to repair the tables:

CHECKDB found 0 allocation errors and 8 consistency errors in database 'NW1'.

repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (NW1).

The above results indicates that the corruption is not severe and you can fix without data loss, which is the opposite of the following error message:

CHECKDB found 10 allocation errors and 1739 consistency errors in database 'NW1'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (NW1).

It indicates that at least one table is severely corrupted and cannot be repaired.

I won't go in deep on methods that can help you to extract the data from a severely damaged table because it's not the goal of this blog post. You may also keep in mind that depending on the table that is affected you can also rebuild the table with data from other tables (it depends on the component that is responsible for the table involved) or it's a table that doesn't store sensitive data and can simply be truncated or dropped and recreated. But for this I strongly suggest you to read the entire blog. I'll approach later with some suggestions if your minimum repair level is "repair_allow_data_loss" - also known as RADL.


For each table that is listed in the CHECKDB results is necessary to perform another DBCC CHECKTABLE to check the level of the damage to each table:




For the tables listed with "repair_rebuild" or "repair_fast" as the minimum repair level, you can repair the tables with the DBCC CHECKTABLE and REPAIR_REBUILD. However, you have to set the database to SINGLE_USER first:



After completing, you can repair each table:





After completing the repairs, perform another DBCC CHECKDB whether your database is now clear. If the result is positive, set the database back to MULTI_USER:




Before releasing the system back online to the users, take a full backup for safety



The minimum repair level is "repair_allow_data_loss". What to do?


If the table was marked as the minimum repair level as "repair_allow_data_loss" it means that this corruption cannot be fixed. In some cases (most of them), the database catalog is still consistent and the affected objects are application tables. Depending on which data was affected, maybe it can be (or in most of the cases, cannot be) reconstructed from redundant application data.  You can identify the application areas that are responsible for those tables and try to approach them with the list of the affected tables and see if they can recover. It's explained on SAP Note 1597910.


It's not guaranteed that it's possible to recreate a database table with the complete data it had before. But you can give a try.


For all instances, in case you have a database object corruption with "repair_allow_data_loss", the best solution is to rely on your backups. Restore your database into another server. Start with the latest good full backup and start restoring the transaction log backups until you recover your system completely.


If a restore is not possible due to retention policy of your backups or your backups are also corrupted/not consistent, then the best approach is to contact Microsoft directly or a third party. SAP urges to customers to maintain a minimal (and efficient) backup strategy and even provide functions which assist the administrators in the areas of backup and database consistency checks. Disaster recovery solutions and efficient backup strategies have proved to be more efficient than to try to remedy those situations.


To understand the SAP Goals and Policy with the handling of the database corruptions on SQL Server, read carefully and completely the SAP Note 1597910.



Action required! Investigate and eliminate the source of corruptions!


All work done so far to fix/restore your database may be jeopardized if you simply ignore the fact that something went (and probably still) wrong in your database server. Investigate and eliminate the source of the corruption is as important as fixing/restoring the database. Even that the corruption can be caused by software or a bug, you can almost always find the root cause of the corruption in the hardware or driver problems.



In order to prevent database corruptions, what can I do?


You can't prevent database corruptions. It's something that is beyond your control. It can happens even if follow all the best practices. The difference is the impact / downtime and the risk that you lose your data is even lesser or 0 if you follow all the recommendations from SAP and Microsoft.


You can work proactively to:

  • Keep your software updated: regularly apply patches to your Windows and SQL Server;
  • Keep your drivers tested and updated.
  • An efficient backup strategy - database corruptions do not spread through transaction logs (unless the storage where you keep your backups is also damaged). Read the SAP Note 1297986 for suggestions to backup/restore strategies for SQL Server.  Testing the consistency of the backups is also part of the backup strategy.

  • Regularly perform DBCC checks to make sure your database is free of corruption.
  • As of SQL Server 2012 you can benefit from AlwaysOn. As there is no shared storage between the primary and secondary replica, if the primary replica is corrupted, there is a chance that the secondary is not. Consider implementing an efficient disaster recovery strategy, it can save a lot of time and your heart may live longer.
  • Do not neglect a backup strategy only because you have a high availability like Windows Failover Cluster or AlwaysOn. Backup is a must and you shouldn't even consider think about giving it up!
  • DO NEVER RUN DBCC WITH REPAIR_ALLOW_DATA_LOSS! Unless it's explicitly asked to you from SAP. And I haven't asked!

Best Regards,

Luis Darui

Support Engineer, SAP NetWeaver DB/OS Platforms
SAP Labs Latin America.

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

Are you facing disconnections (sql/database error 0, 11, 121, -1 or 10054) with your SAP system?


The following points might help you to solve this issue!


1. Refer to Dale's presentation: Analyzing Network Errors on SQL Server.


2. Follow all the steps in the SAP KBA 1478133.


3. Some of the following points are common errors that are prone to cause this issue:

  • Using a ODBC client instead the SQL Native Client;
  • Using older versions of the SNAC (always use at least the same version of your database instance);
  • Older versions of DBSL and SAP Kernel;
  • Running old versions of SQL Server (always run the latest Service Pack and the latest Security update, hotfixes and cumulative updates provided by Microsoft - see Note 62988 and KBA 1733195).
  • Hardware problems, outdated NIC drivers, Server Memory, etc (hardware, physical network, etc);


4. You can perform the following steps to try to avoid those errors:

  1. On the server side, increase the registry setting HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters.
    TcpMaxDataRetransmissions to 10 {up from the default setting of 5}.
  2. On the client side, both the "Keep Alive" and "Keep Alive interval" can be safely increased via the SQL Server Configuration Manager tool.
    The numbers are in milliseconds and the defaults are 30000 and 1000 respectively.
    Increase "Keep Alive" to 90000 and "Keep Alive interval" to 5000.


5. For further information, double check Notes 1794178 and 1593183.


6. Is your SAP system running on VMware? If yes, also check:


Related Spaces:

In order to view the contents of the SAP Notes and KBAs, you will need to be logged into Service Marketplace.

Hope you like this blog and any comments/suggestions are welcome!


Best regards,

Eduardo Rezende

Performance issue becomes a real problem for some SAP systems. Especially if your DB size is big, there are many customer codes.


In one of our customer's system we had a performance issue. We had a meeting with all parts involved in SAP Systems. HW vendor, Network group, Business, System Admins and BASIS. The result of the meeting was an action list.

Beside all architectural actions, one of the action was finding fragmanted indexes and applying defragmantation and it was my duty to do the needful.

This blog is output of this action and about index defragmantation on SQL Server.


I will try to explain the topic in the following order.

  1. List the indexes that will be possibly defragmanted and effecting the system performance
  2. Analyze the indexes
  3. Defragmant the indexes
  4. Run new Statistics for the indexes
  5. Results and Chart
  6. Pros and Cons of defragmanting


And these are the details of the steps:


1. List the Indexes

When we were listing the fragmanted indexes our scope was on the indexes that are used in expensive SQL Statements. We also checked the indexes of the tables that are accessed sequentially on process overview.


Go to DBACOCKPIT --> Performance --> SQL Statements


Double Click on one of the SQL Statements.

Go to Explain Tree Tab to see the index names used in the SQL Statements.


For other indexes we use SM66 Global Work Process Overview.

Check the sequential reads and related tables.



After taking the list of related tables you go to SE11 and take the index names.



2. Analyze the Index

Go to DBACOCKPIT --> Space --> Single Table Analysis

Enter the related table name and select the index name.

Select DBCC showcontig  button.



     Run one of the options Fast, All Levels and Normal.


Yo can also use SQL SQL Server Management Studio to find DBCC Showcontig results using similar Queries like below.


DBCC SHOWCONTIG allows you to measure fragmentation and page density levels on indexes.


The result is analyzed according to the document "Microsoft SQL Server 2000 Index Defragmentation Best Practices" mentioned in SAP Note 159316 - Reorganizing tables on SQL Server. You can check the "Analyzing Output from DBCC SHOWCONTIG" section in the document for details.

The document says that  "High values for logical scan fragmentation can lead to degraded performance of index scans. Consider defragmenting indexes with 20 percent or more logical fragmentation."

It states also that "indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)."


So we selected all indexes that has logical scan fragmantation more than 20% and more pages.


3. Defragmant the indexes


We used SQL Server Management Studio Query editor to run scripts for defragmanting.

Examples:   DBCC INDEXDEFRAG (<SID>, "<sid>.EKPO", [EKPO~0])

                    DBCC INDEXDEFRAG (<SID>, "<sid>.RFBLG", [RFBLG~0])


Duration of defragmantation changes according to the index sizes and Logical Scan Fragmentation percentage. As you see below screen it took 3 hours. Some lasts more than a day.


This process also generates logs. So you must monitor your log space during the process. Otherwise your system can stop due to lack of log space.


DBCC INDEXDEFRAG reports the estimated percentage completed every five minutes.


DBCC INDEXDEFRAG can be stopped at any point in the process, and any completed work is retained.



4- Update Statistics

     We updated statistics of the related tables after defragmantation to obtain the changed statistics.


5- Results and Graph

    When we rerun DBCC Showcontig command after defragmantation we can easily see the decrease in Logical Scan Fragmentation.

     The value became an acceptable value as it is seen in below picture.


     As a result, we took the I/O Stall read and write times from DBACOCKPIT --> Performace --> History --> Database Collector Data Time Series table.


     We exported data to an excel sheet and took the averages of all read and write times for the whole period. (Red and orange lines in below chart).

     From the chart below it can easily be seen that I/O values decreased significantly. So it worth defragmenting.


6- Pros and Cons of defragmanting

  • First of all, SAP explicitly recommends not to reorganize or rebuild any database objects on a regular basis. You should not even reorganize or rebuild objects as an attempt to solve a performance problem as long as it is not evident that fragmentation is the root cause of the problem (which it hardly ever is).


  • You can do this process for particular indexes that are fragmented with high percentages, has alot off pages and used in expensive sql statements.


  • It is time consuming job


  • Generates lots of logs, monitoring is must


  • Defragmentation can be done online. Instead DBREINDEX can be used for offline solution.



159316 - Reorganizing tables on SQL Server

1660220 - Microsoft SQL Server: Common misconceptions

Microsoft SQL Server 2000 Index Defragmentation Best Practices

You're back from your coffee break, and your SQL Server 2012 installation has completed successfully. What next? You're ready to install your SAP application now, right?


Not quite yet. There are a few configuration and housekeeping steps you should take before going further. Note: many of these steps can also be handled within an ABAP system via DBACOCKPIT, or from Solution Manager, but I'm an old hand and still find some of this considerably easier using the SQL Server native tools.


SQL Server Post-Install Configuration (Pre-SAP)



You should still have your Upgrade to and Installation of SQL Server 2012 in an SAP Environment guide open to chapter 4.2 (Installing the SQL Server 2012 Database Server Software Manually). You are now at Step 6 in the guide. In most circumstances the TCP/IP protocol will be enabled by default, so you should not have to change anything. However, it is worthwhile to follow this step to ensure this is the case, and this is a good time to pin the SQL Server Configuration Manager to your taskbar and/or start menu.


Database Mail

First up, let's give SQL Server the ability to email you when it's in trouble.


  • Open the SQL Server Management Studio tool from your Start menu (and pin it to your taskbar and start menu as well), and logon using Windows authentication.
  • Expand your server, then expand the Management folder.
  • Right-click on Database Mail, then select Configure Database Mail.
    • Click Next, then on the Select Configuration Task screen, choose the first (default) option for Set up Database mail... Click Next.
    • A popup warns that the Database Mail feature is not available and asks if you'd like to enable it. Click Yes.
    • On the New Profile screen, give your mail profile a name. I usually choose the SID of the SAP system I'm about to install.
    • Under SMTP accounts click Add.
    • The New Database Mail Account window pops up. Fill in the following fields (those not specified are optional and can be left blank or at their default):
      • Account name: You can again use the SID just as you did for the profile name.
      • Outgoing Mail Server (SMTP)
        • E-mail address: Here I usually enter something like "SID@server.domain.com", using your SAP system's SID, your SQL Server host name as the server, and your organization's domain (or that of your mail server) for "domain.com".
        • Server name: This is the host name of your email server, not your SQL Server. Enter it fully-qualified, i.e. "mail.domain.com".
      • The other fields on this screen should only be changed if your mail server requires it, or to fit your organization's email connection standards. Most often the defaults work fine.
    • Click OK.
    • You are now back on the New Profile screen, and your SMTP account shows up in the list. Click Next.
    • On the Manage Profile Security screen, on the Public Profiles tab, your SID profile shows up in the list. Select the checkbox next to it under Public, and switch the property under Default Profile to Yes. Click Next.
    • On the Configure System Parameters screen, review the defaults and click Next.
    • On the Complete the Wizard screen, review the actions and click Finish.
    • If all goes well, you should see a "Success" message. Click Close.
  • Back in the Management Studio's Object Explorer, right-click Database Mail again, and choose Send Test E-Mail.
    • Your newly-created Database Mail Profile should be shown. In the To: field enter your own email address, then click Send Test E-Mail.
    • In a moment you should receive an email in your inbox. If it worked, click OK on the test popup window.


SQL Agent

While humming the tune SQL Agent Man to yourself, refer back to your Upgrade to and Installation of SQL Server 2012 in an SAP Environment guide and open it to chapter 5, Setting the SQL Server Agent Configuration. Keep that handy.


  • In SQL Server Management Studio, expand SQL Server Agent.
    • Right-click Operators and select New Operator.
      • On the General page, enter the name of a system administrator or group to receive alerts from this SQL Server. If it's not a production system, this might be just yourself. Make sure the Enabled checkbox is checked.
      • Under Notification options, in the E-mail name field, enter the email address for the system administrator or group distribution list (or yourself) as appropriate. Click OK.
    • Right-click SQL Server Agent and select Properties.
      • Switch to the Alert System page.
        • Under Mail session select the checkbox for Enable mail profile.
          • In the Mail profile field, select the SID mail profile you configured earlier. Don't worry if the Test button is greyed out.
        • Under Fail-safe operator select the checkbox for Enable fail-safe operator.
          • The operator you created earlier should already be defaulted; if not, select it.
          • Next to Notify using: select the checkbox for E-mail.
      • Switch to the History page.
        • Per the instructions from the installation guide, set the following parameters:
          • Maximum job history log size (in rows): 6000
          • Maximum job history rows per job: 500
      • Click OK.



Now it's time to configure some alert notifications for general error conditions that hopefully will not occur.


  • In SQL Server Management Studio, expand SQL Server Agent (if not still expanded from the previous section).
  • Right-click Alerts and select New Alert.
    • On the General page, give the alert a name of 11 - Specified Database Object Not Found.
      • Under Event alert definition, in the Severity field, select severity 011.
    • Switch to the Response page.
      • Select Notify operators and the checkbox under E-mail next to your operator's name.
    • Switch to the Options page.
      • Under Include alert error text in: select the checkbox for E-mail.
      • Set Delay between responses to 30 minutes.
    • Click OK.
  • Repeat this process, creating new alerts for severity conditions 013 through 025. I like to name the alert using the severity number and name so it is logically ordered and descriptive when looking at them in the Management Studio.


When you've finished, your list of alerts should look like this:


sql alerts.png


Later, after you've installed your SAP system, you may want to add additional database-specific alerts (for instance, Transaction Log Full) which can, in addition to notifying you by email, kick off a process, such as a log backup.


Configuration Parameters

Now it's time to open Note 1702408: Configuration Parameters for SQL Server 2012.


It is not necessary at this time to set any trace flags, as SWPM will set the appropriate ones automatically during the installation of your SAP system (though you will want to review them afterwards to ensure this happened correctly).


The Note walks you through setting parameters via SQL Query commands. However, many of them can also be set using the Management Studio's GUI options, and it's not a bad idea to familiarize yourself with the tool this way.


  • In SQL Server Management Studio, right-click on your server name (top of the list in Object Explorer) and select Properties.
  • On the General page, make a note of the number (in MB) shown for Memory. This should match the total physical memory installed in your server.
  • Switch to the Memory page.
    • Here the fields Minimum server memory (in MB) and Maximum server memory (in MB) equate to the configure options mentioned in the Note min server memory (MB) and max server memory (MB). How much you set these values to depends on your particular landscape and how much else is also on this server, and the Note gives some general guidelines for making a decision. No matter what you choose, you are going to set both parameters to the same value, so SQL Server will use a fixed amount of memory.
    • If this is a dedicated database server, subtract between 1.5 GB and 6 GB from your total physical memory (as described), convert the remainder into MB, and enter this value in the fields.
    • If this is a central instance, i.e. you will be running both SQL Server and an SAP application instance on this box, then divide your total physical memory by three and enter that number into the fields (i.e., give SQL Server 1/3 of the physical memory). Later, after installing and running your SAP application for a while, you may find you need to adjust this number, but this is a good starting point.
    • If you have a previously installed system with the same amount of physical memory and the same release of NetWeaver application installed, you may find that the installer set a different default for the SQL Server memory. If so, and that system is running well, you may want to use that number again here. For instance, on a machine with 48 GB of physical memory and one NetWeaver AS Java 7.4 instance co-hosted with SQL Server 2012, the number might be 19656.
  • Switch to the Database Settings page.
    • Under Backup and restore, select the checkbox for Compress backup.
  • Switch to the Advanced page.
    • Under Parallelism, set Max Degree of Parallelism to 1.
      • There are exceptions to this rule, generally for BW systems, but in almost all other cases this will give better performance.
  • Click OK.


Now again right-click on your server name and this time select New Query.


  • In the query window, type:
    • sp_configure 'show advanced options', 1
    • reconfigure with override
  • Click on Execute (or hit F5 on your keyboard).
  • Edit the query command so that 'show advanced options' becomes 'xp_cmdshell'. Now the query should look like:
    • sp_configure 'xp_cmdshell', 1
    • reconfigure with override
  • Execute (or hit F5).
  • If you want to confirm all the settings you have made, delete all but the command sp_configure (or highlight just that part) and execute. You will get a list of all the parameters, their minimum and maximum values, and their configured and runtime settings.
  • Close the query window. Do not save changes.


The rest of the parameters described in the Note only apply after installing an ABAP system, so you are done with it for now.


Lock Pages in Memory

There is one additional, important setting that is not described in Note 1702408. It is, however, described in Note 1134345: Using locked pages for SQL Server. This setting cannot be made using the Management Studio, and it is the first reason why I recommended during installation to use an actual service account and not a builtin account (such as Local Service).


  • In Windows, click on Start and select Run.
  • In the Run dialog box, type gpedit.msc in the Open field, and click OK.
  • This will open the Local Group Policy Editor. On the left, expand Local Computer Policy -> Computer Configuration -> Windows Settings -> Security Settings -> Local Policies.
  • Select User Rights Assignment. A list of related policies appears on the right.
  • Find and double-click Lock pages in memory.
  • Click Add User or Group.
  • Type in the name of the domain user account used for your SQL Server service, then click Check Names.
  • Confirm that the correct account is returned, then click OK.
  • Confirm the account appears in the list, and click OK again.
  • The account should now show up under Security Setting next to Lock pages in memory.
  • Close the Local Group Policy Editor.


The setting requires a restart of SQL Server to take effect. Close the SQL Server Management Studio (if it's still open) and start the SQL Server Configuration Manager. On the left, select SQL Server Services, then on the right right-click SQL Server (MSSQLSERVER) and select Restart.


This setting will prevent SQL Server's buffer memory from being paged to disk, which can be a performance-reducing event. The Note gives more detail about this setting, and also some considerations around its use. However, in general, for the majority of installations, it will be appropriate to set this.


You can confirm the setting is operational after the restart by examining the most recent SQL Server Log in the Management Studio. Expand Management -> SQL Server Logs and double-click the one marked Current. In the log, near the beginning, look for the line Using locked pages in the memory manager.


System Maintenance Plans

It's time to get a backup of the system databases and set up some housekeeping jobs.


System DB Backup

When installing SQL Server, you set aside a dedicated drive for database backups, and created a folder there called Backup. If you didn't, now is a good time to do so. Under the Backup folder, create another folder called System. Eventually you will have others here as well, but this will do for now.


Back in the SQL Server Management Studio, expand the Management folder under your server. Right-click Maintenance Plans and select Maintenance Plan Wizard.


  • On the starting page, click Next.
  • On the Select Plan Properties page, give the plan a name: System DB Backup.
    • Under Schedule, click Change.
      • On the New Job Schedule page, set the frequency and time of the backup job as appropriate. For the system databases (Master, Model, and MSDB), I recommend daily backups.
      • Under Frequency, for Occurs: select Daily.
      • Under Daily frequency, for Occurs once at: set an appropriate time (e.g. 12:30:00 AM).
      • Click OK.
    • Back on the Select Plan Properties page, click Next.
  • On the Select Maintenance Tasks page, select the checkboxes for Back Up Database (Full) and Maintenance Cleanup Task. Click Next.
  • On the Select Maintenance Task Order page, use the Move Down or Move Up buttons so that Maintenance Cleanup Task is at the top of the list. Click Next.
  • On the Define Maintenance Cleanup Task page:
    • Under Search folder and delete files based on an extension, search for and select your \Backup\System folder.
    • In the File extension: field type bak (no periods).
    • Select the checkbox for Include first-level subfolders.
    • Under File age: set an appropriate amount of time you wish these backups to remain on disk (at least long enough for network filesystem backups to pick them up, and also long enough for any potential need for them to be covered). Two or three weeks is probably sufficient in most cases.
    • Click Next.
  • On the Define Back Up Database (Full) Task page:
    • For Database(s): select System databases.
    • Under Create a backup file for every database, select the checkbox for Create a Sub-directory for each database.
    • Set the Folder: to your \Backup\System folder.
    • Select the checkbox for Verify backup integrity.
    • Click Next.
  • On the Select Report Options page, click Next.
  • On the Complete the Wizard page, click Finish, then after the success notification, click Close.


Clean Up History

Now use the wizard to create a second maintenance plan.


  • Name the plan Clean Up History.
  • Give this plan a schedule frequency of monthly. A possible option is to occur on the first Saturday of each month at 2:00am.
  • Select the tasks Check Database Integrity, Update Statistics, Clean Up History, and Maintenance Cleanup Task.
  • There is no need to change the order of operation.
  • For Define Database Check Integrity Task, choose All databases.
    • Note, for ABAP systems it is often recommended to schedule this check via DBACOCKPIT, and it may be scheduled automatically on installation. In this circumstance, you may choose to restrict this check to your system databases.
  • For Define Update Statistics Task, again choose All databases and leave the other options at default.
    • Again, note, at least for ABAP systems it is generally recommended to not schedule this job, in part due to the large database sizes involved, and in part because it may not be necessary (autostats should handle this). So, you may choose in this circumstance to restrict this to system databases.
  • For Define History Cleanup Task, set an appropriate amount of time to retain the job history data. The default is 4 weeks, but I feel this is too short. I recommend 2-6 months.
  • For Define Maintenance Cleanup Task:
    • Set Delete files of the following type: to Maintenance Plan text reports.
    • Set Search folder and delete files based on an extension to the folder in your SQL Server installation where job log reports are kept. By default this is \Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log.
    • Set an appropriate file age. I suggest 2 months.


Maintenance Plan Notifications

The wizard does not include an option for setting email alerts in the event of job failure. However, you can set this on the jobs themselves.


  • Expand SQL Server Agent and double-click Job Activity Monitor.
  • On each of your two new jobs (Clean Up History.Subplan_1 and System DB Backup.Subplan_1), double-click to open the job properties.
  • Select the Notifications page.
  • Select the checkbox for E-mail and select your previously defined operator.
  • Click OK.


Now you will receive an email in the event one of these jobs fails for any reason.


Get a Backup

You're done with initial configuration of SQL Server and ready to proceed with installing SAP. Before you do so, I recommend that you get a backup of your configuration. In the Management Studio, expand Management -> Maintenance Plans and right-click System DB Backup. Select Execute.


Let me know if you find any errors in this guide, or if you have alternate suggestions.

SQL Server 2012 is relatively easy to install, and the installation guide from SAP is very clear. However, there are a few guidelines from my own experience that may be of benefit to others performing this installation for the first time. The following example assumes an installation on Windows Server 2012 R2.


SQL Server 2012



Obtaining Installation Guides, Notes, and Software


Critical Guides and Notes

The main installation guide is Upgrade to and Installation of SQL Server 2012 in an SAP Environment. It can be downloaded from the Service Marketplace (logon required) at http://service.sap.com/instguides -> Database Upgrades -> MS SQL Server.


The two most important SAP Notes (logon required) are:

  • 1702408: Configuration Parameters for SQL Server 2012
  • 1134345: Using locked pages for SQL Server


A list of other useful Notes, documents, and websites for reference appears at the end of this blog. Many of the performance and configuration Notes, such as for setting traceflags, are covered in Note 1702408. Others refer to settings specific to tables or ABAP databases and thus are not required for the initial installation.



If you licensed SQL Server through SAP, then you can download the software from the Service Marketplace (login required) at http://support.sap.com/software/databases.html -> MS SQL Server -> Database -> MS SQL SERVER X86_64 -> MS SQL SERVER 2012/X86_64 -> Installation -> Microsoft SQL Server. Download all 8 files that collectively make up DVD material number 51047515. Run the self-extracting executable that makes up the first file, and it will automatically combine with the other 7 files to create a folder structure mirroring an installation DVD.


Service Pack and Cumulative Update

SAP's distribution comes with Service Pack 1 Cumulative Update 7. However, Microsoft has released both a new Service Pack and new Cumulative Updates for both Service Packs 1 and 2. Unfortunately, at this time Microsoft's support website for downloads does not make it easy to find the latest SP and CU, nor even to know which are the latest. Therefore I recommend checking the Microsoft SQL Server Version List blog found at http://sqlserverbuilds.blogspot.com. Scroll down to the SQL Server 2012 section and identify the most recent Service Pack and the most recent Cumulative Update for that Service Pack. At this time, that is SP2 and CU4. The link to the Service Pack will take you directly to a Microsoft download page. The link to the Cumulative Update will take you to a Microsoft page describing the CU. From there, you must supply your email address, and a few minutes later you will receive an automated email with a link to a temporary download location. Unzip both archives.


Preliminary Steps


Service Account

I recommend using a domain account as the service account for SQL Server (the installation guide recommends using Local System). This will be of use when setting configuration parameters such as using locked pages in memory. Create (or request) your service account now so that it is ready during the setup. The account should be configured according your organization's policies for service (non-interactive) accounts, i.e. the password should never expire and the account should not have a logon script associated with it. It does not require any special domain privileges beyond this.


.NET Framework 3.5

The SQL Server Setup program requires .NET Framework 3.5 SP1. This feature is not typically installed by default with Windows 2012 R2, and it is not included with SQL Server. It is available, however, on the Windows 2012 installation media. Assuming the media is available to you, you can install or enable it by launching Server Manager (by default, this tool launches upon logon to the server console, which I personally find annoying and usually turn off), then selecting Manage -> Add Roles and Features.


server manager add roles features.png


On the page Before you begin click Next.

On the page Select installation type choose Role-based or feature-based installation and click Next.

On the page Select destination server select your server and click Next.

On the page Server Roles make no changes and click Next.

On the page Features select .NET Framework 3.5 Features and its subcomponent .NET Framework 3.5 (includes .NET 2.0 and 3.0).


server manager select features.png

If the feature is already enabled (the checkboxes are already selected), then click Cancel. Otherwise, click Next and follow the prompts to complete the installation.


Windows Updates

Apply the latest after enabling .NET Framework 3.5 but before proceeding further. A reboot may be required.



This is a good time to set your desired pagefile size for your SAP installation. The size chosen will depend on many factors and is beyond the scope of this blog, but as a rule of thumb it should be a minimum of 20 GB. If your server has a mix of local and SAN storage, the general recommendation is to locate the pagefile on a local disk (which will likely be your C: drive).


Open Control Panel and select System and Security -> System -> Advanced system settings -> Performance: Settings -> Advanced -> Virtual memory: Change. Deselect Automatically manage paging file size for all drives. Choose the appropriate drive and set Custom size with Initial size and Maximum size at the same value. Click Set then OK. (Reboot usually required).




Windows Explorer Preferences

This is optional, but to avoid annoyance I prefer to change a couple of these settings. In Windows Explorer select View -> Options -> Change folder and search options. On the General tab select Automatically expand to current folder. On the View tab deselect Hide extensions for known file types. This will make your life easier.


Drive and Folder Structure

My recommendation is to have a separate drive or volume for:

  • Backup
  • Operating System / Pagefile
  • SQL Server
  • SAP (/usr/sap)
  • TempDB
  • Transaction Log
  • Database (spread across 4, 8, or 16 drives, depending on number of logical processors)


On the Backup drive, create a Backup folder, and on the TempDB drive, create a TempDB folder.




Set Up Source Files

Copy the SQL Server installation media you downloaded from SAP to a temporary location on your server with at least 7 or 8 GB of free space (e.g. C:\source\SQLServer). Expand \Source\SQLServer\x86-x64\ServicePacks and create a subfolder called SPxCUy (where x and y represent the Service Pack and Cumulative Update numbers you downloaded, e.g. SP2CU4). Copy the SP and CU executables you downloaded from Microsoft into this subfolder. If there are older SP and CU folders present, you may optionally delete them.


Start Setup With Integrated SP and CU

Click the Windows Start menu icon, then the 'down-arrow' to get the Apps menu. From the apps, choose Run. In the Run dialog box select Browse and navigate to \source\SQLServer\x86-x64\EnterpriseEdition\setup.exe and open it. Back in the Run dialog, edit the 'Open' field to add the following command-line switches so that your command looks like this:


     C:\source\SQLServer\x86-x64\EnterpriseEdition\setup.exe /Action=Install /UpdateSource="C:\source\SQLServer\x86-x64\ServicePacks\SP2CU4"


This will eliminate the need to separately apply patches after the installation, as they will be included.



In general, from this point the setup will follow the installation guide as described in chapter 4.2 "Installing the SQL Server 2012 Database Server Software Manually." I will only highlight where I deviate from the instructions given in the guide.


Product Updates

After the License Terms page, you will see a Product Updates page where you can confirm that your Service Pack and Cumulative Update were correctly selected.


setup product updates.png


Feature Selection

In addition to the features listed in the installation guide, I recommend selecting Integration Services (under Shared Features). This is not required to run SAP, but if you choose to setup backups and other housekeeping jobs via Maintenance Plans in the SQL Server Management Studio, then you will need this component. If you choose to setup your backups and housekeeping via DBACOCKPIT, then this is not required.


I strongly recommend changing the default drives suggested by the setup program. By default, setup will recommend installing on your C: drive. I recommend having a dedicated drive for SQL Server, e.g. D:, and thus changing the Shared feature directory and Shared feature directory (x86) as shown below.


setup feature selection directories.png


Clicking the ellipsis after each entry box will give you an opportunity to create these folders directly.


Instance Configuration

Again, I strongly recommend changing the Instance root directory to be the same as the Shared feature directory you just set. Otherwise, leave the selection at Default instance and do not change the Instance ID.


setup instance configuration directory.png


Server Configuration

I do not recommend using Local System accounts as suggested in the guide. I recommend using a domain account for the SQL Server Agent and SQL Server Database Engine.


setup server configuration service accounts.png


After entering the service account ID and password, select the Collation tab to customize the server collation to SQL_Latin1_General_CP850_BIN2 as described in the guide.


Database Engine Configuration

After setting the Server Configuration with Authentication Mode and specified SQL Server administrators (i.e., set BUILTIN\Administrators for this), switch to the Data Directories tab. Change the Temp DB directory to the TempDB folder you created earlier, and the Backup directory to the Backup folder you created. There is no need to change the User database or log directories, as you will be creating multiple directories across multiple drives later when you install your SAP system.


After a few more screens, the installation will begin. It will take several minutes or so, so this is a good time for coffee. Later, when you come back, we'll talk about initial (pre-SAP installation) configuration of SQL Server (in another blog).


Additional Resources


SAP Notes

  • 1238993: Proportional File Auto-Growth with SQL Server 2008
  • 1459005: Enabling index compression for SQL Server
  • 1482275: Setting Traceflags for SQL Server
  • 1488135: Database compression for SQL Server
  • 1558087: SQL Server Statistics Maintenance
  • 1612283: Hardware Configuration Standards and Guidance
  • 1648817: Disallow Page Level Locks for Microsoft SQL Server
  • 1649078: Disabling autostats for certain tables with Microsoft SQL Server
  • 1651862: Release planning for Microsoft SQL Server 2012
  • 1676665: Setting up Microsoft SQL Server 2012
  • 1744217: MSSQL: Improving the database performance
  • 1725220: New Trace Flags set and recommended with SQL Server 2012


SCN Space and Documents

There are many good blogs and documents in the SAP on SQL Server space on SCN. One in particular to highlight for planning purposes is 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 (yes, that's a doozy of a title, and I have yet to see Part II, unfortunately).


External Resources


Please comment if you have any thoughts on any of my recommendations, as I am always open to learn new ways of doing things and to benefit from the experience of others.



Please see SQL Server Configuration: Pre-SAP Installation for my personal recommendations on what to do next after initial installation.

As I’ve been quite busy within the last year I didn’t find much time to contribute or share any breaking new for a while – even though the lack of spare time didn’t improve much recently it’s finally time to talk about a ‘new’ topic which at least everyone working with BW has definitely come across lately – Columnstore.


Within the last year I spent a lot of time migrating BW systems to SQL Server, implementing SQL Server Columnstore Indexes and trying to make the queries and loads as fast as I can or if possible faster than they are on Hana. Today I want to share the results with you.


What is a SQL Server Columnstore Index, how does it work and how to implement it in an SAP BW system?


Many documents answer these questions a lot better than I ever could so I will not repeat the explanations for you but rather point you to the documentation that I found most helpful:


Column Store Indexes Described

Brief explanation on
- what Columnstore is
- how it works technically and
- what the main advantages are

Using SQL Server 2012 Column-Store with SAP BW

My personal favorite as the author managed to compress all the information you need to understand:
- which requirements need to be fulfilled
- how to implement column store indexes
- what to expect

within 19 pages


How difficult is it to implement SQL Server Columnstore Indexes and how long does it take?


In my opinion, SAP does a very good job with providing easy-to-use tools on SAP software level to implement quite powerful features on SQL Server level.
I saw this with row/page compression where a single report (MSSCOMPRESS) was all one needed to row/page compress database objects online/offline. With implementing SQL Server Columnstore Indexes it’s as simple again. SAP provides a report called MSSCSTORE which allows you to create Columnstore Indexes for a single infocube or all infocubes in the system and you can also use this report to switch back to rowstore for a single or all Infocubes (while I can’t think of a reason to switch back) with just a few clicks. The interface of the report is quite self-explanatory – if some questions still remain open it makes sense to look into SAP Note 1771177 and  Using SQL Server 2012 Column-Store with SAP BW.


I didn’t do any detailed performance analysis on the runtimes of implementing Columnstore Indexes and runtimes - as always – are influenced by many factors (hardware, system load, database size, and many more). However, the database sizes where I implemented column store indexes varied from 3 to 12 TB, and the runtime for all Infocubes in total varied from 3 to 12 hours.


Real-World Results with SQL Server Columnstore Indexes

The experiences I gained with SQL Server Columnstore Indexes are based on several proof-of-concept projects where the aim was to find out how fast BW queries and data load processes could get after switching from the conventional table structures of Infocubes to the usage of SQL Server Columnstore Indexes. Even though reducing the database size was not an important goal in any of the POCs I could observe a mentionable and sometimes even a massive reduction of the database size in every single case.

Case Study 1 - Reduction of space consumption

These numbers show how the space consumption of the database changed after different actions.




Original Size of the database

Allocated MB in transaction DB02 so no freespace within the DB Files contained in that number. All objects in the DB are page compressed.

9,5 TB

DB Size after compressing requests of Infocubes

All requests except the ones from the current month were compressed in transaction RSA1.
This has been done for all Infocubes. The aggregates still existed.

9,5 TB

DB Size after implementing Columnstore Indexes for the infocubes and deactivating aggregates

All requests except the ones from the current month were still compressed from the last action.
Columnstore Indexes were created with the option to deactivate all aggregates during the run.

6,2 TB

DB Size with Columnstore Indexes for Infocubes and aggregates using Columnstore Indexes as well

Aggregates were configured to use Columnstore Indexes as well and have been activated again

7 TB

Looking at the tables and indexes of an Infocubes in more detail before and after implementing Columnstore Indexes I gained an idea on how this amount of space is saved without doing anything but creating Columnstore.

Space Consumption of E + F fact table of an Infocube after different actions


Size of F-fact table [GB]

Size of E-fact table [GB]

Size of E + F fact [GB]

Delta to last step [GB]

Original state – no BW requests compressed, no Columnstore indexes, page compressed objects





After compressing all requests of the Infocube except the ones from the last and current year




+ 4,8

After compressing all requests of the Infocube except the ones from the last and current month




+ 0,6

After creating Columnstore indexes for the Infocube




- 13,3

In this case I’ve been using SQL Server 2012 for my tests. As Columnstore indexes are not updatable with this SQL Server release they are only used on the E-fact table of an Infocube. After creating a Columnstore Index including all columns of the E-fact table all other secondary indexes on the table become superfluous – for this reason MSSCSTORE drops them when it creates the Columnstore Index. The space previously occupied by the secondary indexes of E-fact tables becomes freespace now. The Columnstore Indexes of course consume space as well but they are stored column-wise and not row-wise and as columns often have similar data, high compression rates can be achieved (also pointed out here Column Store Indexes Described). In all my tests the Columnstore Indexes were quite small in comparison to the table sizes.

In another project I didn’t record the sizes in this level of detail but it might still be interesting to know that the original database size (Oracle with no database compression feature in use) was around 3 TB and after migrating to SQL Server and creating columnstore indexes (page compression in use as well) it went down to 800 GB.

Case Study 2 – Comparison of Query Runtimes

Looking at the runtimes of a defined set of BW queries on the source system (Oracle) and a SQL Server system with Columnstore Indexes I could observe the following runtimes:


Source System [sec]

SQL Server 2012 with columstore on E-fact tables but without any aggregates [sec]

SQL Server 2012 with columstore on E-fact tables and selected aggregates without Columnstore [sec]

Query 1




Query 2




Query 3




Query 4




Query 5




When I carried out this test SAP’s recommendation was to let report MSSCSTORE deactivate all aggregates while creating the Columnstore Indexes. As the above runtimes show I was able to further speed up 2 queries after re-activating aggregates for 2 queries – back then these were still conventional aggregates without a Columnstore. In the SAP released column-store also for BW-aggregates (SAP Note 1951490) which presumably would have speeded up all tested queries again.


The total runtime of all tested queries looked like this:


Total runtime for all tested queries [sec]

Source System (Oracle, no database compression)


SAP Hana


SQL Server 2012 with page compression and Columnstore Indexes on the E-fact tables but without any aggregates


SQL Server 2012 with page compression and Columnstore Indexes on the E-fact tables and with selected conventional aggregates without Columnstore Indexes


SQL Server 2012 with page compression and Columnstore Indexes on E-fact tables and on aggregates

Unfortunately didn’t have the chance to test this but presumably < 308

Lessons Learned

Keeping it brief without going too much into detail I learned the following lessons:


  • In any case mentionable, mostly even massive reduction of the database size
  • Speedup by factor 0-50 per query depending on the query and the cube design
  • The bigger the cube, the more speedup
  • If bottleneck is not the database, the speedup with implementing tuning measures on DB level like Columnstore Indexes understandably turns out to be a modest affair
  • Load runtimes improve as less index maintenance is required
  • As soon as the requirements are fulfilled the implementation of Columnstore Indexes is very simple
  • The integration of Columnstore Indexes in SAP BW is constantly improved so it makes sense to look for new features on a regular basis and adopt them
  • Considering the achievable speedup it makes sense to benchmark if a BWA still makes sense or if the required runtimes can already be achieved using SQL Server Columnstore Index


Continuous Improvements

Within the last year Iots of improvements were introduced regarding the integration of SQL Server Columnstore Indexes into SAP BW. To make sure I do not miss any of them I keep looking for SAP documentation, SAP notes, blogs on SCN and the SAP on SQL Server section of MSDN before I start with a new migration. It seems that some improvements are shipped within SPs without being announced explicitly for this reason I also try to always use a recent SAP BW and SAP Basis support package.


Filter Blog

By author:
By date:
By tag: