Running SAP Applications on SQL Server

New SAP on SQL Server 2008 Whitepaper released

Posted Wednesday, June 24, 2009 6:29 PM by Juergen Thomas - MSFT | 0 Comments

SQL execution plans, part 3: how to get the plan

Good Case study released on Customer who used SQL Server 2008 Database Compression

I just wanted to point all of you to a case study we just released about our customer Quanta out of Taiwan. It is a great write-up on how one of our customers used SQL Server 2008 Database Compression to reduce their disk space usage by large extents. It also is one of the first customers using Page Dictionary Compression in their productive SAP ERP system.

Please enjoy the read here:  http://www.microsoft.com/casestudies/casestudy.aspx?casestudyid=4000003962

Have fun

Posted Monday, March 30, 2009 9:00 PM by Juergen Thomas - MSFT | 1 Comments

Measuring Memory Consumption of SAP Application Server Instances

Since the 'Green-IT' movement is becoming serious, leverage of server resources or power consumption of existing resources is right in the middle of attention. One of the things analyzed is how much CPU is used on each of the servers and whether one could load a little bit more onto a server when one finds sufficient free CPU resources. Another angle people start looking into is the memory consumption of servers. As shown here, analyzing servers with the SAP Netweaver stack on memory usage is not that easy as it seems from the outside. When the analysis is done by people who are less familiar with the SAP implementation, the SAP Basis team suddenly could find itself confronted with the statement that there hardly is any memory used by the SAP application instances. As it happened with one of our customers. The folks performing the analysis sent the SAP Basis Team statistics stating that out of the 64GB real memory there were steadily 54GB memory available according to Windows Performance Monitor counters. Something that the SAP Basis Team knew that it could not be true. But how to show the truth? Here is how you show the real memory consumption and what the traps are, people are falling into.

In Windows Server 2003, the Windows Task Manager is partly right and partly wrong. Reason for this is the way how SAP creates their central part and largest chunks of memory. The so called Extended Memory is created either as as memory mapped file or as a huge chunk of shared memory (PHYS_MEMSIZE parameter in the SAP instance profiles). The memory defined as Memory Mapped File or Shared Memory is not subtracted from the 'Available Memory' in the Windows Task Manager (see below) or the Windows Performance Counter with the same name (Perfmon--> Memory -->Available Bytes or Available KBs or Available MBs). Let’s look at a screenshot of the Task Manager and go through a few of the numbers

We are looking at a server with 64GB real memory (Physical Memory block - 'Total'). However the row 'Available 'claims still to have 54GB available despite the fact that there are two SAP ABAP instances with 12GB Extended Memory each. Reason for this discrepancy is that 'Available' is calculated by subtracting memory associated to processes from the real memory. Both memory mapped files and  shared memory as used by SAP are not completely associated to a process. Hence only fractions of it are taken into account. Nevertheless the memory configured for the SAP instance needs to be taken into account. This can be done by using Performance Monitor and focus the monitoring on Memory--> Committed Bytes. This counter will correctly reflect the memory allocated for the SAP instances and might show like in the case above around 37GB. These 37GB also are reflected in Task Manager under the graph of PF Usage (Page File Usage) or in the block 'Commit Charge' in the lower left corner of Task Manager. Both are giving the accurate numbers on real memory consumption and are the numbers of interests. If the peak value of committed memory exceeds the real memory, then it indicates that there were situations where Windows had to page out memory of processes into the page file in order to make space for other processes in real memory. Something which can hurt performance. Especially on SAP JAVA application instances. Ideally we don't see the Peak number of committed memory going beyond the real memory of the server.

Unfortunately Windows Server 2008 did take a step back in showing how memory is consumed

Would one believe it, we are running 2 SAP ABAP instance on this system. Every instance has 5GB Extended Memory. Instead of ‘Page File Usage’, the ‘Physical Memory Usage’ is displayed now. This one unfortunately is not based on the committed bytes and hence doesn't count the shared memory segments and extended memory of SAP ABAP instances completely. Besides the performance Counter 'Committed Bytes', the correct numbers in the W2K8 Task Manager are in the last item in the 'System' block. It is called ‘Page File’. The first value of ‘Page File’ gives you the REAL committed memory and the second gives you the maximum which can be committed (real memory plus page file(s)). Again the Perfmon item ‘Committed Bytes’ is the one to monitor.

That only is in regards to application servers without SQL Server. With SQL Server we wouldn't confuse any of those values above since the memory allocated by SQL Server steadily is attached to a process. However where confusion kicked in was when looking on the memory associated with the sqlservr.exe process in the Processes pane of the Windows Task Manager. SQL Server leveraging Large Pages on 64Bit or AWE under 32Bit has the effect that you can’t see how much memory SQL Server uses when you are looking in the ‘Processes’ tab in Task Manager. See this screenshot:

It is not apparent that on this dedicated Database Server the SQL Server process on this system allocated around 58GB for SQL Server's Buffer Pool. A way how to find that out is to check these Performance Monitor Counters:

Memory --> Committed Bytes or Memory --> Available MBytes   in the case of SQL Server and the way SQL Server allocates the memory, these counters indeed work perfectly.

SQL Server --> Buffer Manager --> Total Pages will also show the size of the SQL Server Buffer Pool (however not the memory SQL Server allocates outside the Buffer Pool)

Whereas the SQL Server allocations are honored in the Page file Usage Section of Task Manager in Windows Server 2003 and Physical Memory Usage in Task Manager for Windows Server 2008

Hope this helps a bit to evaluate Memory Usage of systems running SAP software.

Posted Thursday, March 26, 2009 6:57 AM by Juergen Thomas - MSFT | 1 Comments

Applying SQL Server 2008 Database Compression to SAP systems which already ran on SQL Server 2000 RTM and SP1

Lately we had a strange incident when one of our customers for more than 10 years tried to apply SQL Server 2008 Database Compression against the SAP ERP test system. The history of the system is that it first got installed against SQL Server 7.0. The customer was extremely early moving to SQL Server 2000. They also moved very fast to SQL server 2005 and now are in the process of upgrading to SQL Server 2008. Over those years that system saw quite a few SAP release upgrades as well. One of the driving forces for the upgrade was SQL Server 2008 Database Compression. The system is multiple Terabytes in size, so the space savings could be significant and hence investments in storage could be reduced.

As one of their SAP Basis Administrators tried applying ROW Compression for all the tables in the test system, he ran into this failure on SQL Server side:

Msg 21, Level 22, State 1, Line 1
Warning: Fatal error 682 occurred at Feb  4 2009  5:47PM. Note the error and time, and contact your system administrator.

The error occurred while trying to compress the table MARC. So they tried to manually apply ROW compression on the table with this command:

ALTER TABLE tr3.MARC REBUILD WITH (MAXDOP=8, ONLINE=ON, DATA_COMPRESSION = ROW);

Again the same error. Looking deeper into the problem, it figured out that there seemed to be extreme large values in a minor number of rows in the columns GLGMG and VKGLG columns of table MARC. Columns which were not even in use by their SD functionality. The values in those columns were in the Billions ('Milliarden' in Germany). Completely unrealistic numbers. After correcting these numbers to the default value of 0.0 everything went fine.

But what was the problem? How did these numbers get into the system. It was Sven, the SAP Basis Administrator who tried to apply compression, who found OSS note #425946 describing exactly what the problem of the big numbers. The problem could have happened with SQL Server 2000 RTM and SP1. So far the effect of the issue only has been seen on table MARC in the two columns GLGMG and VKGLG. Hence not a worry at all for customers whose systems never ran on SQL Server 2000 RTM or SP1. For those people who want to apply Database Compression to SAP systems which did run on SQL Server 2000 RTM or SP1, we would recommend to check the values which are in those 2 columns of table MARC before going ahead with compression.

Thanks a lot to Sven Otromke for working with us and finding the OSS note.

Posted Thursday, February 26, 2009 4:56 PM by Juergen Thomas - MSFT | 0 Comments

SQL execution plans, part 2: SQL compilation

Today we want to have a closer look how an execution plan for a given SQL statement is created within SQL Server and how this affects an SAP system. The idea of this discussion is not to highlight in detail the phases of the plan creation. The intention is to give an SAP administrator or ABAP/JAVA programmer an idea, which factors have an impact on the execution plan.

The component of SQL Server which creates the execution plan is called (query) optimizer. The procedure of creating an execution plan is called compilation. Once a new plan is re-created for an existing query it is called a re-compilation. The most important decision which has to be made by the optimizer is to decide which database indexes are used and how they are used. SQL Server may perform one or several index seeks. In some cases an index range scan on the index may be even faster. For JOINs the optimizer has to decide the join method (nested-loop, merge-join, hash-join) and the join order.

A compilation can be a very time consuming operation. That’s the reason why SAP uses stored procedures or parameterized statements, which allow SQL Server to re-use an existing execution plan. However, a new execution plan has to be created once no valid execution plan exists:

·       After restarting SQL Server.

·       After clearing the procedure cache using DBCC FREEPROCCACHE. This command can also be executed in the SAP transaction DBACOCKPIT.

·       Once an execution plan is flushed out of the SQL Servers statement cache due to memory pressure. This can happen at any time.

·       For SAP applications running 4.6D kernel or older kernel releases, once the name of a global temporary stored procedure is flushed out of SAP’s stored procedure (SP) name cache. This can happen at any time. As a result SAP creates a new temporary SP which a different name. SQL Server will then create a new execution plan for the new SP.

·       For SAP applications running 4.6D kernel or older kernel releases, once the connection is closed, which originally created a global temporary SP. When SAP restarts a work process, all its database connections are certainly closed. As a result the dependent SPs will be dropped. SAP has to re-create these SPs and SQL Server has to re-create their execution plans.

An existing execution plan may become invalid:

·       After updating the index (or column) statistics, which were relevant for the query optimizer during plan creation. It makes no difference here whether this is an automatic or manual update statistics.

·       Once the structure of the table changes. This includes creating or dropping an index.

·       Once the SP executing a query or a table accessed by the query is explicitly marked for recompilation. This can be done using the system stored procedure sp_recompile. It is not recommended to mark a table for recompilation, because a “sp_recompile <TABLE NAME>” holds and requests database locks. We have seen blocking situations for hours at SAP systems caused by this. 

·       For SAP applications running 6.40 kernel or older kernel releases, If the SAP profile parameter “dbs/mss/max_duration” was set. Whenever the average runtime of a query exceeded the specified threshold, SAP executed a “sp_recompile <PROCEDURE NAME>” for the stored procedure taking too long. This parameter caused rather trouble than solving issues. Therefore it was removed from the SAP kernel.

Generally speaking, the decisions made by the query optimizer are based on the estimation of the number of rows selected and cost calculations (CPU/memory/IO needs) for the potential execution plans. There are several factors which have an influence on the chosen execution plan. Let’s have a closer look on them:

·       Existing indexes
This is the most obvious factor. Without a proper index you cannot expect a fast query execution. In the other hand, having too many indexes is much more often an issue on an SAP system. This may result in unstable (changing) execution plans. Inserts and deletes become more expensive the more indexes you have. Defining proper indexes is therefore often a compromise. You have to find a balance between speeding up a single query and minimizing the impact on the other queries. Typically you therefore create a few combined indexes to tune your most important queries running in dialog. Queries running in a batch job are often not expected to have optimal performance.

·       Clustered index property
SAP (almost) always creates the primary key as a clustered index on SQL Server. You can have at most one clustered index per table since the data is physically sorted by the clustered index. Having a clustered primary key has several advantages and reduces the cost for using the primary key. All other indexes are non-clustered and called secondary indexes. A secondary index stores the clustered index key (which typically does not change) rather than a physical pointer to the data row. This results in better update performance. On the other hand you have to traverse two index trees when using a secondary index: First of all the index tree of the non-clustered index to receive the clustered index key and then the clustered index to access the data row. In a nutshell, having a primary clustered key in SAP reduces the cost of using the primary key and increases the cost of using a secondary index. In extreme cases a secondary index will not be chosen by the optimizer due to higher costs of accessing the secondary index.

·       Index statistics
The index statistics of SQL Server basically consists of the density information of the combined n index columns and a histogram of the first index column. A density is the reciprocal value of the number of distinct values. If a combined index consist of three columns A, B and C then the index statistics contains the density of (A), the combination of (A,B) and the combination of (A,B,C). The histogram is only available for column A. It contains a sample of up to 200 values and the number of rows which have this value. If required, SQL Server automatically creates additional column statistics including a histogram for other columns, too. However, the database option “auto create statistics” is to be set for it, as recommended by SAP.
Having not up-to-date index statistics is the most common suspect when seeing bad query performance. However, on an SAP system the index statistics are typically innocent. SAP strongly recommends to turn on the database options auto update/create statistics. This results in automatically updated statistics and works pretty well on an SAP system. There are only a few scenarios where SAP recommends performing a manual update statistics in addition. One example is a client import of a relatively small client (column “MANDT” means “Mandant” in German) into a huge SAP database. The same may be necessary when creating a new company code (column “BUKRS” means “Buchungskreis” in German) on a system which only has a few distinct customer codes.
Updating the statistics only makes sense when the selectivity of the updated columns changes significantly. For example, the document number (column “BELNR” means “Belegnummer” in German) is almost always very selective. Even when updating a one year old index statistics of this column you will not see any significant difference in the selectivity. Therefore updating the statistics of this column is not relevant for optimizer decisions.

·       Parameters used for compilation
The SQL Server query optimizer estimates the number of selected rows based on the parameters passed at compilation time. This is very useful in many scenarios. A typical example is a delete flag (column “LOEKZ” means “Loeschkennzeichen” in German). There are many tables in ABAP which have such a column, containing either the value “ “ (for delete flag not set) and “X” (for delete flag set). There is often an index containing “LOEKZ” in order to speed-up selecting the rows, with have the delete flag set. Just having a look on the density 0.5 of this field, this index looks useless. Once you take the parameter value “X” into account and know from the histogram that only 0.01% of the rows have the value “X” (and 99.99% have the value “ “), the index becomes very attractive. This SQL Server feature of creating execution plans dependent on the parameters passed at compilation time is called parameter sniffing.
Although parameter sniffing helps in many cases, it may result in unexpected execution plans in some scenarios. Running a query the first time with untypical parameters results in an execution plan which may not fit for the subsequent executions of the same query. Dependent on the parameters passed by chance at compilation time, totally different execution plans may be used.

·       Other factors
It is self-evident that an optimizer hint or a plan guide has a huge impact on the execution plan. But also SQL Server configuration and connection settings are important. SAP recommends to turn off parallel query execution by setting the configuration option “max degree of parallelism” to 1. Setting this to a different value may result in different (parallel) execution plans.

All of the above factors influence the execution plan. For an SAP system you will rather see an issue with parameter sniffing than with outdated index statistics. However, customers often think that they have solved a performance issue by updating the statistics. They are not aware of the side effect of the statistics update. The optimizer will create a new execution plan due to the new statistics. This time the parameters used for compilation are probably different from the previous compilation. It’s just like rolling the dice. Probably a DBBCC FREEPROCCACHE would also have (temporarily) solved the issue.

You may probably ask yourself what to do when running into a performance issue. It is the same as with any other issue. You first have to analyze it before taking action. With SQL Server 2005 you can easily figure both, the execution plan and the parameters used for compilation. Once you detect instable execution plans due to varying parameters you should force an execution plan, for example by adding an optimizer hint. You can add all possible SQL Server optimizer hints into SAP ABAP code. You will even find SAP Standard coding which will force certain indexes for different databases by using ABAP Query hints (please see OSS notes #129385, 133381)

Posted Sunday, February 22, 2009 9:07 PM by Martin Merdes | 1 Comments

Frequently asked Question - Why do we need SQL Server Enterprise Edition for SAP Netweaver based applications?

In these hard economic times we get to hear one question very often these days: Why do we need SQL Server Enterprise Edition for SAP Netweaver based applications? Background to this question is that SAP mandates the usage of SQL Server Enterprise Edition for applications based on the SAP Netweaver Stack. Especially by customers who did not purchase SQL Server from SAP, but through other channels, this question is asked frequently. Those customers would like to use SQL Server 2008 Standard Edition, especially for their smaller systems. Let me give you some ideas why SQL Server Enterprise Edition is needed for SAP Netweaver.

Latest with SQL Server 2005 SAP Netweaver took hard dependencies on certain features of SQL Server which usually are not present in Standard Edition. For SQL Server 2005, it started with SQL Server's Table Partitioning which is an Enterprise Edition only feature. SAP implemented the usage of this feature in SAP BI/BW. In older Basis releases like 6.40 (SAP BI/BW 3.5), the usage of table partitioning was on customer demand only (non default). However with SAP BI 7.0, the usage of SQL Server Table Partitioning for Fact tables and Staging tables became the default behavior. Means there is a hard dependency on the presence of the Table Partition feature in the SQL Server 2005/2008 version.

Other Enterprise Edition features SAP customers and to a part SAP relies on, is 'parallel index creation' introduced with SQL Server 2000 or Online Index Maintenance introduced with SQL Server 2005. Especially Online Index Maintenance is something one hardly would like to miss today with database sizes in the Terabytes.

Now with SQL Server 2008 we introduced Database Compression and Backup Compression. Both features don't exist in SQL Server 2008 Standard Edition. In order to use storage in a space efficient manner, SAP by default takes advantage of the new SQL Server Row Compression. This means if you install new SAP systems against SQL Server 2008, SAP will create the tables with using the new and more space efficient Row format. This is true for all the SAP products using the ABAP stack. It represents a hard dependency against a SQL Server feature which doesn't exist in Standard Edition.

But think about the benefits of features like Database Compression and Backup Compression: They can save you a lot of money. Backup Compression was something a lot of customers requested for a long time. Hence a lot of customers spent money on 3rd party applications to get compressed backups. This isn't necessary anymore with SQL Server 2008. A net saving one should consider when looking at SQL Server Enterprise Edition. Readers of this blog might have seen articles on Microsoft IT applying the Row compression (new more efficient Row format) to our SAP ERP system. We saved quite a lot of space. Space which now extends the life span of the storage used in production, test and different sandbox systems. All in all around 400K-500K USD of investments in storage could be saved by applying the new Row format (Row Compression) to Microsoft's productive SAP ERP system alone in this fiscal year. Another customer reported a reduction of 35% volume of their SAP BI/BW system by just applying Row compression. More and more experiences around compression come back, reporting reducing larger tables to a fraction of their original size by either applying ROW compression or Page Dictionary compression. The potential savings achievable by SQL Server's database compression features and Backup compression could pay easily for the more expensive initial investment of SQL Server Enterprise Edition. We also don't make a secret about it, there will be more features about lowering the storage footprint of SQL Server in the future.

For a detailed matrix on features supported by different editions of SQL Server please checkout this document: http://msdn.microsoft.com/en-us/library/cc645993.aspx 

Hope this gives some good background 

Posted Thursday, February 19, 2009 9:47 PM by Juergen Thomas - MSFT | 0 Comments

SQL execution plans, part 1: SQL statement execution on an SAP system

The most common root causes of a performance issue in an SAP system are slow I/O subsystems and inadequate execution plans. Rarely do we encounter cases where CPU resources would be scarce. In order to analyze slow SQL statements you first need to figure out the statement text and the execution plan. SAP provides a rich set of database monitoring tools which makes it easy to figure out a slow SQL statement. It is also easy to get an execution plan for it. However, this might not be the currently used execution plan, but an estimated plan based on the given parameters. This causes a lot of confusion for database administrators and SAP consultant. Without a deeper knowledge how SAP executes SQL statements and how the SAP database monitors retrieve an execution plan you will not be able to understand performance issues based on bad or unstable execution plans.

Since this will be an extensive discussion, I had to divide it into several parts. Today we want to discuss how SAP executes SQL statements on SQL Server.

SAP was originally ported to Microsoft SQL Server on version 6.0 in the year 1995. In these days SQL Server had no SQL statement cache, but only a Stored Procedure Cache. Each time an ad-hoc query or prepared statement was sent to SQL Server, a new execution plan had to be created. However, without re-using execution plans the overall SAP performance on SQL Server (and other database platforms) would not have been acceptable. Therefore the SAP database interface always wrapped a stored procedure (SP) around an ABAP Open SQL Statement for execution against Microsoft SQL Server.

This had several advantages: The statement text sent to SQL Server (over the network) was shorter. It only contained the SP name and the parameters. Existing execution plans could be reused (which was the main intention of using SPs). In addition, it was quite simple to get the current execution plan of an existing SP.

On the other hand, using SPs also resulted in a lot of overhead in the SAP database interface. A mapping between the Open SQL statement of ABAP and the SP executing the native SQL statement on SQL Server had to be implemented. Once the Open SQL statement in ABAP changed, SAP had to make sure that also the SP changed. Before executing a SP, you first have to create it. This has to be performed on a separate database connection in order to commit the SP creation. To minimize the SP creation attempts SAP implemented a SP name cache which contained the existing SPs already created on the database. As you can clearly see, this resulted in a quite complex SAP database interface.

Just as the SQL Server features increased with new releases (SQL Server 6.5, 7.0, 2000, 2005, 2008), the SAP database interface made several improvements within new releases. It was decided to simplify the SAP database interface and get rid of SPs, because the original limitations of SQL Server 6.0 regarding re-usage of execution plans do not exist anymore.

Up to SAP release 4.6D SAP always used stored procedures:

·       Permanent stored procedures for SQL statements which have a statement id (a SAP internally well-defined identification of the corresponding Open SQL statement). Those stored procedures usually started with a 'Y' in their name.

·       Global temporary stored procedures (starting with “##”) for SQL statements which do not have a statement id (this is typically the ABAP command SELECT FOR ALL ENTRIES)

As a result the global temporary stored procedures have been replaced with parameterized statements as of SAP kernel version 620. Starting with SAP kernel version 700 all stored procedures have been replaced with parameterized statements.

There are two special cases. In all SAP releases there is a small set of stored procedures (starting with “sap_“) used by the SAP database monitors. Secondly you may execute Native SQL statements using the ABAP command EXEC SQL. This is a very rarely used feature in ABAP. On SQL Server it results in ad-hoc queries without any parameter.

The following table gives an overview how SQL statements are executed on Microsoft SQL Server for the particular SAP NetWeaver releases.

Using parameterized statements simplified the SAP database interface. However, it is very difficult to figure out the current execution plan of a parameterized SQL statement on SQL Server 2000. Starting with SQL Server 2005 you can easily query the SQL Server dynamic management views (or use the SAP database monitors). The only practicable method on SQL Server 2000 is a SQL Server profiler trace, which results in a huge overhead. Therefore it is recommended to upgrade a SQL Server 2000 system to SQL Server 2005 or SQL Server 2008 when running on SAP Basis releases of 6.20 or newer independent of ABAP or JAVA usage. Next week we will discuss when and how SQL Server creates an execution plan.

Posted Monday, February 09, 2009 8:52 PM by Martin Merdes | 1 Comments

SAP declares support for SQL Server 2008 and Windows Server 2008

Great news arrived from SAP these days. SAP declared support for SQL Server 2008 and Windows Server 2008. Let's first talk about SQL Server 2008.

The SAP Final Assembly department signed up on SQL Server 2008 GA for Netweaver 7.0 last week. As in the last 11 years, we passed the Final Assembly tests using SQL Server 2008 CU2 without any flaws and problems. The GA declaration will be reflected or to a degree already is reflected in the SAP Product Availability Matrix. Please keep in mind that SAP supports SQL Server 2008 on the two 64Bit platforms of x64 and IA64 only. As Operating systems we look into W2K3 SP2 (64Bit) and W2K8 (as soon as SAP declares GA for it). SAP products supported for SQL Server 2008:

• SAP products which are based on Netweaver 7.0 (SR3) including Netweaver EHP1. This would include SAP ERP based on ECC6.0, SAP Business Suite 7 (formerly 2008), CRM 7, BI 7, SAP EP 7, …
• SAP PI 7.1 and future SAP releases

This is a great success for us, despite the fact that it now took nearly 5 months to get the official stamp. Reason for the delay of 5 months was a pretty simple one. We released SQL Server 2008 when SAP was in the midst of bringing their SAP Business Suite 7 through Final Assembly. We also were facing the most complex certification ever with SAP release upgrades taking place on the same basis release (CRM5 to CRM7). So the test matrix was substantial. But it is done now. For SAP documentation on the Upgrade from SQL Server 2000 and 2005 to SQL Server 2008, please go to: http://service.sap.com/instguides --> Other Documentation --> Database Upgrades --> MS SQL Server

Despite taking 5 months, it represents a significant shorter time than with some of our competitors where new releases need more than a year to find support by SAP due to the product quality of our competitor's products. This leads us to the quality of SQL Server underneath SAP products. This quality is not coming out of the nowhere or is a given because we have more clever developers. No, the quality of SQL Server running underneath SAP applications, is a result of hard work, enthusiasm, dedication of a lot of people in Microsoft and SAP. It also is a result of Microsoft's IT personal going productive with SQL Server Beta/CTP releases with their SAP ERP system. Behind this exercise there are countless hours of testing in various SAP systems within Microsoft, changing SQL Server CTP build every quarter once in production to make sure that the latest bits provide the quality necessary. It simply is a huge responsibility and huge quantity of work the SAP Basis team within Microsoft takes to provide this great kind of real-life environment with one of the most important software systems within Microsoft. Another huge contributor to this success are the two teams of SAP working on porting SAP software to SQL Server. one team sitting with me in SQL Server development, the other team sitting in Walldorf, Germany. Without their early work on new SQL Server releases and their enthusiasm supporting Microsoft IT, we couldn't run with SQL Server Beta/CTP releases in production.

Now let's talk about Windows Server 2008. SAP today announced support of Windows Server 2008 for their Netweaver 7.0 based products. The support matrix for SAP applications on Windows Server 2008 looks like the one for SQL Server 2008:

• SAP products which are based on Netweaver 7.0 (SR3) including Netweaver EHP1. This would include SAP ERP based on ECC6.0, SAP Business Suite 7 (formerly 2008), CRM 7, BI 7, SAP EP 7, …
• SAP PI 7.1 and future SAP releases. SAP did support Netweaver 7.1 based products since a while since SAP's own JVM 5 can be used for

The delay in supporting Netweaver 7.0 based products was caused by problems around Sun's JVM 1.4.2 support. Sun announced the availability of a 1.4.2 JVM for Windows Server 2008 on 01/15 (http://finance.yahoo.com/news/Sun-Microsystems-to-Provide-bw-14069911.html ). Meanwhile SAP also released the news of supporting Windows Server 2008 for NW7.0 (https://www.sdn.sap.com/irj/sdn/windows ). Please check out he FAQ in the article about Windows Server 2008. Please also check OSS note #1054740. For running NW 7.0 based products you will need to download the new Sun 1,4,2_17 JVM according to OSS note #941595. When you are on the Sun website, please make sure not to download the _18 and _19 JVMs. These are not supported by SAP yet. You need to download the _17 JVM or JSE.

Have fun

Posted Wednesday, January 28, 2009 10:54 PM by Juergen Thomas - MSFT | 1 Comments

Procedure used for applying Database Compression to Microsoft SAP ERP system

As promised, we are publishing the stored procedure used to compress Microsoft’s SAP ERP system in this location. This procedure is not the only way getting SQL Server 2008 database compression applied.

The easiest one to use is the tool SAP provides as an attachment to OSS note #991014. It imports an additional report which can be used to assemble the list of tables in a specific schema of the SAP database. It allows compressing a select list of tables or all tables. It allows as well compressing in online and offline mode. For cases where we look at a few hundred GB databases and the chance to perform compression within a downtime, it is the easiest way to use this tool, just select all the tables and go for an ‘offline’ compression using all CPU resources on the table. The goal clearly is to use this tool and only move to use the second tool described here in cases where the situation gets too complicated using the first and simple method. Dependent on the underlying hardware one easily should be able to compress around 500GB-1TB database volume in an offline fashion within 24h.

The strategy also clearly is to compress all tables of the database. With SAP in future releases using SQL Server 2008 Row level compression by default, it doesn't make sense for going for large tables only, but really move the complete database into the state of Row Level Compression. Later on, the same approach might apply to Page Level compression.

The case where compressing a SAP database with a few Terabytes needs to be done ‘online’ with selected time slices in times of low activity (like weekends) needs some more testing, some more planning and eventually a more complex tool which is more like an expert tool. This will be the tool we talk about here.

So what is the tool we used in Microsoft? In the attachment to this blog you find T-SQL coding which when executed will create a stored procedure called sp_use_db_compression. It got developed for the operation conditions we encountered at Microsoft’s SAP landscape:

·         The SAP ERP Production System had more or less 6.5TB of data volume to be compressed completely into ROW level compression

·         Compression activities only were allowed to run between 6pm Friday evening and Sunday noon

·         Compression of data only could be done online with 2-4 CPU resources out of 16 available only

·         No additional downtime could be taken beyond the regular 2h every month

·         Some weekends needed to be spared due to quarter end reporting

·         There were requests not to compress certain tables on specific weekends

·         The largest table was 700GB

·         Microsoft uses Database Mirroring, hence Full Recovery Model was a given and couldn’t be changed to reduce the transaction log volume created

·         Monitoring the progress since Log-shipping was used as well and one needed to know how far the copies of the transaction log backups to the secondary fell back (Transaction log backups are executed every minute)

Steps taken to get to the compression phase

In order to get to the actual compression of the productive SAP ERP system the following steps were done:

·         Added another SAN partition to the database server of the test database server on the principal as well as on the mirror side

·         A second transaction log file got added on the new partition on the principal side and grew it to a size of a few hundred GB in order to have enough space in the transaction log. The new log file is automatically getting created on the mirror. For this purpose synchronous Database Mirroring got switched to asynchronous. After the extension of the transaction log Database Mirroring got switched into synchronous mode again

·         Ran the stored procedure sp_use_db_compression out of the SAP ERP schema with this call:
sp_use_db_compression 'ROW', @online='ON', @maxdop=2

The stored procedure opens a cursor over all tables in the specific database schema, joins data of those tables against some system tables and then works through the list of tables. The procedure has a set of tables which are excluded (tables starting with ‘sap’ or ‘sp’ and which will not be handled. The activity is reported in the table sp_use_db_compression_table. In this table run times, data sizes before and after the compression are reported (see more in the monitoring section). While this process was running, operations on the test system continued including stress tests. After a few days the whole execution finished. Checking the table sp_use_db_compression_batch_input one can find entries like this:

Tables reported with the Status ‘TO_BE_DONE’  are some of the larger table with varbinary(max) columns. This tables can’t be rebuilt in an online manner and require to be done in a downtime or in low workload times

In a downtime executed the stored procedure again with the following syntax:

sp_use_db_compression 'ROW', @maxdop=0, @batch_input=1

This time the stored procedure took the entries marked with a Status of ‘TO_BE_DONE’ in the table sp_use_db_compression_batch_input as input of tables to be compressed and worked through the list of tables in an online fashion (see more later).

At the end of such a run through a test or sandbox system with production like data one has a pretty comprehensive idea about the run time under eventually even more strenuous conditions than one could expect in production. Going through the table sp_use_db_compression_table with a select like this:

select * from sp_use_db_compression_table order by Date DESC

one could get a list of tables in the reverse order of the compression. With the following statement:

select sum(Compression_Time) from sp_use_db_compression_table

one gets the sum of the time taken to compress the whole test system in milliseconds. In the particular case of Microsoft’s system  one calculated that one probably needs 6 weekends to perform compression of different set of tables. To remain on the conservative side one calculated on spending 30h per weekend. It also figured out that the largest table would require one weekend for itself.

Building the Packages for the different weekends

After a lot of discussions with the user community using the MS SAP ERP system an agreement on the weekends for the online row level compression work was reached. At the same time, tables were identified which could not be converted on certain weekends. The next step was to create six tables of the structure of 'sp_use_db_compression_batch_input' which would contain all the tables of the SAP ERP database. The different portions of the tables in those 6 different tables (functioning as work packages per weekend) were structured according to the run time. Important rule: The available freespace in the database has to be at least the size of the table to be compressed. The easiest way to achieve this, is to start with the smallest tables and work the way up to the largest table. Why is this? Tables in the database are typically fragmented and are rebuilt during the compression. The released fragmentation space + the space saved by the compression will increase the available freespace. In the case of the MS SAP ERP system the first package contained all but around 70 tables. The other 6 packages contained less and less tables up to the last one, which only consisted of one single table. The format and content of one of the tables/packages looked like:

The work packages were called like 'sp_use_db_compression_batch_input_week<x>'. Each weekend prior to the compression work, to renames had to be executed: rename of 'sp_use_db_compression_batch_input' to 'sp_use_db_compression_batch_input_week<prior week>' and rename of 'sp_use_db_compression_batch_input_week<x>' to 'sp_use_db_compression_batch_input'. It is important to keep the content of table 'sp_use_db_compression_batch_input' after the compression was finished to identify tables which have to be done in offline mode. The last step in the compression work is to execute the offline compression for all left-over tables. One has to combine all the rows of the different batch_input tables which had the Status ‘TO_BE_DONE’ and with the column Online being set to ‘OFF’ into the finale 'sp_use_db_compression_batch_input' table. A regular downtime was used then to compress those tables to Row level compression. New tables created out of the SAP Data Dictionary will be automatically created in the Row Level compression format.

Monitoring while compression is running

There are two possibilities for monitoring while the compression procedure runs. With this query:

select * from sp_use_db_compression_table order by Date desc

one basically gets an output as shown in the screenshot below:

Please note the first row with NULL values in columns like Compression_Time, Row_Count, After_Reserved. This is an indication that this table is currently being worked at.

If sp_use_db_compression is executed with '@batch_input=1' set as parameter then another possibility for monitoring is:

select * from sp_use_db_compression_batch_input

The result then could look like:

The table with the Status field set to ‘IN_WORK’ will indicate the table which is being compressed at the moment.

Experiences with Row Level Compression so far

From a space savings point of view, the MS SAP ERP system experienced savings of 29%. Sure this included effects of database re-organization as well. However one needs to take into account that a lot of content in major tables were not too fragmented anyway. Reason was a Unicode Migration which took place in 2007. The Unicode Migration left the database in a complete re-organized state. Nevertheless, 29% were more than 1.76TB freespace released. Given the fact that images of the productive database in the Microsoft SAP landscape is stored for at least 10 times the overall saving amount to more than 17TB. There was a wide bandwidth of compression efficiency on different tables. There were some tables like ANLC which compressed down to ¼ of their original size. There were other tables which were cut to half of their volume. We saw a great effect on one of our customer tables which was 700 GB with 1.6 billion rows. After applying row level compression the table ended up with 280GB of volume.

Other positive side effects were:

·         One could observe a reduction of I/O. However a visible improvement of the database response time could not be immediately observed. Reasons are manifold starting with the fact that the cache hit ratio of the SAP ERP system anyway was beyond 99% before already. Other reasons are that the database response time on daily basis varies by a range of 25-30% anyway dependent on load and season

·         Online backups and differential backups became smaller. Thanks to SQL Server 2008 Backup Compression, backups were reduced dramatically anyway. However a reduction could be observed again after Row level compression got applied

What about performance of specific jobs? Honestly in Microsoft one didn’t investigate in the many hundreds of different jobs which are running on the systems. One only can tell one thing which is that no jobs runtime has increased due to row level compression. An increase of CPU consumption also couldn’t be observed. However that also would be difficult due to changes in the 10% range on a daily basis. Stress tests in the test  environment after row level compression also showed a performance neutral behavior in the system.

Posted Monday, November 24, 2008 9:59 AM by Juergen Thomas - MSFT | 1 Comments

Analyzing a HANGMAN log file, part 2

The SAP and Windows specific sections of a HANGMAN log file have already been explained in a previous blog entry. Today we want to have a look on the SQL Server specific sections. For the explanation I will use the new terms session/connection/request introduced in SQL Server 2005. However, you may still see the old terms connection/SPID in a HANGMAN log file for SQL Server 2000.

Blocking SQL process list
This list contains all blocking and blocked SQL sessions. The following example is from an SAP JAVA system running on SQL Server 2000. The output of HANGMAN is different on SQL 2000 compared to SQL Server 2005 or newer (due to the different architecture and system tables). For all SQL Server versions you can get the blocked SQL statements and the current SQL statement running on the blocking session. Exclusive locks are held until the end of a transaction, which may consists of many SQL statements. Therefore the current running SQL statement in the blocking session may not necessarily be the SQL statement holding the blocking lock(s). This is a common misunderstanding at customer sites and even sometimes in SAP support. It may even be the case that there currently is no running SQL statement on the blocking session. The only thing a session needs to block another is an open transaction and an lock which conflicts with lock requests of other transactions.

The following list is an extract from a HANGMAN log file of a SQL Server 2000 system. All further lists are extracted from the attached HANGMAN log file of a SQL Server 2005 system.
spid ecid blocked wait[ms] input_buffer   stmt_text
---- ---- ------- -------- -------------- ----------------------------
 153    0       0        0 sp_execute;1   -->SELECT "VSE"."OBJECTID","
 144    0     153  3313813 sp_prepexec;1  ---->SELECT VSE.OBJECTID, VO
 123    0     144    73797 sp_prepexec;1  ------>SELECT VSE.OBJECTID,
 139    0     144  1873797 sp_prepexec;1  ------>SELECT VSE.OBJECTID,
 143    0     144  2773813 sp_prepexec;1  ------>SELECT VSE.OBJECTID,

The input buffer shows the last statement sent to SQL Server on this particular session. Having a look at the input buffer in the example above you can only see a “sp_execute”. This means that the SQL statement is a prepared statement. It does not tell you which SQL statement is running. SAP always uses prepared statements for JAVA. For ABAP prepared statements are used in certain cases as of NetWeaver release 6.20 and in (almost) all cases as of NetWeaver release 7.00 running against SQL Server 2005 and 2008. To figure out the SQL statement you have to look at the text column (“stmt_text” or “sql_text”). In some cases you will not see the SQL statement. There could simply be no running SQL statement on the blocking session (while there is always a running SQL statement on the blocked session). In rare cases you will not see the SQL statement text while the optimizer is creating an execution plan for this statement.

Blocking SQL process list of a SQL Server 2005 system:
session blocking wait
   id      id    sec  wait_type   wait_resource sql_text
------- -------- ---- ----------- ------------- -----------------------------
   81       0    NULL NULL        NULL          > delete from BLK_TEST3
   86      81     425 LCK_M_U     KEY: 5:720575 --> delete from BLK_TEST3 whe
   87      81     423 LCK_M_X     KEY: 5:720575 --> insert into BLK_TEST1 val
   89      87     422 LCK_M_SCH_S OBJECT: 5:212 ----> (@P0 int,@P1 int)begin
   95      89     418 LCK_M_SCH_S OBJECT: 5:212 ------> (@P0 int,@P1 int)begi
   97      89     417 LCK_M_SCH_S OBJECT: 5:212 ------> (@P0 int,@P1 int)begi
  101      89     415 LCK_M_SCH_S OBJECT: 5:212 ------> (@P0 int,@P1 int)begi
  102      89     414 LCK_M_SCH_S OBJECT: 5:212 ------> (@P0 int,@P1 int)begi
   88      81     423 LCK_M_X     KEY: 5:720575 --> insert into BLK_TEST1 val
   90       0     414 WAITFOR                   > WAITFOR DELAY '20:00'
   92      90     419 LCK_M_S     KEY: 5:720575 --> select * from BLK_TEST3 w
  104      92     412 LCK_M_S     KEY: 5:720575 ----> select * from BLK_TEST3
   93      90     419 LCK_M_S     KEY: 5:720575 --> select * from BLK_TEST3 w
  100      90     416 LCK_M_S     KEY: 5:720575 --> select * from BLK_TEST3 w

For SQL Server 2005 and more recent releases the blocking process list also contains the wait type, wait resource and the wait time in seconds. A blocking situation as such is not an issue. It may become a problem once it remains for many seconds or minutes and starts blocking more and more requests. In the example above you can see two independent blocking situations. To figure out who blocks whom you can either lock at the session ID and blocking ID or at the indentation (-->) of the SQL statements.  Let’s have a look on the first blocking situation caused by session ID 81. This session is not blocked by anyone else (blocking ID = 0), but blocks sessions 86, 87 and 88. Session 87 blocks session 89 which in turn blocks the sessions 95, 97, 101 and 102. HANGMAN recursively sorts the hanging SQL process list, which makes it easy to identify the independent blocking trees just by looking at the indentation of the SQL statements.

Some people confuse a blocking situation with a deadlock. A blocking situation happens if session B waits for a lock which is hold by session A. Such a blocking situation resolves without further interaction at the point in time session A releases the blocking lock. The blocking situation turns into a deadlock once session A waits for another lock which is hold by session B. SQL Server automatically detects such a deadlock and automatically resolves it by terminating one of the two sessions. In an SAP system you typically see an ABAP short dump as the result of a deadlock. SAP configures the update work processes to be always the victim of a deadlock. The SAP update work process can repeat a deadlocked database transaction without any business impact. Therefore you will not see a short dump or any other error in the SAP system log once a deadlock occurs on an update work process.

SQL process list
For SQL Server 2005 and more recent releases this section consist of 2 independent lists. The first list is actually a join of the SQL Server sessions (S), connections (C) and requests (R) retrieved from the SQL Server dynamic management views. This list is ordered by the SAP work process number (using the program name).  It contains the recent SQL statement which was executed on the connection, even when there is currently no active request. The second list contains all SQL requests ordered by the corresponding session ID. This list contains the current SQL statement which is being executed.

For an ABAP system, SAP sets the program name when connecting to SQL Server, for example “R3D00 comm rd ODBC”. The first 2 characters of the program name are “R3” followed by the work process type (“D” for dialog work process, “B” for batch, “U” for update, ...). The next two characters show the work process number as you can see in the SAP work process list. The following characters indicate the SQL Server isolation level (“comm rd” for committed read, “unc rd” for uncommitted read) and the used database interface (“ODBC” or “OLEDB”). You should be aware that the type of an SAP work process may change without reconnecting to the database (when changing SAP’s operation mode). Therefore the type displayed in the application name is not always reliable. However, the work process number does not change.

A session is in status running if the work process which opened the session has sent an SQL statement to SQL Server and is waiting for the results. For running sessions a request exists which is using one of SQL Servers worker threads. A sleeping session simply means that there is still an open session, but SQL Server has nothing to do for this session. A sleeping (user) session has no corresponding request.
The number of threads which can concurrently run on Windows depends on the installed CPUs. This limits also the number of SQL Server requests which can be in status ‘running’. For a two processor, dual core, non-hyperthreading machine there can only be 4 requests in status running at one point in time. When looking into a HANGMAN log file one running request always comes from HAMGAN itself, respectively OSQL.EXE. It is consequential that the time the SQL process list is retrieved, it is the retrieving request which runs.

ID  Host  Program  OS    Status  delta delta delta             Status
(S) (S)   Name (S) Proc  (S)     CPU(R)Reads LogReads delta    (R)
--- ----- -------- ----- ------- ----- ----- -------- -------- -------
  1                      sleepin 0     0     0        1000 ms  backgroun
  2                      sleepin 0     0     0        1000 ms  backgroun
 86 WSI64          0     running 0     0     0        1000 ms  suspended
 87 WSI64          0     running 0     0     0        1000 ms  suspended
 88 WSI64          0     running 0     0     0        1000 ms  suspended
 89 WSI64          0     running 0     0     0        1000 ms  suspended
 95 WSI64          0     running 0     0     0        1000 ms  suspended
 96 WSI64          0     sleepin
 97 WSI64          0     running 0     0     0        1000 ms  suspended
101 WSI64          0     running 0     0     0        1000 ms  suspended
102 WSI64          0     running 0     0     0        1000 ms  suspended
 81 WSI64 Microsof 2920  sleeping
106 WSI64 OSQL-32  6808  running 15    0     291      1000 ms  running
 61 WSI64 R3D00 co 3080  sleepin
 62 WSI64 R3D02 co 3096  sleepin
 72 WSI64 R3D02 un 3096  running 0     -65   -1356    1000 ms  suspended
 66 WSI64 R3D03 un 3104  sleepin
 56 WSI64 R3D04 co 2540  running 0     1     3

Similar to the Windows process list, a delta of CPU usage, physical reads/writes and logical reads is calculated for each request. The time frame used for the delta values is displayed in a separate column. The currently running hangman/osql request in the example above was consuming 15ms CPU time within the last 1000ms. In this time frame 0 physical reads and 291 logical reads (data buffer accesses) were performed. In rare cases you might see negative values which can be explained with inaccurate values in the SQL Server dynamic management views.

In the section “Blocking SQL process list” above we have seen a blocking situation caused by session 81. The first thing for analyzing this is to have a look at the SQL process list and figure out what is going on in this session. The first surprise is that session 81 is sleeping. Therefore there is no corresponding request which could consume CPU time. Secondly session 81 was opened by the program “Microsoft SQL Server Management Studio”. This blocking situation is therefore not caused by any SAP report.

If a blocking session was opened by a SAP work process then the session could also be sleeping. The SAP work process might be busy with executing ABAP calculations while an open database transaction persists. For further analysis you should check what the SAP work process is doing, in particular whether the work process consumes CPU or not. To figure out which work process opened the session you can use the server name “Host(S)” and the SAP work process number as part of “Program Name(S)”. Alternatively you can also use the Windows process ID from the column “OS Proc(S)”. Unfortunately you can see neither the program name nor the Windows process ID for a JAVA system.

SAP uses multiple active result sets (MARS) of SQL Server 2005 and newer. Therefore you always have at least two SQL connections per SQL session. The first connection is internally used for MARS and will never be related to any SQL request. That’s the reason why HANGMAN does not show this first SQL connection per session in the SQL process list.

Database lock list
The list of all held and requested locks at any given point in time is one of the most powerful features of HANGMAN. For performance reasons (of HANGMAN) the list may be incomplete. However, this is typically not the case when there are less than a few hundred of thousand locks. The list is definitely complete if it ends with the number of locks in the list (“xxx rows affected”). In addition to the object ID you can see the object name (table name) for each database lock. Since the translation form object ID to object name can be very time-consuming, it is only performed for the database HANGMAN is connected to. The database locks of other databases are also contained in the list, but without the object name.

The locks are ordered by the session ID which is holding (status = “G” for GRANTED) or requesting (status = “WAIT”) a database lock. The lock granularity of SQL Server can be a row, a page or a table (type = “OBJECT”). Row locks are either row IDs (type = “RID”) or a hash value calculated on the clustered index key (type = “KEY”). Since almost all tables in SAP have a primary clustered index you will see lots of KEY locks and very seldom PAGE or OBJECT locks in a HANGMAN log file. SQL Server has a special lock mode, called intent locks (mode = “IX”, “IS”, “IU”, ...). Intent locks are an indication for an existing lock with lower granularity. For example, a shared lock (“S”) on a row results in an intent shared lock (“IS”) on the page and on the table. The lock list of HANGMAN does not contain granted intent locks, but it contains all waits on intent locks.

In our example above we have seen a blocking situation caused by session 81. Let’s have a closer lock on the related locks: Sessions 87 is requesting an exclusive row lock (KEY 0500d1d065e9) on index ID 1 of table BLK_TEST1. The blocking session 81 is holding an exclusive lock on exactly this row. Hereby we have proven that this works as designed. There are no page or table locks involved. It is not the lock granularity of SQL Server but the application logic, which caused the blocking situation.

session                        db ind
   id   object id  object name id id  type     mode  status description
 ------ ---------- ----------- -- --- -------- ----  ------ --------------
 81     .          .           5  .   METADATA Sch-S G      principal_id =
 81     .          .           5  .   METADATA Sch-S G      schema_id = 6
 81     2060495065 BLK_TEST1   5  1   KEY      X     G      (020068e8b274)
 81     2060495065 BLK_TEST1   5  1   KEY      X     G      (010086470766)
 81     2060495065 BLK_TEST1   5  1   KEY      X     G      (0400b4b7d951)
 81     2060495065 BLK_TEST1   5  1   KEY      X     G      (03000d8f0ecc)
 81     2060495065 BLK_TEST1   5  1   KEY      X     G      (0500d1d065e9)
 81     41011873   BLK_TEST3   5  1   KEY      X     G      (010086470766)
 86     .          .           5  .   METADATA Sch-S G      principal_id =
 86     .          .           5  .   METADATA Sch-S G      schema_id = 6
 86     41011873   BLK_TEST3   5  1   KEY      U     WAIT   (010086470766)
 87     .          .           5  .   METADATA Sch-S G      schema_id = 6
 87     .          .           5  .   METADATA Sch-S G      class = 0, maj
 87     .          .           5  .   METADATA Sch-S G      principal_id =
 87     .          .           5  .   RID      X     G      1:334232:0
 87     2060495065 BLK_TEST1   5  1   KEY      X     WAIT   (0500d1d065e9)
 87     2124495293 BLK_TEST2   5  1   PAGE     X     G      1:334232
 87     2124495293 BLK_TEST2   5  1   PAGE     X     G      4:333927
 87     2124495293 BLK_TEST2   5  .   OBJECT   Sch-M G
 87     5          sysrowsets  5  1   KEY      X     G      (0000ca0144f3)
 87     7          sysallocun  5  1   KEY      X     G      (0000c96b563e)
 88     .          .           5  .   METADATA Sch-S G      schema_id = 6
 88     .          .           5  .   METADATA Sch-S G      principal_id =
 88     2060495065 BLK_TEST1   5  1   KEY      X     WAIT   (0400b4b7d951)
 89     .          .           5  .   METADATA Sch-S G      schema_id = 6
 89     2124495293 BLK_TEST2   5  .   OBJECT   Sch-S WAIT
 95     .          .           5  .   METADATA Sch-S G      schema_id = 6
 95     2124495293 BLK_TEST2   5  .   OBJECT   Sch-S WAIT
 97     .          .           5  .   METADATA Sch-S G      schema_id = 6
 97     2124495293 BLK_TEST2   5  .   OBJECT   Sch-S WAIT
 101    .          .           5  .   METADATA Sch-S G      schema_id = 6
 101    2124495293 BLK_TEST2   5  .   OBJECT   Sch-S WAIT
 102    .          .           5  .   METADATA Sch-S G      schema_id = 6
 102    2124495293 BLK_TEST2   5  .   OBJECT   Sch-S WAIT
...
(11154 rows affected)

Session 87 is holding a schema modification lock (mode = “Sch-M”) on table BLK_TEST2. This lock mode is used when changing the table structure, for example when creating or dropping indexes. It is also used when truncating a table. Session 89 simply wants to select data from table BLK_TEST2 and therefore requests a schema stability lock (mode = “Sch-S”) for the duration of the execution. This lock only can be granted once the schema modification lock held by session 87 on the same table is released. Sessions 95, 97, 101 and 102 also request a schema stability lock on the same table. Logically they also wait for session 87. However, due to the internal monitoring structures of SQL Server they wait on session 89, which in turn waits on session 87. Anyway, schema stability locks can be granted to multiple sessions at the same time.

SQL Server statistics
HANGMAN adds at the end of its log file additional SQL Server statistics regarding schedulers, memory usage, cache hit ratio and the SQL Server Errorlog. The most interesting part is probably the I/O response time per file. In the attached example the response time is horribly bad. Currently HANGMAN calculates the response time as an average since SQL Server startup. There is no delta calculation on the last seconds as it is the case for the CPU usage in the SQL process list. This feature will be added in a future version of HANGMAN.

db   file ms per ms per database path
id   id   read   write
---- ---- ------ ------ -------- ------------------------------
  1   1   12.84  58.00  master   C:\Program Files\Microsoft SQL
  1   2   16.82  25.00  master   C:\Program Files\Microsoft SQL
  2   1   36.39  200.12 tempdb   E:\TEMPDB\tempdb.mdf
  2   2   17.11  23.90  tempdb   E:\TEMPDB\templog.ldf
  3   1   13.30  24.50  model    C:\Program Files\Microsoft SQL
  3   2   20.86  34.38  model    C:\Program Files\Microsoft SQL
  4   1   14.41  32.66  msdb     C:\Program Files\Microsoft SQL
  4   2   18.13  10.78  msdb     C:\Program Files\Microsoft SQL
  5   1   129.47 136.71 SR3      E:\SR3DATA1\SR3DATA1.mdf
  5   2   35.50  32.20  SR3      E:\SR3LOG1\SR3LOG1.ldf
  5   3   136.23 159.89 SR3      E:\SR3DATA2\SR3DATA2.ndf
  5   4   144.60 151.73 SR3      E:\SR3DATA3\SR3DATA3.ndf
  5   5   154.84 149.97 SR3      E:\SR3DATA4\SR3DATA4.ndf

If you want to have a look at the complete HANGMAN log file of our example then just open the attached file HANGMAN__2008-10-07__06-15-37__650.log.

Posted Sunday, November 23, 2008 7:19 PM by Martin Merdes | 1 Comments

Update on Rollout of SQL Server 2008 Row Level Compression in Microsoft’s SAP ERP system

Well meanwhile the Roll-Out is as good as finished. The only tables remaining are some tables with BLOB fields which can’t be done ’online’. Those tables will be done in our quarterly downtime where we need to apply a whole stack of new SAP Support Packages, exchange to more recent SAP executables, etc. But all tables which could be done online and some of the smaller tables with BLOB columns meanwhile are in Row-level format. As mentioned in the first articles already, we only talk about the Clustered Indexes/Data and not the non-clustered indexes at this point in time. SAP does not yet support compression on non-clustered indexes. SQL Server 2008 would be able to compress non-clustered indexes as well. We are working with SAP to allow compression of non-clustered indexes in the future as well.

So what did we experience? Our data volume shrunk by 1.75TB. This represents space savings of 29%. Since we are honest folks, we will not hide that parts of that effect roots in the defragmentation which takes place when we rebuild the tables in the Row-Level format. However since the database got completely unloaded and reloaded in February 2007 during our Unicode Migration, we had plenty of older data in the tables which wasn’t fragmented at all. Also given the fact that we got the original SAP productive database around 10 times in sandbox, test systems and our DR site, the savings on disk space really accumulate up.

The effects on different tables varied. Some larger effects we saw on the table ANLC which shrunk to 1/5 of its origin size. Also some of our custom tables shrunk down to 40% of its origin. We had one of those custom tables which was around 700GB with 1.6 billion rows which ended at around 280GB after the Row-Level compression. COSP out of the SAP standard tables also compressed extremely well shrinking to ¼ of its origin size.

The best of it all, it doesn’t cost any CPU. Row-Level compression is just another way to store some datatypes. Hence all we saw was reduction of I/O rate, but no measurable increase in CPU consumption.

So what do we do with all the free space in our SAP ERP system? Well we currently look at 2TB free space with around 4.6TB data volume in the database remaining. Since our monthly growth rate at the moment is around 150GB (due to row level compression it went down as well), it only is a matter of time when the free space is eaten up again. Hence we simply leave the sizes of data file as is now and have the monthly growth rate eat up all this free space again but we could delay the purchase of additional storage thanks to Row level Compression.

The Row Level compression work was done in an online mode over 4 weekends and no negative impact to any business processes occurred. The execution was done very successfully and has gotten a lot of attention within Microsoft IT to repeat it also with other Non-SAP SQL Server databases.

The stored procedure we used and will use in the future will be released in this blog within the next few weeks.

Posted Thursday, October 30, 2008 8:18 PM by Juergen Thomas - MSFT | 1 Comments

Analyzing a HANGMAN log file, part 1

The HANGMAN log file, created with the HANGMAN tool described in SAP note 948633 gives us a better understanding about the root cause of a system hang or hiccup of an SAP system. The log file consists of several sections: SAP process list per instance, Windows process list per server, SQL process list, database locks and some additional information (cache sizes and hit ratio, scheduler info, I/O response time per file).

In the following examples we only will have a look at the most interesting columns of each list. In real life, the lists contain much more information in additional columns. You can see this in the attached hangman log file HANGMAN__2008-10-07__06-15-37__650.log. This log file was created on a test system (SAP 7.00 ABAP + JAVA stack, SQL Server 2008) while some external JAVA programs executed blocking and blocked SQL statements. The intention of this was to get a more interesting log file containing (indirectly) blocked statements. Normally you will not see external programs accessing the tables of an SAP database.

The most common SAP modules are based on an ABAP stack. You also may use HANGMAN for a pure JAVA stack, for example for an SAP Enterprise Portal system. However, in this case you will not see any (JAVA) work process list. In the following “SAP work process” is always related to ABAP. Let’s have a closer look on the main sections of a HANGMAN log file:

SAP instance list
This list gives you an overview of the whole SAP system. The output is similar to SAP transaction SM51. You can see the hostname, SAP instance number and installed services for each running SAP instance. Since this information is retrieved from the SAP message server a stopped SAP instance is not listed here.

CLIENT-NAME     HOST     SERVICE  SERVICES (SAP)
--------------------------------- ---------------------
SAPAPP1_SR3_00  sapapp1  sapdp00  DIA UPD BTC UP2
SAPAPP2_SR3_02  sapapp2  sapdp02  DIA BTC
SAPCI_SR3_00    sapci    sapdp00  DIA UPD ENQ BTC SPO ICM

SAP work process list per instance
The columns of the SAP work process list are separated by tabs. Therefore the tool used to open the HANGMAN log file affects the layout. All other lists use spaces to separate the columns. The SAP work process list is collected remotely using the COM interface of SAP kernel 4.5B and newer. Since this is collected by of the dispatcher process of a particular instance, a free SAP dialog process is not needed to retrieve the data. The output looks similar to the output of SAP transaction SM50:

No Typ  Pid  Status Reason Sem Time Program  Client User Action      Table
0  DIA  388  Wait                                   
1  BTC  4624 Run               472  SAPLIBIN 100    MIKE Direct Read ZSHIP
2  BTC  5028 Stop   RFC        4212 RSCOLL00 000    DDIC     
3  BTC  2252 Stop   RFC        1271 SAPLSALC 100    DDIC
4  BTC  264  Stop   ARFC       2471          000    SAM      
5  UP2  2400 Wait                                       

This is typically the starting point of any analysis in an ABAP system, because you can see here the state of the system from an SAP point of view. You can figure out the name of an SAP user, which is performing a database select. “Pid” is the Windows process ID running the ABAP work process.
A work process containing the value “Direct Read” or “Sequential Read” in column “Action” is waiting on a database SELECT. “Sequential Read” simply means that an ABAP “select single” is executed, which results in a “select top 1” on SQL Server. This has nothing to do with the execution plan used by SQL Server to run the SELECT. Customers are often confused about this and assume that SQL Server is performing a full table scan once they see long rung sequential reads in SM50.

SAP work process queue statistics
The HANGMAN log file contains the SAP work process list of each SAP instance twice in order to get a better idea what is going on in the system. Between the two snapshots of the work process list you can see the queue statistics for each type of work process.

Typ  Now  High Max 
NOWP 0    3    2000
DIA  0    4    2000
UPD  0    1    2000
ENQ  0    0    2000
BTC  0    1    2000
SPO  0    0    2000
UP2  0    0    2000

The most interesting types are DIA (dialog), BTC (batch) and UPD (update). “Now” tells you the number of requests waiting on a free SAP work process at this moment. Any value greater zero means that all work processes of the according type are in use. “High” is the highest number which occurred since the SAP instance started. “Max” is the configured queue length, which is typically large enough.

Windows process list
The Windows process list is collected for each ABAP application server and the database server. Running two SAP instances and SQL Server on the same server will result in one single Windows process list (not in three).

Proc                                       DeltaUsr DeltaKrn       
  Id ProcessTree            Thread WorkingSet  Time Time   ProcessPath
   0 System Idle Process         8      28672     0 5140             
   4 System                     72      36864     0   15             
 744   smss.exe                  2     274432     0    0             
 984     csrss.exe              16    4546560     0    0  C:\WINDOWS\s
1200     winlogon.exe           18    4390912     0    0  C:\WINDOWS\s
1340       services.exe         19    2314240     0   62  C:\WINDOWS\s
1832         SAPOSCOL.EXE        4   15220736     0    0  F:\usr\sap\P
2064         SAPSTARTSRV.EXE     9   22855680    15    0  F:\usr\sap\P
3456           disp+work.exe     3   16351232     0    0  F:\usr\sap\P
 292             gwrd.exe        4    5828608     0    0  F:\usr\sap\P
 380             icman.exe      54    9572352     0    0  F:\usr\sap\P
 388             disp+work.exe   5  363868160     0   15  F:\usr\sap\P
4624             disp+work.exe   5  363728896   750   15  F:\usr\sap\P
1556             disp+work.exe   5  196173824     0    0  F:\usr\sap\P

HANGMAN uses WMI to get Windows process information twice within a time frame. It then calculates a delta of user/kernel time, physical reads/writes and page faults. In the example above you can see that the SAP work process with windows process id 4624 consumed 750ms CPU time in user mode and 15ms in kernel mode during the time frame. The length of the time frame is not fixed and depends on the speed and workload of the whole SAP system. Means it is dependent on the execution time of the HANGMAN script. You can calculate the time frame by adding all user and kernel times of all processes, including the system  idle process (and dividing the result by the number of CPU cores). In the example the time frame (delta time) is about 6000ms.
By comparing the process ID (here: 4624) of the Windows process list with the SAP process list, you can see that it was SAP work process ID 1 (a batch job) which consumed the 750ms CPU time in user mode.
The Windows process list of the database server contains the process SQLSERVR.EXE and its total number of threads. This includes system and worker threads. Seeing a huge number of threads here while the SQL process list is missing in the same HANGMAN log file is an indication for SQL Server running out of worker threads. In this case you should restart HANGMAN with the DAC (dedicated admin connection) parameter.

The next time we will have a further look on the SQL Server specific sections of a HANGMAN log file.

Posted Friday, October 24, 2008 10:05 AM by Martin Merdes | 0 Comments

SQL Server SPs, CUs and hotfixes for SAP

The first cumulative update package (CU1) of SQL Server 2008 was released last week. You can download it from http://support.microsoft.com/kb/956717/en-us. SAP will not support SQL 2008 RTM – at least CU1 has to be installed. [April 2009: in the meanwhile CU2 is the minimum supported version by SAP] The SQL Server DVD from SAP will include an unattended installation, which installs SQL Server 2008 and CU1 consecutively.

Which SP, CU or hotfix is supported by SAP?
In the past there was some confusion about the SQL Server versions required for SAP. As a basic principle SAP tests, supports and delivers each SQL Server Service Pack. Once a new Service Pack is released we encourage SAP customers to apply it. If an important fix is generally needed for SAP databases then we state the build number of the fix as the minimum SQL Server build required for SAP. This is typically the build number of the actual Service Pack, but it also could be a cumulative update or hotfix. SAP note #62988 describes in detail the actual SQL Server versions supported by SAP. Currently you need at least build 9.00.3042 for SQL Server 2005 and build 8.00.2039 for SQL Server 2000. SQL Server 2008 is not mentioned in the note yet because it is still in SAP’s final software validation stage.
We do not encourage SAP customers to apply each and every cumulative update or hotfix once it is available from Microsoft. The fact that a hotfix is not listed in OSS note #62988 does not mean that this fix can’t be used in an SAP system. If a hotfix is recommended by Microsoft support to solve a particular problem at a customer’s system, then SAP will support this hotfix, even if it is not mentioned in SAP note #62988.

What’s special for fixes of SQL Native Client?
An issue of SQL Server Native Client (SNAC) does not only concern the database server. A hotfix of SNAC has to be installed on each and every SAP application server. This can be easily missed, in particular in large data centers with dedicated Windows, database and SAP administrators. When installing a new SAP application server you should therefore double check that you have installed the same SNAC version as on the database server. SAP also checks this and displays any mismatch in transaction RZ20. The most severe SNAC issue is described in SAP note #1067103. SAP implemented a workaround in the SAP database interface (DBSL, dbmssslib.dll). The DBSL will automatically be replicated to each SAP application server once an SAP NetWeaver ABAP instance starts up. Therefore this particular issue will not occur for ABAP even if the necessary SNAC fix was not applied to all application servers.

What’s special for the installation of SQL Server for an SAP system?
SAP as a reseller of SQL Server delivers its own SQL Server DVD. This DVD contains exactly the same bits as the retail version of SQL Server. In order to simplify the SQL Server (2000, 2005 and 2008) installation for SAP, a VB-script is delivered by SAP as well. Double-clicking SQL4SAP.VBS starts the customized installation of SQL Server for SAP. It is not mandatory to install SQL Server via the SAP delivered VB-script. The manual installation and required settings are described in the SAP installation guide as well. The customer has the choice which way to install.

SQL4SAP does install unattended with the correct configuration for SAP and hence avoids mistakes in manual installations. It installs SQL Server Enterprise Edition, the latest service pack and cumulative update consecutively. The script performs comprehensive prerequisite tests and automatically chooses the right platform (x86, x64, IA64). SQL4SAP always installs the collation SQL_Latin1_General_CP850_BIN2, which is mandatory for an SAP system. It enables TCP/IP, Named Pipes and Shared Memory and sets some configuration options to 1 ('show advanced options', 'max degree of parallelism', 'xp_cmdshell'). For security reasons, the SQL Server authentication mode is set to “Windows Only”. In addition the SQL login “sa” has a random password and is disabled. For further details about the customized installation of SQL Server for SAP read the attached SAP guide.

Posted Monday, September 29, 2008 4:51 PM by Martin Merdes | 1 Comments

SQL Server 2008 Row Level Compression Roll-Out in Microsoft’s SAP ERP system

Lately a lot of questions came up around what our different compression types in SQL Server 2008 would achieve in reduction of database volume. In this article and some following articles we would like to answer this question as well as the question on how to get an existing database to use SQL Server 2008 compression methods.

Lets clarify one thing first: SQL Server 2008 Row Level Compression is not really a compression in the sense of applying an algorithm to compress some data. It simply is a different row format which is more efficiently storing all numeric data types and fixed length character data types. In all our tests with SAP systems we so far didn’t experience any negative performance impact by applying Row Level Compression. Hence any SAP application which will install against SQL Server 2008 will create ALL tables Row Level compressed. Nonclustered Indexes are created non-compressed since SAP is not yet ready with the implementation of compressing non-clustered indexes. SQL Server 2008 itself can compress clustered as well as non-clustered indexes.

But now to our usage case in Microsoft’s own ERP system. The challenge looked like:

·         We were looking at our SAP ERP system having 6.5TB data volume

·         We were not allowed to take any additional downtime than the monthly 2h in order to  perform additional work to compress the data. This forced us to perform compression of the tables during operational times using the possibility to rebuild the tables online

·         We were not allowed to perform the compression during the week, but on the weekends

·         Some Business User groups had concerns and we needed to arrange to have certain tables being rebuild on specific weekends

·         The goal was to get ALL the tables onto the new Row level Compression as if the system would have been just installed by SAP against SQL Server 2008

From a tool side there are two possibilities. The functionality SAP developed to compress tables and a procedure I developed. The SAP tool has clear advantages in cases where the database is smaller (up to 1TB) and the possibility exists to take the system down for 24-36h. Whereas the tool I developed has advantages in situations as we encountered them here in Microsoft. I am in the process of writing a longer paper on both tools which we will publish on SDN and/or on Microsoft.com in the next few months. The procedure I developed will be released in one of the articles in this blog after some more testing.

Based on data we collected applying Row Level Compression in our test system it became clear that we will need to spread this activity over 5-6 weekends. We were allowed to start Friday evening 7pm, but needed to finish latest on Sunday afternoon 4pm every weekend. Based on the timing we got from the test system, we basically built 6 groups of tables. Each group was planned to be worked through on one weekend. The group for the first weekend contained all but around 120 tables from the empty tables up to small to midsized tables in the range of a few GB. At the other end of the spectrum, at the last weekend we just planned for compressing our largest table. Our largest tables in the SAP ERP system is a customer created table supporting a customer created functionality and has a volume of more than 1 TB with around 2.3 billion rows. The strategy was to perform compression on the smallest tables first in order to create free space which is necessary to perform compression on the larger tables.

Where are we now? Our progress was faster than expected. We were able to compress on two weekends all but the 6 largest tables plus some of the tables with varbinary(max) columns which can’t be rebuild online. All in all we already worked through 4TB of the original 6.5TB data volume. The 4TB of original data volume ended up to be 3TB volume after the compression. Means we cut a nice 1TB database volume out of our productive database. With at least another 2 weekends to go, we are pretty optimistic to cut out another 600GB at least. Sure, applying Row Compression to a table also will defragment a table. However we need to keep in mind that the productive database of Microsoft’s SAP ERP system got completely exported/imported for the Unicode conversion in February 2007. Hence fragmentation on some of the tables was rather limited at least to significant parts of the tables.

Another good experience was that our synchronous Database Mirroring could keep up with the rebuild activity of the tables on the productive database. There never was a need to resynchronize either the mirror or the Log-Shipping destination. Given the fact that we have 10 copies of our productive database within our SAP landscape, cutting 1TB out of it, meant saving all in all 10TB already in the whole SAP landscape.

We are looking forward to finish our activity in another 2 weekends after getting permission from our Finance Department who blocked all further compression activities while running Quarter End Reporting. Stay tuned for more news

Posted Monday, September 29, 2008 12:40 AM by Juergen Thomas - MSFT | 1 Comments